Расширенные SQL-запросы для анализа данных

SQL – один из самых ценных инструментов для работы с данными. Существуют некоторые продвинутые запросы SQL, которые вы не узнаете при изучении основ SQL, но которые пригодятся вам в работе при анализе данных. Поэтому, если вы хотите узнать о некоторых продвинутых SQL-запросах для анализа данных, эта статья для вас. В этой статье я расскажу вам о некоторых продвинутых SQL-запросах для анализа данных и о том, когда их можно использовать.

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

Функции окон

Оконные функции выполняют вычисления в наборе строк таблицы, которые каким-то образом связаны с текущей строкой. В отличие от агрегатных функций, оконные функции не приводят к группировке строк в одну выходную строку. Вы можете использовать оконные функции для вычисления промежуточных итогов, ранжирования, скользящих средних и кумулятивных сумм, не сворачивая результаты.

Например, посмотрите на таблицу ниже:

Приведенный ниже запрос вычисляет среднюю зарплату в каждом отделе, не группируя всю таблицу по отделам, что позволяет напрямую сравнить зарплату каждого сотрудника со средней зарплатой по отделу:

SELECT 
  employee_id,
  department,
  salary,
  AVG(salary) OVER(PARTITION BY department) as avg_department_salary
FROM employees;

Оконные функции поддерживаются в PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.25.0+) и IBM DB2.

Общие табличные выражения (CTE)

CTE – это способ создания временного набора результатов, на который можно ссылаться в другом операторе SELECT, INSERT, UPDATE или DELETE. Вы можете использовать CTE для создания временных наборов результатов, на которые ссылаются несколько раз в рамках одного запроса, или для улучшения читабельности и организации сложных запросов.

Например, посмотрите на таблицу ниже:

Приведенный ниже запрос рассчитывает общий объем продаж по регионам, а затем выбирает только те регионы, в которых объем продаж превышает средний объем продаж по региону:

WITH RegionalSales AS (
  SELECT region, SUM(amount) AS total_sales
  FROM sales
  GROUP BY region
)
SELECT region, total_sales
FROM RegionalSales
WHERE total_sales > (SELECT AVG(total_sales) FROM RegionalSales);

CTE поддерживаются в PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.8.3+) и IBM DB2.

Рекурсивные CTE

Рекурсивный CTE – это обычное табличное выражение, которое ссылается на себя для многократного выполнения, что позволяет выполнять иерархические или рекурсивные запросы. Рекурсивные CTE можно использовать для запросов к иерархическим данным, таким как организационные диаграммы, или для рекурсивных вычислений, таких как последовательности Фибоначчи.

Например, приведенный ниже рекурсивный CTE генерирует ряд чисел от 1 до 10:

WITH RECURSIVE NumberSeries AS (
  SELECT 1 AS value
  UNION ALL
  SELECT value + 1 FROM NumberSeries WHERE value < 10
)
SELECT * FROM NumberSeries;

Рекурсивные CTE поддерживаются в PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.8.3+) и IBM DB2.

Запросы PIVOT

Оператор PIVOT превращает уникальные значения указанного столбца в несколько столбцов в выходных данных, что позволяет проводить перекрестное табулирование данных. Вы можете использовать PIVOT для преобразования строк в столбцы, часто для создания отчетов или сравнительного анализа.

Например, посмотрите на таблицу ниже:

Приведенный ниже запрос поворачивает данные о продажах таким образом, чтобы каждая строка представляла сотрудника, а каждый столбец – общий объем продаж за год:

SELECT *
FROM 
  (SELECT EmployeeName, Year, Sales FROM EmployeeSales) AS SourceTable
PIVOT
  (
    SUM(Sales)
    FOR Year IN ([2020], [2021], [2022])
  ) AS PivotTable;

Pivot Queries поддерживаются только в SQL Server и Oracle.

Динамический SQL

Динамический SQL подразумевает динамическое построение SQL-запросов в виде строк и их последующее выполнение. Обычно это делается в хранимых процедурах или сценариях. Динамический SQL можно использовать, когда структура запроса не известна до момента выполнения, например, когда имена столбцов или критерии фильтрации задаются пользователем.

Например, приведенный ниже запрос динамически формирует запрос на выборку всех из указанной таблицы и выполняет его:

DECLARE @TableName NVARCHAR(100) = N'Employees';
DECLARE @SQLQuery AS NVARCHAR(MAX);

SET @SQLQuery = N'SELECT * FROM ' + @TableName;
EXEC sp_executesql @SQLQuery;

Динамический SQL поддерживается SQL Server, Oracle, PostgreSQL, MySQL и IBM DB2.

Заявление о слиянии

Оператор MERGE позволяет выполнить несколько действий, таких как INSERT, UPDATE или DELETE, в одном операторе на основе заданного условия. Он часто используется для синхронизации двух таблиц. Вы можете использовать оператор MERGE, когда вам нужно обновить таблицу на основе содержимого другой таблицы, вставить новые строки, обновить существующие или удалить те, которые больше не соответствуют.

Например, посмотрите на таблицы ниже:

Приведенный ниже запрос синхронизирует TargetTable с SourceTable, обновляя имена, если идентификаторы совпадают, вставляя новые записи из SourceTable и удаляя записи в TargetTable, которые больше не имеют соответствующих записей в SourceTable:

MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID
WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name) VALUES (source.ID, source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Операция MERGE поддерживается SQL Server, Oracle и DB2.

Резюме

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