Оконные функции ROW_NUMBER, RANK, LAG: SQL до уровня эксперта с AI на ASI Biont

## Введение В мире современной аналитики данных 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()` присваивает уникальный номер каждой строке в окне. Часто используется для дедупликации или пагинации. ```sql SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept FROM employees; ``` **Пример с CTE для дедупликации:** ```sql 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()` — нет. ```sql 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()` позволяет обращаться к данным из предыдущей строки окна — например, для вычисления разницы с предыдущим периодом. ```sql 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 Оконные функции могут быть дорогими, особенно при больших объёмах данных. Ключевые моменты для оптимизации: 1. **Индексы под ORDER BY и PARTITION BY.** Если вы часто фильтруете по `department_id` и сортируете по `salary`, создайте составной индекс: `CREATE INDEX idx_dept_salary ON employees(department_id, salary DESC);`. 2. **Использование EXPLAIN ANALYZE.** Проверьте, использует ли запрос Index Scan или Seq Scan. Например: ```sql EXPLAIN ANALYZE SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) FROM employees; ``` 3. **Ограничение количества строк.** Если нужен топ-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. Начните с бесплатного модуля уже сегодня!

Оконные функции ROW_NUMBER, RANK, LAG: SQL до уровня эксперта с AI на ASI Biont

Комментарии

Читайте также

Основы охраны труда: ключевые понятия и обучение с AI на ASI Biont

On This Page: Новая эра Vibe Coding и контекстного AI от Model Context Protocol

Генерация синтетических данных с помощью LLM: методы, инструменты и лучшие практики

Aiogram 3: Основы и хендлеры для Telegram-ботов — обучение с AI на ASI Biont

ИИ для доступности: Как LLM расширяют возможности людей с инвалидностью

AI для маркетплейсов: как управлять карточками, ценами и аналитикой на Wildberries и Ozon

Excel и Google Таблицы: Обучение с AI на ASI Biont — ваш ключ к автоматизации и аналитике

Создание API на основе ИИ: лучшие практики интеграции LLM в продакшн

OpenAI o3 против DeepSeek R2: Битва моделей рассуждений 2026 года