🪟Оконные функции в SQL — как они работают и где применяются?

Когда обычный GROUP BY не даёт нужной гибкости, на помощь приходят оконные функции.

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

Оконная функция выполняет операцию на наборе строк, называемом "окном" — это подмножество данных, которое определяется с помощью ключевых слов OVER(), оно производит вычисления над набором строк, связанных с текущей записью, без группировки. Можно представить окно, как рамку, которая двигается вдоль данных, вычисляя значения для каждой позиции.

Образец общего синтаксиса:

SELECT 
    column1,
    column2,
    ОКОННАЯ_ФУНКЦИЯ() OVER (
        [PARTITION BY partition_expression, ...]
        [ORDER BY sort_expression [ASC | DESC], ...]
        [ROWS | RANGE frame_specification]
    ) AS result_column
FROM 
    table_name;

Типы оконных функций:
ROW_NUMBER() — присваивает уникальный номер каждой строке в окне.
RANK() — ранжирует строки с учётом возможных одинаковых значений.
DENSE_RANK() — похож на RANK(), но без пропусков в номерах рангов.
NTILE(n) — делит данные на n равных частей.
SUM(), AVG(), MIN(), MAX() — агрегатные функции, применяемые к окну.

Пример 1: AVG()
Посчитать зарплату каждого сотрудника и среднюю зарплату по его отделу, не теряя строк.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

Что происходит:
1. PARTITION BY department делит строки на группы (по отделам),
2. AVG(salary) считает среднюю по каждой такой группе.

Пример 2: NTILE(n)
NTILE(n) делит данные на n равных частей и присваивает каждому элементу номер его "порции".
Это полезно, например, для разбивки данных на квартили или группы.

SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Здесь сотрудники будут разделены на 4 группы по зарплатам.

Еще пример: ROW_NUMBER()
Функция присваивает уникальный номер каждой строке в окне. Строки нумеруются по порядку, начиная с 1, на основе сортировки в ORDER BY. Это полезно, например, для генерации уникальных идентификаторов в рамках группы.

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

В этом примере всем сотрудникам будет присвоен уникальный номер, начиная с самого высокооплачиваемого.

Важно!
1. Оконные функции не сворачивают строки (в отличие от GROUP BY).
2. Работают после WHERE и GROUP BY, но до ORDER BY в основном запросе.
3. Поддерживаются в PostgreSQL, MySQL 8+, SQL Server, Oracle.

Преимущества оконных функций:
1. Гибкость — оконные функции позволяют решать сложные аналитические задачи, не меняя структуру данных.
2. Скорость — они могут быть быстрее, чем использование подзапросов или объединений, поскольку не нужно выполнять дополнительные агрегации.
3. Удобство — оконные функции позволяют работать с большими объёмами данных и производить расчёты, сохраняя доступ к каждой строке.

#Оконные_функции #SQL

📱 Подписаться на канал | Курс автора по SQL DDL