Язык структурированных запросов (SQL) станет отличным дополнением к вашему набору инструментов. Возможно, он не так популярен, как Python или JavaScript, но знание SQL, особенно продвинутого SQL, дает ряд преимуществ.
Во-первых, знание передовых методов SQL может помочь вам лучше понять данные и более эффективно донести свои идеи. А поскольку SQL подвержен тем же узким местам и проблемам производительности, что и другие языки, более глубокие знания помогут вам оптимизировать ваши запросы.
Наконец, дополнительные знания помогут вам выделиться на фоне других разработчиков, которые могут знать операции CRUD, но не продвинутые концепции SQL. Это даст вам преимущество в карьере.
Эта статья познакомит вас с некоторыми продвинутыми концепциями SQL, включая подзапросы, промежуточные итоги и общие табличные выражения (CTE). Для работы вы можете использовать любую базу данных, поддерживающую SQL, и редактор по вашему выбору. Вы также можете использовать онлайн-редактор SQL.
В следующих примерах используется онлайн-редактор SQL вместе с базой данных SQLite. Если вы работаете с такой базой данных, как PostgreSQL, возможно, вам придется изменить синтаксис; однако основные концепции остаются теми же.
- Операторы UNION, INTERSECT и EXCEPT
- Оператор UNION
- Оператор INTERSECT
- Оператор EXCEPT
- Подзапросы
- Подзапрос с помощью SELECT
- Подзапрос с FROM
- Подзапрос с WHEN
- Общие табличные выражения (CTE)
- Рекурсивные CTE в SQL
- Первые N чисел
- Факториал
- Общий синтаксис для рекурсивных CTE
- Добавление выборочных данных
- Задача первая
- Вторая задача
- Текущие итоги
- Утверждения CASE WHEN
- Заключение
Операторы UNION, INTERSECT и EXCEPT
Вы можете использовать пример данных из этого ресурса. В данном примере все команды были объединены в один скрипт в этом хранилище. Запустите скрипт, и он создаст три таблицы с данными примера. Ниже приведена схема таблиц:
Изображение любезно предоставлено w3resource
Первые пять строк каждой таблицы показаны ниже:
Операторы UNION, INTERSECT и EXCEPT помогают объединить результаты двух запросов. Операторы в SQL похожи на математические операторы при работе с множествами. Вы должны помнить следующее правило:
Количество столбцов и порядок столбцов в таблице, возвращенной первым запросом, должны быть равны количеству столбцов и порядку столбцов в таблице, возвращенной вторым запросом. Кроме того, тип данных соответствующих столбцов должен быть совместим.
Ниже приведен общий синтаксис использования операторов в SQL:
SELECT * FROM TABLE1
OPERATOR
SELECT * FROM TABLE2
Оператор UNION
Оператор UNION возвращает комбинацию таблиц из двух запросов. По умолчанию результат не будет содержать дублирующихся значений. Однако вы можете использовать оператор UNION ALL, если хотите сохранить дубликаты.
Следующая команда вернет список всех разных городов:
SELECT working_area FROM AGENTS
UNION
SELECT cust_city FROM CUSTOMER
Следующая команда вернет список всех городов, включая дубликаты:
SELECT
working_area
FROM
AGENTS
UNION ALL
SELECT
cust_city
FROM
CUSTOMER
Ниже приведено изображение, сравнивающее результаты команд UNION и UNION ALL:
Как вы можете видеть, в правой таблице Лондон и Бангалор повторяются.
Оператор INTERSECT
Оператор INTERSECT возвращает общие элементы в таблицах, возвращенных первым и вторым запросом.
-- Returns a list of common cities in both columns
SELECT
working_area
FROM
AGENTS
INTERSECT
SELECT
cust_city
FROM
CUSTOMER
Результат показан ниже:
Оператор EXCEPT
Оператор EXCEPT возвращает все элементы в таблице, возвращенной первым запросом, которых нет в таблице, возвращенной вторым запросом.
SELECT
working_area
FROM
AGENTS
EXCEPT
SELECT
cust_city
FROM
CUSTOMER
Приведенная выше команда вернет таблицу с одной строкой, содержащей Ванкувер. Вы заметите, что Ванкувер — единственный город, который есть в CUSTOMER.cust_city, но нет в AGENTS.working_area.
Подзапросы
В SQL можно иметь запрос внутри запроса, также известный как подзапрос. Подзапрос также может содержать запрос внутри себя. Подзапросы имеют три основные формы:
- Как часть оператора
SELECT. - После
FROM - После
WHERE.
Подзапросы помогают разбить сложные соединения и объединения на более мелкие части, что делает ваш запрос более читабельным и удобным в обслуживании, тем более что вы можете использовать результаты существующих запросов. Подзапросы также облегчают отладку, поскольку вы можете отлаживать каждый запрос отдельно. Понимание подзапросов также поможет вам писать рекурсивный SQL.
Подзапрос с помощью SELECT
Посмотрите на таблицы с именами AGENTS и ORDERS. Перед вами стоит задача вернуть таблицу с двумя столбцами. Первый столбец должен содержать agent_code из таблицы AGENTS, а второй — сумму столбца ORDERS.ord_amount. Во втором столбце должно быть одно и то же значение или сумма, повторенная несколько раз.
Первый возвращает сумму всех сумм заказов:
SELECT
SUM(ord_amount)
FROM
ORDERS
Приведенная выше команда будет вашим подзапросом. Вы можете использовать его, как показано ниже, чтобы получить требуемый результат:
SELECT
agent_code,
(
SELECT
SUM(ord_amount)
FROM
ORDERS
) total
FROM
AGENTS
Команда во второй строке является подзапросом. Он возвращает сумму столбца ORDERS.ord_amount. Вы также можете назвать второй столбец, либо используя AS, либо включив предпочитаемое название после подзапроса. Ниже приведен скриншот частичного результата:
Подзапрос с FROM
Следующая команда SQL возвращает таблицу с общим количеством сумм заказов, средним значением сумм заказов и количеством заказов, проданных агентом клиенту:
SELECT
agent_code,
cust_code,
sum(ord_amount) total_amnt,
avg(ord_amount) avg_amount,
count(*) AS num
FROM
ORDERS
GROUP BY
agent_code,
cust_code
Вы должны использовать результат, чтобы найти общее количество заказов, проданных каждым агентом. Вы можете использовать приведенную выше команду в качестве подзапроса вместе с FROM:
SELECT
agent_code,
count(agent_code) AS count
FROM
(
SELECT
agent_code,
cust_code,
sum(ord_amount) total_amnt,
avg(ord_amount) avg_amount,
count(*) AS num
FROM
ORDERS
GROUP BY
agent_code,
cust_code
)
GROUP BY
agent_code
Приведенная выше команда возвращает общее количество клиентов, которым продал заказы каждый агент. Вместо того чтобы выбирать из существующей таблицы, вы выбираете из таблицы, возвращенной вашим подзапросом. Ниже приведен частичный результат:
Подзапрос с WHEN
Теперь используйте таблицы AGENTS и CUSTOMER, чтобы вернуть таблицу, содержащую только общие имена агентов и клиентов.
Сначала верните таблицу с именами всех клиентов, используя эту команду:
SELECT
cust_name
FROM
CUSTOMER
Теперь используйте оператор IN и таблицу, возвращенную вышеприведенным запросом, чтобы найти общие имена. Ниже приведена команда, использующая предыдущую команду в качестве подзапроса:
SELECT
agent_name
FROM
AGENTS
WHERE
agent_name IN (
SELECT
cust_name
FROM
CUSTOMER
)
Предложение WHERE проверяет, присутствует ли AGENTS.agent_name в таблице, возвращенной вашим подзапросом. Он вернет таблицу с двумя строками: Benjamin и Charles.
Общие табличные выражения (CTE)
В SQL вы создаете временные переменные для хранения результатов ваших подзапросов. Эти переменные доступны только во внешнем запросе. В предыдущем примере, в котором вы вернули таблицу с количеством заказов, проданных каждым агентом, вы можете сохранить этот результат в виде CTE, чтобы сделать ваши запросы более читабельными.
WITH CTE_AGENT_CUSTOMER AS (
SELECT
agent_code,
cust_code,
sum(ord_amount) total_amnt,
avg(ord_amount) avg_amount,
count(*) AS num
FROM
ORDERS
GROUP BY
agent_code,
cust_code
)
SELECT
agent_code,
count(agent_code) AS count,
AVG(total_amnt) AS AVG,
SUM(total_amnt) AS SUM
FROM
CTE_AGENT_CUSTOMER
GROUP BY
agent_code
CTE необходимы при работе с рекурсивным SQL.
Рекурсивные CTE в SQL
Рекурсия в SQL похожа на рекурсию в любом другом языке. Вы делите большую проблему на меньшие и продолжаете решать меньшие проблемы, пока не достигнете условия завершения. Если вы не укажете конечное условие, это отправит вашу команду SQL в бесконечный цикл.
Ниже приведены две распространенные проблемы, которые можно решить с помощью рекурсии.
Первые N чисел
Ваша задача — с помощью рекурсии вывести таблицу с числами от одного до двадцати. Ниже приведен рекурсивный код:
WITH NUMBER_CTE AS (
SELECT
1 AS num -- Starting Table
UNION
-- Combine Starting Table with Table from Below Query
SELECT
num + 1 as num -- Update Rule
FROM
NUMBER_CTE -- CTE calling itself, ie recursion
WHERE
num < 20 -- Terminating Condition
)
SELECT
*
from
NUMBER_CTE -- Displaying the recursive CTE
Рассмотрим отдельно первый и второй запрос оператора UNION.
SELECT
1 AS num -- Starting Table
Выше приведен прямой запрос, который возвращает столбец num со значением 1 в нем.
SELECT
num + 1 as num -- Update Rule
FROM
NUMBER_CTE -- CTE calling itself, ie recursion
WHERE
num < 20 -- Terminating Condition
Приведенный выше запрос увеличивает значение столбца num на единицу до тех пор, пока исходное значение меньше 20.
При первом запуске результат первого подзапроса будет таким:
num
------
1
------
А результатом второго запроса будет:
num
------
2
------
Оператор UNION объединит обе таблицы и обновит ваш CTE до следующей таблицы:
num
------
1
------
2
------
Во втором запросе, поскольку вы выбираете из вашего CTE, два запроса вместе с оператором UNION будут выполнены снова. Первый запрос снова вернется:
num
------
1
------
Второй запрос будет использовать CTE из предыдущего запуска и увеличивать каждую строку на единицу, возвращая следующее:
num
------
2
------
3
------
Оператор UNION снова объединяет таблицы из обоих запросов. Обновленный CTE будет выглядеть следующим образом:
num
------
1
------
2
------
3
------
Этот процесс будет продолжаться до тех пор, пока в столбце не появится строка со значением 20. В этом случае условие WHEN не будет выполнено, и вы остановите процесс рекурсии.
Факториал
Теперь попробуйте выполнить команду SQL, которая создает таблицу с факториальными значениями. Попробуйте найти факториал числа 5.
WITH FACTORIAL AS (
SELECT
5 AS NUM,
1 AS PRODUCT -- Starting Table
UNION
-- Combine Starting Table with Table from Below Query
SELECT
NUM - 1 AS NUM,
PRODUCT * NUM AS PRODUCT -- Update Rule
FROM
FACTORIAL -- CTE calling itself, i.e recursion
WHERE
NUM > 1 -- Terminating Condition
)
SELECT
*
FROM
FACTORIAL
Ваша исходная таблица должна иметь два столбца — один для хранения текущего множителя и один для хранения текущего произведения. Произведение будет 1, а первый множитель будет 5.
Результат первого запроса будет следующим:
num product
------ ---------
5 1
------ ---------
Результатом второго запроса будет:
num product
-------- --------------
5-1 = 4 5*1 = 5
-------- --------------
Новый CTE будет таким:
num product
------ ---------
5 1
------ ---------
4 5
------ ---------
При следующем запуске результат первого запроса будет таким же:
num product
------ ---------
5 1
------ ---------
Результат второго запроса будет рассчитан на основе CTE из предыдущего запуска:
num product
--------- ---------
5 - 1 = 4 1 * 5 = 5
--------- ---------
4 - 1 = 3 5 * 4 = 20
--------- ---------
Оператор UNION объединит обе таблицы. Обновленный CTE будет выглядеть следующим образом:
num product
------ ---------
5 1
------ ---------
4 5
------ ---------
3 20
------ ---------
Этот процесс будет продолжаться до тех пор, пока вы не достигнете конечного условия.
Общий синтаксис для рекурсивных CTE
Исходя из вышеописанных проблем, вы можете увидеть общий синтаксис для рекурсивных CTE в SQL:
WITH CTE AS
(
-- Starting Table
UNION -- Combine Starting Table with Table from Below Query
-- Updated Table with Update conditions if any
FROM CTE-- CTE calling itself, i.e recursion
-- Terminating Condition
)
SELECT * FROM CTE
Завершающее условие не обязательно должно быть условием WHERE; оно также может быть условием JOIN. Вы рассмотрите условия завершения с JOIN в следующих случаях.
Добавление выборочных данных
Рекурсия обычно используется для поиска иерархических структур в компании или при работе с семейными деревьями. Вам нужно будет создать новую таблицу в вашей базе данных. Первым столбцом будет код агента, а вторым — код агента его менеджера. Если у агента нет менеджера, значение во втором столбце будет NULL. Ниже приведена иерархическая структура, которую вы добавите в свою базу данных:
Вы можете найти скрипт для добавления данных в этом репозитории.
Задача первая
Допустим, вам дали ID агента и попросили вернуть таблицу с менеджером агента, skip-менеджером, skip-skip-менеджером и т.д., вплоть до самого верха. Ниже приведена команда для выполнения этого задания:
with HIERARCHY_CTE AS (
SELECT
h1.agent_code,
h1.manager_code
from
HIERARCHY h1
WHERE
h1.agent_code == 'A006'
UNION
SELECT
h2.agent_code,
h2.manager_code
FROM
HIERARCHY h2
JOIN HIERARCHY_CTE ON h2.AGENT_CODE = HIERARCHY_CTE.manager_code
)
SELECT
*
FROM
HIERARCHY_CTE
Опять же, рассмотрим каждый запрос в отдельности.
SELECT
h1.agent_code,
h1.manager_code
FROM
HIERARCHY h1
WHERE
h1.agent_code == 'A006'
Приведенный выше запрос возвращает код агента и код агента его менеджера. Ниже показан результат:
agent_code manager_code
--------- ------------
A006 A005
Теперь посмотрите на второй запрос:
SELECT
h2.agent_code,
h2.manager_code
FROM
HIERARCHY h2
JOIN HIERARCHY_CTE ON h2.AGENT_CODE = HIERARCHY_CTE.manager_code
Этот запрос выполняет JOIN между таблицей HIERARCHY и созданным вами CTE. Он пытается найти строки, в которых agent_code из HIERARCHY совпадает с manager_code для CTE. Затем возвращается код агента и код его менеджера, или менеджера A006 и skip-менеджера A006.
Ниже приведен вывод:
agent_code manager_code
--------- ------------
A005 A003
Объединение возвращает следующую таблицу:
agent_code manager_code
--------- ------------
A006 A005
--------- ------------
A005 A003
При втором запуске снова возвращается первый запрос:
agent_code manager_code
--------- ------------
A006 A005
Второй запрос, однако, будет включать и менеджера A003. Ниже показан результат:
agent_code manager_code
--------- ------------
A005 A003
--------- ------------
A003 A001
Оператор UNION объединяет таблицы. Ваш обновленный CTE будет выглядеть следующим образом:
agent_code manager_code
--------- ------------
A006 A005
--------- ------------
A005 A003
--------- ------------
A003 A001
У A001 нет менеджера. В следующем запуске вы столкнетесь с прерывающим условием.
Вторая задача
В этом задании вы назначите уровень каждому агенту. Агенты, у которых нет начальника, относятся к первому уровню, агенты, которые им подчиняются, — ко второму, и так далее. Ниже приведена команда, которая выводит эту таблицу:
with HIERARCHY_CTE AS (
SELECT
h1.agent_code,
h1.manager_code,
1 AS level
from
HIERARCHY h1
WHERE
h1.manager_code IS NULL
UNION
SELECT
h2.agent_code,
h2.manager_code,
level + 1 AS level
from
HIERARCHY h2
JOIN HIERARCHY_CTE on h2.manager_code == HIERARCHY_CTE.agent_code
)
SELECT
*
FROM
HIERARCHY_CTE
Это похоже на команду из предыдущего задания. Основное отличие заключается в том, что вы начинаете с верхнего агента, а не с нижнего.
Текущие итоги
Текущие итоги также известны как кумулятивные суммы. Рассмотрим таблицу ORDERS в вашей базе данных. В ней есть столбцы ord_amount и ord_date. Попробуйте вычислить кумулятивную сумму по каждой записи даты в столбце ord_date. Ниже приведена команда:
SELECT
ord_date,
SUM(ord_amount) OVER (
ORDER BY
ord_date
) AS Running_Total
FROM
ORDERS
Команда OVER сообщает SQL, что вы хотите найти кумулятивную сумму столбца ord_amount по всему набору данных. Столбец в предложении ORDER BY используется для сортировки результатов по дате.
Утверждения CASE WHEN
Операторы CASE WHEN в SQL похожи на операторы if-else или switch-case в других языках. Ниже приведен общий синтаксис операторов CASE WHEN:
CASE
WHEN CONDITION THEN RESULT
WHEN CONDITION THEN RESULT
WHEN CONDITION THEN RESULT
ELSE RESULT
END Column_Name
Результат в ELSE возвращается, если ни одно из условий не является истинным.
В таблице CUSTOMER есть столбец grade со значениями от 0-4. Перед вами стоит задача создать новый столбец grade_letter, который сопоставит 1 с A, 2 с B, а все остальное с F. Для выполнения этой задачи можно использовать операторы CASE WHEN. Ниже приведена команда:
SELECT
*,
CASE WHEN grade == 1 THEN 'A' WHEN grade == 2 THEN 'B' ELSE 'F' END grade_letter
FROM
CUSTOMER
Заключение
Это руководство позволило вам получить практический опыт работы с более продвинутыми темами SQL. Применение этих инструментов поможет вам получить больше информации из ваших данных, чтобы вы могли писать более качественные запросы на SQL.
Если вы работаете с SQL, вас может заинтересовать CloudQuery. Эта система инвентаризации облачных активов с открытым исходным кодом на базе SQL интегрируется с основными облачными провайдерами, включая Azure, AWS и GCP. Вы можете использовать его для аудита и оценки ваших облачных активов в текущей и исторической перспективе.
Чтобы узнать больше о том, что CloudQuery может сделать для вас, ознакомьтесь с его документацией.
This article was originally posted on
Advanced SQL: Going beyond the Basics
Connect with me on LinkedIn:
https://www.linkedin.com/in/rahulbanerj