💻Оконные функции - мощь аналитики в SQL (9/14)

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

Типы оконных функций и задачи, которые они решают

Агрегатные оконные функции - аналог агрегатных (SUM, AVG, COUNT) только с возможностью считать по окнам данных, не группируя весь набор строк. Например, подсчёт накопительной суммы или скользящего среднего.

Функции ранжирования - позволяют присваивать строкам ранги в пределах окна. К ним относятся ROW_NUMBER() (присваивает уникальный последовательный номер), RANK() (даёт одинаковый ранг одинаковым значениям, при этом пропускает следующие номера) и DENSE_RANK() (как RANK, но без пропусков).

Функции смещения - LAG() и LEAD() позволяют получить значения предыдущей или следующей строки, что полезно для вычисления разниц, сравнения и анализа трендов.

Примеры наиболее часто используемых оконных функций

Представим таблицу с продажами по клиентам: |customer_id|order_date| amount | |-----------|----------|--------| | 1         |2025-01-01| 100    | | 1         |2025-01-05| 150    | | 2         |2025-01-02| 200    | | 2         |2025-01-03| 50     | | 3         |2025-01-04| 300    | 1. ROW_NUMBER() - уникальный порядковый номер внутри группы

SELECT customer_id, order_date, amount,        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num FROM sales; Результат: |customer_id|order_date| amount | row_num | |-----------|----------|--------|---------| | 1         |2025-01-01| 100    | 1       | | 1         |2025-01-05| 150    | 2       | | 2         |2025-01-02| 200    | 1       | | 2         |2025-01-03| 50     | 2       | | 3         |2025-01-04| 300    | 1       | Полезно, например, чтобы отобрать первый заказ каждого клиента.

2. RANK() и DENSE_RANK() - ранжирование с учётом равных значений

Если у нескольких заказов одинаковая сумма, RANK() даст им одинаковый номер, но с пропусками впереди.

SELECT customer_id, amount,        RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank FROM sales; Результат (примеры с повторяющимися суммами в данных): | customer_id | amount | rank | |-------------|--------|------| | 1           | 150    | 1    | | 1           | 100    | 2    | | 2           | 200    | 1    | | 2           | 50     | 2    | DENSE_RANK() отличается тем, что пропусков в рангах нет.

3. SUM() OVER - накопительная сумма

Накопительная сумма показывает сумму всех предыдущих значений по определённому окну.

SELECT customer_id, order_date, amount,        SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_sum FROM sales; Результат: |customer_id|order_date| amount | cumulative_sum | |-----------|----------|--------|----------------| | 1         |2025-01-01| 100    | 100            | | 1         |2025-01-05| 150    | 250            | | 2         |2025-01-02| 200    | 200            | | 2         |2025-01-03| 50     | 250            | | 3         |2025-01-04| 300    | 300            | Это помогает видеть, как растёт объём продаж со временем у каждого клиента.

Почему они на столько крутые?

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

Запомните, даже самый спокойный медведь умеет рычать, когда надо. Берегите голову, берегите данные — и пусть в вашем дне будет немного тишины, ясности и добрых переменных.