Что такое оконные функции в БД?

Оконные функции в БД (Window Functions)

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

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

SELECT
  column1,
  column2,
  AGGREGATE_FUNCTION() OVER (
    [PARTITION BY column]
    [ORDER BY column]
    [ROWS BETWEEN ... AND ...]
  ) as window_result
FROM table_name;

Компоненты:

• PARTITION BY — разделяет данные на подгруппы (как GROUP BY, но сохраняет все строки)
• ORDER BY — сортирует строки внутри окна
• ROWS/RANGE — определяет размер окна (скользящее окно)

Типы оконных функций

1. Агрегирующие функции (с OVER)

SELECT
  employee_id,
  salary,
  department,
  -- Сумма зарплат в отделе
  SUM(salary) OVER (PARTITION BY department) as dept_total,
  -- Средняя зарплата в компании
  AVG(salary) OVER () as company_avg,
  -- Максимальная зарплата в отделе
  MAX(salary) OVER (PARTITION BY department) as dept_max
FROM employees;

Результат:

2. Функции ранжирования

SELECT
  employee_id,
  salary,
  department,
  -- ROW_NUMBER: уникальный номер (1, 2, 3...)
  ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
  -- RANK: ранг с пропусками при равных значениях
  RANK() OVER (ORDER BY salary DESC) as rank,
  -- DENSE_RANK: ранг без пропусков
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

Разница между RANK и DENSE_RANK:

3. Функции смещения

SELECT
  order_date,
  revenue,
  -- Предыдущее значение
  LAG(revenue) OVER (ORDER BY order_date) as prev_revenue,
  -- Следующее значение
  LEAD(revenue) OVER (ORDER BY order_date) as next_revenue,
  -- Первое значение в окне
  FIRST_VALUE(revenue) OVER (ORDER BY order_date) as first_revenue,
  -- Последнее значение в окне
  LAST_VALUE(revenue) OVER (ORDER BY order_date) as last_revenue,
  -- Разница с предыдущим днём
  revenue - LAG(revenue) OVER (ORDER BY order_date) as daily_change
FROM daily_sales
ORDER BY order_date;

4. Функции распределения

SELECT
  student_id,
  score,
  -- Процентиль (0-100)
  PERCENT_RANK() OVER (ORDER BY score) as percentile,
  -- N-тиль (1-4 для квартилей)
  NTILE(4) OVER (ORDER BY score) as quartile
FROM student_scores;

Практический пример: скользящее окно

SELECT
  order_date,
  daily_sales,
  -- Скользящее среднее за 7 дней
  AVG(daily_sales) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as ma_7day,
  -- Сумма за последние 30 дней
  SUM(daily_sales) OVER (
    ORDER BY order_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) as sum_30day
FROM daily_sales
ORDER BY order_date;

Вариант ROWS:

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — текущая строка и 6 предыдущих
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — всё окно
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING — предыдущая, текущая и следующая

Пример: Анализ когорт

WITH user_first_purchase AS (
  SELECT
    user_id,
    order_date,
    revenue,
    -- Первая покупка пользователя
    MIN(order_date) OVER (PARTITION BY user_id) as first_purchase_date
  FROM orders
),
cohorts AS (
  SELECT
    user_id,
    order_date,
    revenue,
    first_purchase_date,
    -- Месяцы от первой покупки
    DATEDIFF(month, first_purchase_date, order_date) as cohort_month,
    -- Ранг покупки
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) as purchase_num
  FROM user_first_purchase
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) as users,
  SUM(revenue) as revenue,
  AVG(revenue) as avg_revenue
FROM cohorts
GROUP BY cohort_month
ORDER BY cohort_month;

Оконные функции vs GROUP BY

-- ❌ GROUP BY: теряются детали
SELECT
  department,
  AVG(salary) as avg_salary
FROM employees
GROUP BY department;
-- Результат: одна строка на отдел

-- ✅ Оконная функция: сохраняет все строки
SELECT
  employee_id,
  salary,
  department,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;
-- Результат: все сотрудники + средняя в их отделе

Производительность

Оконные функции часто быстрее альтернатив:

• Лучше, чем вложенные SELECT
• Лучше, чем самоприсоединение (self-join)
• Требуют сортировки, но это обычно быстрее GROUP BY

Поддержка в разных БД

• PostgreSQL — полная поддержка (8.4+)
• MySQL — полная поддержка (8.0+)
• SQL Server — полная поддержка
• Snowflake — полная поддержка
• BigQuery — полная поддержка
• SQLite — частичная поддержка (8.0+)

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