Введение
В мире современной аналитики данных SQL остаётся незаменимым инструментом. Однако базовая выборка с GROUP BY и ORDER BY часто не справляется с задачами, требующими ранжирования, скользящих средних или сравнения строк. Здесь на помощь приходят оконные функции — мощный механизм, позволяющий выполнять вычисления над наборами строк, не теряя детализации. В этой статье мы разберём три ключевые функции: ROW_NUMBER, RANK и LAG, а также покажем, как их комбинировать с CTE и индексами для достижения production-уровня производительности. Курс SQL Mastery на платформе ASI Biont, основанный на обучении с AI, поможет вам освоить эти и другие продвинутые темы: от B-tree до full-text search.
Концепция оконных функций
Оконные функции работают в рамках «окна» — подмножества строк, определённого с помощью PARTITION BY и ORDER BY. В отличие от агрегатных функций, они не схлопывают строки: каждая строка сохраняется, а результат вычисляется для её контекста. Это идеально для задач ранжирования, кумулятивных сумм и лагов.
Синтаксис и примеры
ROW_NUMBER: нумерация строк
ROW_NUMBER() присваивает уникальный номер каждой строке в окне. Часто используется для дедупликации или пагинации.
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;
Пример с CTE для дедупликации:
WITH deduped AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn
FROM orders_history
)
SELECT * FROM deduped WHERE rn = 1;
RANK и DENSE_RANK: ранжирование с пропусками
RANK() оставляет пропуски при совпадении значений, DENSE_RANK() — нет.
SELECT
product_name,
sales,
RANK() OVER (ORDER BY sales DESC) AS rank_standard,
DENSE_RANK() OVER (ORDER BY sales DESC) AS rank_dense
FROM products;
LAG: доступ к предыдущей строке
LAG() позволяет обращаться к данным из предыдущей строки окна — например, для вычисления разницы с предыдущим периодом.
SELECT
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_revenue;
Оптимизация запросов с EXPLAIN ANALYZE
Оконные функции могут быть дорогими, особенно при больших объёмах данных. Ключевые моменты для оптимизации:
- Индексы под ORDER BY и PARTITION BY. Если вы часто фильтруете по
department_idи сортируете поsalary, создайте составной индекс:CREATE INDEX idx_dept_salary ON employees(department_id, salary DESC);. - Использование EXPLAIN ANALYZE. Проверьте, использует ли запрос Index Scan или Seq Scan. Например:
EXPLAIN ANALYZE
SELECT
employee_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM employees;
- Ограничение количества строк. Если нужен топ-3, используйте
LATERALили подзапрос сLIMIT.
Сравнение подходов
| Функция | Ключевое поведение | Типичное применение |
|---|---|---|
| ROW_NUMBER | Уникальный номер, без дубликатов | Пагинация, дедупликация |
| RANK | Пропуски при равенстве | Топ-N с учётом связей |
| LAG | Доступ к предыдущей строке | Вычисление дельты, скользящие метрики |
Заключение
Оконные функции — это не просто синтаксический сахар, а фундаментальный инструмент для аналитиков и разработчиков, работающих с реляционными базами данных. Освоив ROW_NUMBER, RANK и LAG, вы сможете писать элегантные и эффективные запросы, которые раньше требовали сложных подзапросов или хранимых процедур. Вместе с CTE, правильными индексами и пониманием query planning эти техники выводят ваш SQL на уровень эксперта.
Готовы углубиться? Платформа ASI Biont с обучением на основе AI предлагает практический курс SQL Mastery, где вы разберёте оконные функции, B-tree и GIN индексы, партиционирование и full-text search. Начните с бесплатного модуля уже сегодня!
Комментарии