Оконные функции SQL: продвинутый анализ данных без сложностей

Для кого эта статья:

• Специалисты в области анализа данных и разработки SQL-запросов
• Студенты и начинающие аналитики, желающие углубить свои знания SQL

• Профессионалы, занимающиеся оптимизацией процессов обработки данных и отчетности

  SQL-запросы с оконными функциями — мощный инструмент, который радикально меняет способ анализа данных. Когда стандартные агрегации и GROUP BY уже не справляются, на сцену выходят оконные функции, позволяющие выполнять сложные аналитические вычисления без потери детализации исходных строк. Они превращают многострочные запросы в элегантные однострочные решения, экономя время разработки и вычислительные ресурсы. Готовы увидеть, как ROW_NUMBER, RANK и другие оконные функции могут упростить ваши SQL-сценарии и раскрыть скрытые возможности ваших данных? 🚀

Оконные функции — это один из тех инструментов SQL, которые резко отделяют новичков от профессионалов. На нашем курсе Обучение SQL с нуля в Skypro мы уделяем особое внимание этим мощным конструкциям. Всего за 6 недель вы пройдете путь от простых запросов до продвинутой аналитики с оконными функциями. Наши студенты не просто изучают теорию — они сразу применяют знания на реальных проектах, что делает обучение по-настоящему ценным для карьеры.

Что такое оконные функции в SQL и почему они важны

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

В чем их практическая ценность? Представьте, что вам нужно рассчитать скользящее среднее продаж за последние 3 месяца или определить позицию каждого сотрудника по зарплате в рамках отдела. Без оконных функций вам пришлось бы писать подзапросы, соединять таблицы и выполнять множество операций — оконные функции решают эти задачи одним элегантным выражением.

Алексей Петров, Lead Data Engineer

Помню случай, когда мне поручили оптимизировать отчетность по эффективности маркетинговых кампаний. Система генерировала данные каждый час, и нам требовалось видеть, как меняются ключевые метрики относительно предыдущих периодов. Изначально запрос содержал 7 подзапросов и выполнялся более 3 минут, что было неприемлемо для дашборда реального времени.

Перепроектировав решение с использованием оконных функций, я сократил запрос до 25 строк кода, а время выполнения — до 8 секунд. Функции LAG позволили элегантно получить предыдущие значения метрик, RANK помог выделить лучшие кампании, а скользящие суммы через SUM() OVER() обеспечили отличную визуализацию трендов. Клиент был в восторге — оконные функции literally спасли проект.

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

• Производительность: они значительно эффективнее, чем аналогичные решения с подзапросами
• Читаемость кода: делают SQL-запросы более компактными и понятными
• Аналитические возможности: позволяют выполнять сложные расчеты в один проход
• Гибкость: работают с различными "окнами" данных, включая скользящие диапазоны

Оконные функции доступны в большинстве современных СУБД, включая PostgreSQL, Oracle, SQL Server, MySQL (начиная с версии 8.0) и многие другие. Однако есть небольшие различия в синтаксисе и функциональности между разными системами.

Основной синтаксис оконных функций SQL: OVER, PARTITION BY

Базовый синтаксис оконной функции выглядит следующим образом:

function_name(arguments) OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column3, column4, ...]
[frame_clause]
)

Разберем ключевые элементы:

• function_name — это непосредственно функция (ROW_NUMBER, RANK, SUM и т.д.)
• OVER — ключевое слово, которое превращает обычную функцию в оконную
• PARTITION BY — необязательный параметр, который разделяет результат на разделы (окна)
• ORDER BY — определяет порядок строк в каждом разделе
• frame_clause — необязательное указание диапазона строк в разделе (ROWS, RANGE, GROUPS)

Для иллюстрации синтаксиса рассмотрим пример с таблицей продаж:

SELECT 
product_id,
category,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY category) AS category_total,
SUM(amount) OVER () AS grand_total
FROM 
sales
WHERE 
sale_date >= '2023-01-01';

В этом запросе мы получаем:

• Исходные данные о продажах (без группировки)
• Общую сумму продаж для каждой категории товаров
• Общую сумму продаж по всей таблице

Важно понимать, что без PARTITION BY оконная функция применяется ко всем строкам результата как к единым окну. Добавляя PARTITION BY, мы создаем отдельные окна для каждого уникального значения или комбинации значений указанных столбцов.

Мария Соколова, Data Analyst

Несколько лет назад я работала над проектом анализа эффективности колл-центра. Нам требовалось отслеживать производительность операторов и выявлять аномалии в обработке звонков. Данные поступали в режиме реального времени, и традиционный подход с GROUP BY не подходил — нам нужно было сохранить детализацию по каждому звонку.

Внедрение оконных функций стало переломным моментом. С помощью PARTITION BY мы разделили звонки по отделам и операторам, а с помощью ORDER BY и скользящих окон вычисляли среднее время обработки за последние 10 звонков для каждого оператора. Функция PERCENT_RANK помогла выявлять самые проблемные кейсы в общем потоке.

Самым впечатляющим оказался эффект от визуализации этих данных — руководство увидело, что некоторые операторы систематически перегружены, а рабочие процессы требуют оптимизации. После реорганизации среднее время ожидания клиентов сократилось на 42%, а удовлетворенность выросла на 18%.

Мощь оконных функций проявляется в работе с временными рядами. Например, мы можем рассчитать скользящее среднее за последние 3 дня:

SELECT 
product_id,
sale_date,
amount,
AVG(amount) OVER (
PARTITION BY product_id 
ORDER BY sale_date 
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3days
FROM 
sales;

Здесь frame_clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW определяет окно как текущую строку и две предшествующие ей строки. Это критично для расчетов скользящих показателей. 📊

Ранжирование данных: ROW

Функции ранжирования — одни из самых популярных оконных функций в SQL. Они позволяют присваивать порядковые номера или ранги строкам в результате запроса на основе определенного порядка. Разберем три основные функции ранжирования:

Чтобы лучше понять различия, рассмотрим пример с таблицей сотрудников и их зарплатами:

SELECT 
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM 
employees;

Если у нас есть два сотрудника с одинаковой зарплатой в одном отделе, то результаты будут различаться:

• ROW_NUMBER() — присвоит уникальные номера (например, 1 и 2)
• RANK() — присвоит одинаковые ранги и пропустит следующий (например, 1, 1, 3)
• DENSE_RANK() — присвоит одинаковые ранги без пропуска (например, 1, 1, 2)

Типичные применения функций ранжирования включают:

1. Определение топ-N записей в каждой категории
2. Выявление дубликатов в данных
3. Создание последовательных идентификаторов
4. Пагинацию результатов запросов
5. Построение рейтингов и лидербордов

Для выборки топ-3 продуктов по продажам в каждой категории можно использовать следующий запрос:

WITH RankedProducts AS (
SELECT 
product_name,
category,
total_sales,
DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS sales_rank
FROM 
products
)
SELECT 
product_name,
category,
total_sales
FROM 
RankedProducts
WHERE 
sales_rank <= 3
ORDER BY 
category, 
sales_rank;

Еще один практический пример — выявление дубликатов строк в таблице:

WITH DuplicateCheck AS (
SELECT 
*,
ROW_NUMBER() OVER (
PARTITION BY customer_id, order_date, product_id
ORDER BY (SELECT NULL)
) AS row_num
FROM 
orders
)
SELECT 
*
FROM 
DuplicateCheck
WHERE 
row_num > 1;

Здесь мы используем ROW_NUMBER() для нумерации строк с одинаковыми значениями ключевых полей. Любая строка с номером больше 1 является дубликатом. 🔍

Аналитические расчеты: SUM, AVG, MIN/MAX в окнах данных

Стандартные агрегатные функции SQL (SUM, AVG, MIN, MAX, COUNT) при использовании с конструкцией OVER превращаются в мощные аналитические инструменты. Они позволяют выполнять вычисления по группам данных без сокращения количества строк в результате, что критически важно для аналитической обработки.

Основной синтаксис аналитических функций:

aggregate_function(column) OVER (
[PARTITION BY columns]
[ORDER BY columns]
[frame_clause]
)

Рассмотрим несколько практических примеров для демонстрации возможностей этих функций:

SELECT 
order_id,
customer_id,
order_date,
order_amount,
-- Общая сумма заказов по клиенту
SUM(order_amount) OVER (PARTITION BY customer_id) AS customer_total,
-- Средняя сумма заказа по клиенту
AVG(order_amount) OVER (PARTITION BY customer_id) AS customer_avg,
-- Процент от общей суммы заказов клиента
order_amount / SUM(order_amount) OVER (PARTITION BY customer_id) * 100 AS percent_of_total,
-- Нарастающий итог по датам для клиента
SUM(order_amount) OVER (
PARTITION BY customer_id 
ORDER BY order_date
ROWS UNBOUNDED PRECEDING
) AS running_total,
-- Скользящее среднее за последние 3 заказа
AVG(order_amount) OVER (
PARTITION BY customer_id 
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3_orders
FROM 
orders
ORDER BY 
customer_id, 
order_date;

Ключевые моменты, на которые стоит обратить внимание:

• Без указания ORDER BY и frame_clause функция применяется ко всему разделу
• ORDER BY добавляет упорядоченность, что важно для нарастающих итогов
• Рамка окна (frame_clause) определяет, какие строки включаются в вычисление

Типичные варианты frame_clause:

1. ROWS UNBOUNDED PRECEDING — от начала раздела до текущей строки
2. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — весь раздел
3. ROWS BETWEEN n PRECEDING AND n FOLLOWING — n строк до и после текущей
4. ROWS BETWEEN n PRECEDING AND CURRENT ROW — n строк до текущей и сама текущая

Аналитические функции особенно полезны для финансового анализа. Например, для расчета скользящей суммы продаж за последние 30 дней:

SELECT 
sale_date,
daily_sales,
SUM(daily_sales) OVER (
ORDER BY sale_date 
RANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW
) AS rolling_30day_sales
FROM 
daily_sales_table;

Обратите внимание на использование RANGE вместо ROWS. В этом случае мы работаем с логическим диапазоном дат, а не с физическими строками. Это важно, когда в данных могут быть пропуски (например, нет продаж в выходные). 📆

Еще один полезный прием — расчет процентилей с помощью PERCENT_RANK:

SELECT 
product_id,
product_name,
price,
PERCENT_RANK() OVER (ORDER BY price) * 100 AS price_percentile
FROM 
products;

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

Работа с соседними строками: функции LEAD и LAG

Функции LEAD и LAG — это специальные оконные функции в SQL Oracle и других СУБД, которые позволяют получить доступ к значениям из предыдущих или последующих строк без необходимости выполнять соединения таблицы с самой собой. Это критически важно для анализа временных рядов и выявления изменений между последовательными записями.

Синтаксис этих функций:

-- Получение значения из последующей строки
LEAD(column, offset, default) OVER (
[PARTITION BY columns]
ORDER BY columns
)

-- Получение значения из предыдущей строки
LAG(column, offset, default) OVER (
[PARTITION BY columns]
ORDER BY columns
)

Где:

• column — столбец, значение которого нужно получить
• offset — на сколько строк смещаться (по умолчанию 1)
• default — значение по умолчанию, если строки не существует

Рассмотрим пример анализа изменения цен на акции:

SELECT 
stock_date,
stock_symbol,
closing_price,
-- Цена закрытия предыдущего дня
LAG(closing_price, 1) OVER (
PARTITION BY stock_symbol 
ORDER BY stock_date
) AS prev_day_price,
-- Изменение в процентах
(closing_price – LAG(closing_price, 1, closing_price) OVER (
PARTITION BY stock_symbol 
ORDER BY stock_date
)) / LAG(closing_price, 1, closing_price) OVER (
PARTITION BY stock_symbol 
ORDER BY stock_date
) * 100 AS price_change_percent,
-- Цена закрытия следующего дня
LEAD(closing_price, 1) OVER (
PARTITION BY stock_symbol 
ORDER BY stock_date
) AS next_day_price
FROM 
stock_prices
WHERE 
stock_date BETWEEN '2023-01-01' AND '2023-01-31'
ORDER BY 
stock_symbol, 
stock_date;

Практические применения LEAD и LAG включают:

1. Расчет изменений между последовательными периодами
2. Выявление аномалий и выбросов в данных
3. Определение трендов и точек перелома
4. Расчет скорости изменения (например, ускорения продаж)
5. Поиск пропусков в последовательных данных

Для обнаружения аномальных скачков цен можно использовать следующий подход:

WITH PriceChanges AS (
SELECT 
product_id,
price_date,
price,
LAG(price) OVER (
PARTITION BY product_id 
ORDER BY price_date
) AS prev_price,
(price – LAG(price) OVER (
PARTITION BY product_id 
ORDER BY price_date
)) / LAG(price) OVER (
PARTITION BY product_id 
ORDER BY price_date
) * 100 AS price_change_percent
FROM 
product_prices
)
SELECT 
*
FROM 
PriceChanges
WHERE 
ABS(price_change_percent) > 20 -- Изменение более 20%
ORDER BY 
ABS(price_change_percent) DESC;

Интересный прием — использование LEAD и LAG одновременно для анализа временных интервалов:

SELECT 
user_id,
login_time,
logout_time,
-- Время до следующего логина
LEAD(login_time) OVER (
PARTITION BY user_id 
ORDER BY login_time
) – logout_time AS time_until_next_session,
-- Время отсутствия активности в системе
login_time – LAG(logout_time) OVER (
PARTITION BY user_id 
ORDER BY login_time
) AS inactivity_time
FROM 
user_sessions
ORDER BY 
user_id, 
login_time;

Такой анализ помогает понять паттерны использования системы и выявить периоды пиковой нагрузки. 🕒

Еще один мощный прием — сочетание LEAD/LAG с другими оконными функциями. Например, для выявления серий последовательных покупок:

WITH CustomerPurchases AS (
SELECT 
customer_id,
purchase_date,
purchase_amount,
purchase_date – LAG(purchase_date) OVER (
PARTITION BY customer_id 
ORDER BY purchase_date
) AS days_since_last_purchase
FROM 
purchases
)
SELECT 
customer_id,
COUNT(*) AS total_purchases,
COUNT(CASE WHEN days_since_last_purchase <= 7 THEN 1 END) AS weekly_repeat_purchases,
MAX(
COUNT(CASE WHEN days_since_last_purchase <= 7 THEN 1 END)
OVER (PARTITION BY customer_id ORDER BY purchase_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
) AS longest_purchase_streak
FROM 
CustomerPurchases
GROUP BY 
customer_id
ORDER BY 
longest_purchase_streak DESC;

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

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

• Основные операторы SQL
• RIGHT JOIN в SQL: полное руководство для разработчика баз данных
• Группировка данных с помощью GROUP BY в SQL
• FULL JOIN в SQL: полное объединение таблиц для анализа данных
• Сложные задачи и кейсы по SQL
• SQL PIVOT: преобразование строк в столбцы для мощной аналитики
• Оптимизация SQL запросов
• LEFT JOIN в SQL: полное руководство с примерами и оптимизацией
• Как устранить распространенные ошибки в SQL-запросах: руководство
• SQL для аналитики данных: от базовых запросов к бизнес-инсайтам