Для чего нужны оконные функции?

Применение и назначение оконных функций в SQL

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

Основные цели использования оконных функций

1. Вычисления с контекстом

Оконные функции позволяют анализировать данные, сохраняя детализацию исходных строк. Например, можно вычислить долю продаж каждого товара в общем объёме, не теряя строки с конкретными значениями.

SELECT 
   product_id,
   sales,
   SUM(sales) OVER () as total_sales,
   sales * 1.0 / SUM(sales) OVER () as sales_ratio
FROM sales_data;

2. Ранжирование и нумерация строк

Часто используются для:

• Присвоения порядковых номеров (ROW_NUMBER)
• Определения рейтинга с пропусками/без пропусков (RANK, DENSE_RANK)
• Разбиения на группы (NTILE)

SELECT 
   employee_id,
   department,
   salary,
   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
   RANK() OVER (ORDER BY salary DESC) as company_rank
FROM employees;

3. Аналитические вычисления

• Скользящие средние для временных рядов
• Суммы нарастающим итогом (cumulative sum)
• Сравнение со смежными строками (LAG, LEAD)

SELECT 
   date,
   revenue,
   AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
   SUM(revenue) OVER (ORDER BY date) as cumulative_sum,
   LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue
FROM daily_sales;

4. Статистический анализ

• Вычисление процентилей
• Определение медианных значений
• Анализ распределения данных

Ключевые аспекты синтаксиса

Оконные функции используют три основных компонента:

PARTITION BY

Разделяет данные на логические группы (аналогично GROUP BY, но без сворачивания). Внутри каждой партиции вычисления выполняются независимо.

ORDER BY

Определяет порядок строк внутри окна, что критично для функций ранжирования и накопительных итогов.

Фрейм окна (ROWS/RANGE)

Точное определение границ «окна» для вычислений:

-- Разные типы фреймов
SUM(value) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- Накопительная сумма от начала до текущей строки

AVG(value) OVER (ORDER BY date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW)
-- Среднее за последние 7 дней

Преимущества перед альтернативными подходами

1. Производительность — оконные функции обычно оптимизированы СУБД и выполняются быстрее, чем коррелированные подзапросы.

2. Читаемость — код с оконными функциями более компактен и понятен по сравнению с многоуровневыми подзапросами.

3. Гибкость — можно комбинировать различные оконные функции в одном запросе с разными условиями партиционирования.

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

Финансовый анализ

-- Вычисление разницы с предыдущим периодом
SELECT 
   month,
   revenue,
   revenue - LAG(revenue, 1) OVER (ORDER BY month) as monthly_growth
FROM financials;

Анализ пользовательской активности

-- Поиск первой и последней активности пользователя
SELECT 
   user_id,
   event_time,
   FIRST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY event_time) as first_event,
   LAST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY event_time 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_event
FROM user_events;

Ограничения и особенности

1. Оконные функции вычисляются после операций WHERE, GROUP BY, HAVING, но перед ORDER BY на уровне запроса.
2. Нельзя использовать в условиях WHERE напрямую (требуется обёртка в подзапрос).
3. В разных СУБД могут быть особенности реализации (например, в MySQL оконные функции появились только в версии 8.0).

Заключение

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