Использование PARTITION BY в оконных функциях

Оператор PARTITION BY в оконных функциях SQL используется для разделения набора строк на отдельные группы или "окна" перед выполнением вычислений. Это позволяет применять оконные функции к каждой группе данных отдельно, а не ко всему набору данных сразу. Рассмотрим более подробно, как PARTITION BY работает и как его можно использовать.

Синтаксис PARTITION BY

PARTITION BY используется в сочетании с оконными функциями и имеет следующий синтаксис:

function_name(expression) OVER (
    PARTITION BY partition_column
    ORDER BY order_column
    [ROWS frame_specification]
)

• function_name(expression): Название оконной функции и выражение для вычисления.

• PARTITION BY partition_column: Разделяет данные на группы по значению указанного столбца.

• ORDER BY order_column: Определяет порядок строк внутри каждого окна.

• ROWS frame_specification: Определяет размер окна относительно текущей строки (опционально).

Примеры использования PARTITION BY

1. Ранжирование строк в пределах групп

Пример с ROW_NUMBER()

SELECT employee_id, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

• PARTITION BY department: Разделяет данные на группы по отделам.

• ORDER BY salary DESC: Упорядочивает сотрудников в каждом отделе по зарплате в порядке убывания.

Результат: В каждой группе (отделе) сотрудники будут упорядочены по зарплате, и каждому сотруднику будет присвоен уникальный номер.

2. Вычисление рангов внутри групп

Пример с RANK()

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

• PARTITION BY department: Разделяет данные на группы по отделам.

• ORDER BY salary DESC: Упорядочивает сотрудников в каждом отделе по зарплате в порядке убывания.

Результат: Внутри каждого отдела сотрудники получат ранги, причём одинаковые зарплаты будут иметь одинаковый ранг.

3. Кумулятивные суммы и скользящие средние в группах

Пример с SUM()

SELECT employee_id, department, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;

• PARTITION BY department: Разделяет данные на группы по отделам.

• ORDER BY salary: Упорядочивает сотрудников в каждом отделе по зарплате.

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Определяет размер окна для вычисления кумулятивной суммы.

Результат: Для каждого сотрудника будет вычислена кумулятивная сумма зарплат всех сотрудников в пределах отдела до текущего сотрудника включительно.

4. Скользящее среднее внутри групп

Пример с AVG()

SELECT employee_id, department, salary,
       AVG(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

• PARTITION BY department: Разделяет данные на группы по отделам.

• ORDER BY salary: Упорядочивает сотрудников в каждом отделе по зарплате.

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: Определяет размер окна для вычисления скользящего среднего.

Результат: Для каждого сотрудника будет вычислено среднее значение зарплат за текущую и две предыдущие строки внутри отдела.

Заключение

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