Для чего используют оконные функции SQL?

Для чего используют оконные функции SQL?

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

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

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

Синтаксис:

FUNCTION_NAME() OVER (
  PARTITION BY column1, column2
  ORDER BY column3
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Основные категории оконных функций

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

• SUM() — сумма по окну
• AVG() — среднее значение
• COUNT() — количество строк
• MIN(), MAX() — минимум/максимум

Пример: Расчёт процента от общего оборота

SELECT 
  product,
  sales,
  SUM(sales) OVER () as total_sales,
  ROUND(100 * sales / SUM(sales) OVER (), 2) as percent
FROM orders;

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

• ROW_NUMBER() — уникальный номер строки в окне
• RANK() — ранг с пропусками при равенстве
• DENSE_RANK() — ранг без пропусков
• NTILE() — разбиение на квартили/децили

Пример: Top-3 продукты по продажам в каждой категории

SELECT *
FROM (
  SELECT 
    category,
    product,
    sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank
  FROM products
) sub
WHERE rank <= 3;

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

• LAG() — значение из предыдущей строки
• LEAD() — значение из следующей строки
• FIRST_VALUE() — первое значение в окне
• LAST_VALUE() — последнее значение в окне

Пример: Расчёт изменения продаж день-к-дню

SELECT 
  date,
  sales,
  LAG(sales) OVER (ORDER BY date) as prev_day_sales,
  sales - LAG(sales) OVER (ORDER BY date) as day_change
FROM daily_sales
ORDER BY date;

Практические сценарии использования

1. Накопительная сумма (Running Total)

SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumsum
FROM transactions;

Использование: отслеживание прогресса проекта, накопление дохода.

2. Скользящее среднее

SELECT 
  date,
  temperature,
  AVG(temperature) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7
FROM weather;

Использование: сглаживание временных рядов, анализ тренда.

3. Сравнение с предыдущим периодом

SELECT 
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) as growth_percent
FROM monthly_revenue;

Использование: анализ динамики KPI, отчёты для руководства.

4. Дублирующиеся значения (Деинг)

SELECT 
  id,
  email,
  ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn
FROM users
WHERE ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) > 1;

Использование: поиск дубликатов, очистка данных.

5. Разбиение на группы

SELECT 
  customer_id,
  NTILE(4) OVER (ORDER BY total_spent DESC) as quartile
FROM customers;

Использование: сегментация клиентов, анализ RFM.

Преимущества оконных функций

1. Не требуют GROUP BY — сохраняют все исходные столбцы
2. Гибкость — можно определить любое подмножество данных (PARTITION BY)
3. Порядок — поддерживают ORDER BY для упорядочивания
4. Производительность — часто быстрее, чем подзапросы с LEFT JOIN
5. Читаемость — код более понятный и поддерживаемый

Сравнение с GROUP BY

GROUP BY:

SELECT category, SUM(sales) FROM products GROUP BY category;
-- Результат: 1 строка на категорию

Оконная функция:

SELECT product, sales, SUM(sales) OVER (PARTITION BY category) FROM products;
-- Результат: все строки + сумма по категории для каждой строки

Совет System Analyst

1. Оконные функции критичны для аналитики и reporting
2. Используй их вместо сложных подзапросов с JOIN
3. PARTITION BY — твой инструмент для анализа по группам
4. Всегда указывай ORDER BY для ранжирующих функций
5. Помни про производительность на больших таблицах (требуют индексов)

Оконные функции — это часто вопрос на собеседованиях System Analyst и аналитиков, так как они показывают понимание работы с данными.