Что такое оконная функция в SQL? В чем отличие от функции агрегации с группировкой?

Что такое оконные функции

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

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

FUNCTION_NAME(column) OVER (
  [PARTITION BY column1, column2, ...]
  [ORDER BY column3 ASC|DESC]
  [ROWS BETWEEN start AND end]
)

Компоненты:

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

Основные оконные функции

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

-- ROW_NUMBER() — уникальный номер строки
SELECT 
  name, 
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- RANK() — ранг с пропусками при одинаковых значениях
SELECT 
  name, 
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- DENSE_RANK() — ранг без пропусков
SELECT 
  name, 
  salary,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

2. Агрегирующие оконные функции

SELECT 
  name, 
  salary,
  department,
  AVG(salary) OVER (PARTITION BY department) as avg_dept_salary,
  SUM(salary) OVER (PARTITION BY department) as total_dept_salary,
  COUNT(*) OVER (PARTITION BY department) as dept_count
FROM employees;

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

SELECT 
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) as prev_day_revenue,
  LEAD(revenue) OVER (ORDER BY date) as next_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) as growth
FROM daily_sales;

4. Функции относительного положения

SELECT 
  name,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary,
  LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as lowest_salary
FROM employees;

GROUP BY vs Window Functions

GROUP BY (агрегация с сжатием)

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

Результат:

department | avg_salary
-----------|------------
Sales      | 55000
Engineering| 85000
HR         | 45000

Window Function (без сжатия)

-- Результат: все строки с добавленной колонкой
SELECT 
  name,
  salary,
  department,
  AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;

Результат:

name  | salary | department | avg_salary
------|--------|------------|----------
Alice | 60000  | Sales      | 55000
Bob   | 50000  | Sales      | 55000
Carol | 90000  | Engineering| 85000
Dave  | 80000  | Engineering| 85000
Eve   | 45000  | HR         | 45000

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

SELECT 
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7days,
  ROW_NUMBER() OVER (ORDER BY date) as day_number
FROM daily_sales
ORDER BY date;

Это вычисляет среднее выручки за последние 7 дней (включая текущий день) для каждой даты.

Когда использовать

Window Functions лучше когда:

• Нужно сохранить все исходные строки
• Нужны расчёты относительно других строк в наборе (LAG, LEAD)
• Нужно добавить агрегированные значения к каждой строке
• Нужно ранжировать строки внутри групп

GROUP BY лучше когда:

• Нужна полная агрегация данных
• Нужно сжать данные (итоговый отчёт)
• Нужна фильтрация по агрегированным значениям (HAVING)

Оконные функции — мощный инструмент для анализа данных, которые часто используются в реальной практике Data Science для временных рядов, сравнения значений и расчёта метрик.