Что такое оконная функция?

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

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

Просто говоря: это позволяет считать агрегаты (SUM, AVG, RANK) не сворачивая всю таблицу, а сохраняя детальность.

Простой пример

-- БЕЗ оконной функции
SELECT 
  employee_id,
  salary,
  (SELECT AVG(salary) FROM employees) as avg_salary  ← считаю среднее для всех
FROM employees;

-- С оконной функцией (НАМНОГО проще)
SELECT 
  employee_id,
  salary,
  AVG(salary) OVER() as avg_salary  ← автоматически
FROM employees;

Синтаксис

FUNCTION() OVER (
  [PARTITION BY column]  ← разбить на подгруппы
  [ORDER BY column]       ← порядок сортировки
  [ROWS BETWEEN ...]      ← диапазон окна
)

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

1. Агрегатные функции

SELECT 
  employee_id,
  salary,
  department,
  SUM(salary) OVER (PARTITION BY department) as dept_total,
  AVG(salary) OVER (PARTITION BY department) as dept_avg,
  COUNT(*) OVER (PARTITION BY department) as dept_count
FROM employees;

Результат:
emp_id | salary | dept   | dept_total | dept_avg | dept_count
1      | 100    | Sales  | 500        | 166.67   | 3
2      | 150    | Sales  | 500        | 166.67   | 3
3      | 250    | Sales  | 500        | 166.67   | 3
4      | 80     | Admin  | 200        | 100      | 2
5      | 120    | Admin  | 200        | 100      | 2

# Получил детальность по сотрудникам, но с агрегатами по отделам

2. Ранжирующие функции

SELECT 
  employee_id,
  salary,
  department,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank_global,
  RANK() OVER (ORDER BY salary DESC) as rank_with_ties,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_by_dept
FROM employees;

Результат:
emp_id | salary | dept   | rank_global | rank_with_ties | rank_by_dept
3      | 250    | Sales  | 1           | 1              | 1
5      | 120    | Admin  | 2           | 2              | 1
2      | 150    | Sales  | 3           | 3              | 2
1      | 100    | Sales  | 4           | 4              | 3
4      | 80     | Admin  | 5           | 5              | 2

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

ROW_NUMBER() — даёт уникальный номер, даже если значения одинаковые
RANK() — повторяет ранг для одинаковых значений
DENSE_RANK() — похож на RANK но без пропусков в нумерации

Пример с одинаковыми зарплатами (200):
ROW_NUMBER: 1, 2, 3, 4
RANK():     1, 1, 3, 4  ← пропуск 2
DENSE_RANK(): 1, 1, 2, 3  ← нет пропусков

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

SELECT 
  order_date,
  sales_amount,
  LAG(sales_amount) OVER (ORDER BY order_date) as prev_day_sales,
  LEAD(sales_amount) OVER (ORDER BY order_date) as next_day_sales,
  sales_amount - LAG(sales_amount) OVER (ORDER BY order_date) as day_over_day_change
FROM daily_sales;

Результат:
order_date | sales_amount | prev_day_sales | next_day_sales | day_over_day_change
2024-01-01 | 1000         | NULL           | 1200           | NULL
2024-01-02 | 1200         | 1000           | 900            | 200
2024-01-03 | 900          | 1200           | 1500           | -300
2024-01-04 | 1500         | 900            | NULL           | 600

# LAG — предыдущее значение
# LEAD — следующее значение

4. Процентные функции

SELECT 
  customer_id,
  purchase_amount,
  PERCENT_RANK() OVER (ORDER BY purchase_amount) as percentile,
  NTILE(4) OVER (ORDER BY purchase_amount) as quartile  ← разделить на 4 части
FROM purchases;

Результат:
cust_id | amount | percentile | quartile
1       | 100    | 0.0        | 1  ← bottom 25%
2       | 250    | 0.33       | 1
3       | 500    | 0.67       | 2  ← 25-50%
4       | 750    | 1.0        | 4  ← top 25%

Практические примеры

Пример 1: Когортный анализ

-- Найти сколько дней после первой покупки каждый пользователь совершил следующую

WITH first_purchase AS (
  SELECT 
    user_id,
    MIN(order_date) as first_date
  FROM orders
  GROUP BY user_id
),
user_orders_with_rank AS (
  SELECT 
    o.user_id,
    o.order_date,
    fp.first_date,
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.order_date) as order_num,
    LAG(o.order_date) OVER (PARTITION BY o.user_id ORDER BY o.order_date) as prev_order_date
  FROM orders o
  JOIN first_purchase fp ON o.user_id = fp.user_id
)
SELECT 
  user_id,
  order_num,
  order_date,
  DATEDIFF(day, prev_order_date, order_date) as days_since_prev_order
FROM user_orders_with_rank
WHERE order_num > 1
ORDER BY user_id, order_date;

-- Результат: дни между покупками для каждого пользователя

Пример 2: Кумулятивные суммы (running totals)

SELECT 
  DATE_TRUNC('day', created_at)::date as day,
  SUM(amount) as daily_sales,
  SUM(SUM(amount)) OVER (
    ORDER BY DATE_TRUNC('day', created_at)::date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as cumulative_sales
FROM orders
GROUP BY day
ORDER BY day;

Результат:
day        | daily_sales | cumulative_sales
2024-01-01 | 1000        | 1000
2024-01-02 | 1200        | 2200
2024-01-03 | 900         | 3100
2024-01-04 | 1500        | 4600

# cumulative_sales = текущий день + все дни до текущего

Пример 3: Скользящее среднее (moving average)

SELECT 
  order_date,
  sales_amount,
  AVG(sales_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  ← 3-дневное окно
  ) as moving_avg_3day
FROM daily_sales
ORDER BY order_date;

Результат:
order_date | sales | moving_avg_3day
2024-01-01 | 100   | 100       ← только текущая
2024-01-02 | 150   | 125       ← (100+150)/2
2024-01-03 | 200   | 150       ← (100+150+200)/3
2024-01-04 | 180   | 176.67    ← (150+200+180)/3
2024-01-05 | 220   | 200       ← (200+180+220)/3

# ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
# = текущая строка + 2 предыдущих

Пример 4: Top N per group

-- Найти топ 3 товара по продажам для каждого месяца

WITH sales_ranked AS (
  SELECT 
    DATE_TRUNC('month', order_date)::date as month,
    product_id,
    product_name,
    SUM(amount) as total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_TRUNC('month', order_date)
      ORDER BY SUM(amount) DESC
    ) as rank
  FROM orders
  GROUP BY month, product_id, product_name
)
SELECT 
  month,
  product_id,
  product_name,
  total_sales
FROM sales_ranked
WHERE rank <= 3
ORDER BY month DESC, rank;

Результат:
month      | prod_id | name      | sales
2024-02-01 | 5       | Product A | 5000  ← ТОП 1 за февраль
2024-02-01 | 3       | Product B | 4500  ← ТОП 2 за февраль
2024-02-01 | 7       | Product C | 4000  ← ТОП 3 за февраль
2024-01-01 | 2       | Product D | 3500  ← ТОП 1 за январь
...

Пример 5: Сравнение с предыдущим периодом

SELECT 
  DATE_TRUNC('month', order_date)::date as month,
  SUM(amount) as monthly_revenue,
  LAG(SUM(amount)) OVER (
    ORDER BY DATE_TRUNC('month', order_date)
  ) as prev_month_revenue,
  ROUND(
    100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (...)) / 
    LAG(SUM(amount)) OVER (...),
    2
  ) as pct_growth
FROM orders
GROUP BY month
ORDER BY month;

Результат:
month      | revenue | prev_revenue | pct_growth
2024-01-01 | 50000   | NULL         | NULL
2024-02-01 | 55000   | 50000        | 10.00  ← +10% рост
2024-03-01 | 54500   | 55000        | -0.91  ← -0.91% падение

PARTITION BY vs GROUP BY

-- GROUP BY: сворачивает таблицу
SELECT 
  department,
  AVG(salary) as avg_salary
FROM employees
GROUP BY department;

Результат:
1 строка на отдел

-- OVER: сохраняет детальность
SELECT 
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;

Результат:
Все строки сотрудников + среднее их отдела

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

Window functions быстрее GROUP BY + JOIN:

-- Медленно (GROUP BY + JOIN)
SELECT 
  e.employee_id,
  e.salary,
  dept_avg.avg_salary
FROM employees e
JOIN (
  SELECT department, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
) dept_avg ON e.department = dept_avg.department;

-- Быстро (Window function)
SELECT 
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;

Вывод

Оконные функции — мощный инструмент для анализа.

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

• Агрегатные: SUM, AVG, COUNT (с OVER)
• Ранжирующие: ROW_NUMBER, RANK, DENSE_RANK
• Смещения: LAG, LEAD
• Процентные: PERCENT_RANK, NTILE

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

1. Нужны агрегаты БЕЗ сворачивания таблицы
2. Ранжирование (top N)
3. Сравнение с предыдущим периодом
4. Кумулятивные суммы
5. Скользящие средние

Преимущество: Нет необходимости в JOIN'ах и GROUP BY, что делает запросы проще и быстрее.