Оконные функции с коррелированными CTE

Оконные функции и коррелированные CTE (Common Table Expressions) часто используются вместе в SQL для выполнения сложного анализа данных. Оконные функции позволяют выполнять вычисления по окнам данных, а коррелированные CTE позволяют использовать промежуточные результаты в основных запросах, создавая более гибкие и мощные запросы.

Основные понятия

1. Оконные функции: Оконные функции выполняют вычисления по набору строк, который определяется оконной рамкой (window frame). Эти функции могут быть использованы для агрегации, ранжирования и выполнения других аналитических задач.

2. Коррелированные CTE: Коррелированные CTE используют значения из основного запроса для вычисления промежуточных результатов. Они могут быть использованы для создания промежуточных наборов данных, которые зависят от значений в основном запросе.

Синтаксис оконных функций

Оконные функции используют оконные функции, такие как ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), и другие, в сочетании с ключевыми словами OVER и PARTITION BY:

SELECT column1,
       column2,
       WINDOW_FUNCTION(column3) OVER (PARTITION BY column4 ORDER BY column5) AS result
FROM table_name;

Примеры использования оконных функций с коррелированными CTE

Пример 1: Ранжирование сотрудников по зарплате в каждом департаменте

Создание коррелированного CTE для вычисления ранга сотрудников по зарплате и использование оконной функции для подсчета общего количества сотрудников в каждом департаменте.

WITH RankedEmployees AS (
    SELECT employee_id, name, department_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT re.employee_id, re.name, re.department_id, re.salary, re.rank,
       COUNT(*) OVER (PARTITION BY re.department_id) AS department_size
FROM RankedEmployees re
WHERE re.rank <= 5
ORDER BY re.department_id, re.rank;

В этом примере:

• RankedEmployees вычисляет ранг сотрудников в каждом департаменте по зарплате.

• Основной запрос использует оконную функцию COUNT(*) для подсчета общего количества сотрудников в каждом департаменте и фильтрует топ-5 сотрудников по зарплате.

Пример 2: Вычисление скользящего среднего

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

WITH SalesData AS (
    SELECT sale_date, salesperson_id, sales_amount,
           ROW_NUMBER() OVER (PARTITION BY salesperson_id ORDER BY sale_date) AS rn
    FROM sales
)
SELECT sd.sale_date, sd.salesperson_id, sd.sales_amount,
       AVG(sd.sales_amount) OVER (PARTITION BY sd.salesperson_id
                                  ORDER BY sd.rn
                                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM SalesData sd
ORDER BY sd.salesperson_id, sd.sale_date;

В этом примере:

• SalesData создает номер строки для каждой продажи в порядке даты для каждого продавца.

• Основной запрос использует оконную функцию AVG() для вычисления скользящего среднего по продажам за последние 3 месяца (2 предыдущих месяца и текущий месяц).

Пример 3: Определение роста продаж по сравнению с предыдущим периодом

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

WITH MonthlySales AS (
    SELECT sale_month, salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY sale_month, salesperson_id
),
SalesGrowth AS (
    SELECT sale_month, salesperson_id, total_sales,
           LAG(total_sales) OVER (PARTITION BY salesperson_id ORDER BY sale_month) AS previous_sales
    FROM MonthlySales
)
SELECT sale_month, salesperson_id, total_sales,
       total_sales - COALESCE(previous_sales, 0) AS growth
FROM SalesGrowth
ORDER BY salesperson_id, sale_month;

В этом примере:

• MonthlySales агрегирует продажи по месяцам и продавцам.

• SalesGrowth вычисляет продажи в предыдущем месяце с помощью оконной функции LAG().

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

Заключение

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