Продвинутый SQL: Выходя за рамки основ

Язык структурированных запросов (SQL) станет отличным дополнением к вашему набору инструментов. Возможно, он не так популярен, как Python или JavaScript, но знание SQL, особенно продвинутого SQL, дает ряд преимуществ.

Во-первых, знание передовых методов SQL может помочь вам лучше понять данные и более эффективно донести свои идеи. А поскольку SQL подвержен тем же узким местам и проблемам производительности, что и другие языки, более глубокие знания помогут вам оптимизировать ваши запросы.

Наконец, дополнительные знания помогут вам выделиться на фоне других разработчиков, которые могут знать операции CRUD, но не продвинутые концепции SQL. Это даст вам преимущество в карьере.

Эта статья познакомит вас с некоторыми продвинутыми концепциями SQL, включая подзапросы, промежуточные итоги и общие табличные выражения (CTE). Для работы вы можете использовать любую базу данных, поддерживающую SQL, и редактор по вашему выбору. Вы также можете использовать онлайн-редактор SQL.

В следующих примерах используется онлайн-редактор SQL вместе с базой данных SQLite. Если вы работаете с такой базой данных, как PostgreSQL, возможно, вам придется изменить синтаксис; однако основные концепции остаются теми же.

Операторы 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
Вход в полноэкранный режим Выход из полноэкранного режима

Оцените статью
devanswers.ru
Добавить комментарий