Оконные функции в SQL для аналитиков данных

1. Введение в оконные функции

Что такое оконные функции?

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

Отличие от агрегатных функций*

Агрегатные функции (например, SUM(), AVG(), COUNT()) сгруппируют строки и вернут одну строку для каждой группы.

Пример с агрегатной функцией:

SELECT 
    product_id,  
    SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Результат:

product_id	total_sales
1	500
2	300
3	700

В этом примере мы видим, как агрегатная функция SUM(amount) суммирует значения по каждому product_id, но все остальные данные, например, количество заказов для каждого товара, теряются.

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

Запрос с оконной функцией:

SELECT 
    product_id, 
    amount,
    SUM(amount) OVER (PARTITION BY product_id) AS total_sales
FROM sales;

Результат:

product_id	amount	total_sales
1	100	500
1	200	500
1	200	500
2	100	300
2	200	300
3	300	700
3	400	700

Теперь, в отличие от агрегатной функции, мы видим для каждой строки значение total_sales для соответствующего product_id, не теряя данные о каждом заказе.

---

2. Основные элементы оконных функций

Оконные функции обычно состоят из двух важных частей: PARTITION BY и ORDER BY.

• PARTITION BY делит набор данных на группы, как в случае с GROUP BY, но строки внутри каждой группы сохраняются.
• ORDER BY определяет порядок строк в окне для вычислений, что важно для таких функций, как ранжирование или подсчёт накопленных значений.
• ROWS/RANGE – определяет рамки окна (например, текущая строка и две предыдущие).

Пример с PARTITION BY и ORDER BY:

SELECT 
    order_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER () AS cumulative_sales
FROM orders;

Результат:

order_id	order_date	total_amount	cumulative_sales
101	2025-03-01	150	150
102	2025-03-01	200	350
103	2025-03-02	100	100
104	2025-03-02	300	400
105	2025-03-03	250	250

Здесь PARTITION BY order_date группирует строки по дате заказа, а ORDER BY order_date обеспечивает правильный порядок для вычисления накопленных продаж.

---

3. Использование ROWS BETWEEN

Когда нужно подсчитывать, например, скользящие суммы или средние значения для определённого диапазона строк, можно использовать ROWS BETWEEN. Эта конструкция позволяет определять диапазон строк, на основе которых будет вычисляться функция.

Пример с ROWS BETWEEN для подсчёта скользящей суммы:

SELECT 
    sale_date, 
    amount,
    SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum
FROM sales;

Результат:

sale_date	amount	moving_sum
2025-03-01	100	100
2025-03-02	150	250
2025-03-03	200	450
2025-03-04	50	400
2025-03-05	300	550

Здесь для каждой строки считается сумма amount для текущего дня и двух предыдущих. Это создаёт скользящую сумму, что полезно для анализа трендов.

---

4. Агрегатные оконные функции

Можно использовать оконные функции, такие как SUM(), AVG() и COUNT(), чтобы вычислять агрегатные показатели, но при этом не терять индивидуальные строки.

Пример запроса с SUM(), AVG() и COUNT():

SELECT 
    order_date, 
    total_amount,
    COUNT(*) OVER (PARTITION BY order_date) AS orders_count,
    AVG(total_amount) OVER (PARTITION BY order_date) AS avg_amount,
    SUM(total_amount) OVER (PARTITION BY order_date) AS total_sales
FROM orders;

Результат:

order_date	total_amount	orders_count	avg_amount	total_sales
2025-03-01	150	2	175	350
2025-03-01	200	2	175	350
2025-03-02	100	2	200	400
2025-03-02	300	2	200	400
2025-03-03	250	1	250	250

Здесь для каждой строки рассчитываются: количество заказов, среднее значение и суммарные продажи для каждой даты, но данные о каждом заказе сохраняются.

---

5. Функции ранжирования

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

Пример с ROW_NUMBER():

SELECT 
    customer_id, 
    order_id, 
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS order_rank
FROM orders;

Результат:

customer_id	order_id	total_amount	order_rank
1	101	150	2
1	102	200	1
2	103	100	2
2	104	300	1
3	105	250	1

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

---

6. RANK() и DENSE_RANK()

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

Пример с RANK() и DENSE_RANK():

SELECT 
    customer_id, 
    order_id, 
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS order_rank,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS dense_order_rank
FROM orders;

Результат:

customer_id	order_id	total_amount	order_rank	dense_order_rank
1	101	500	1	1
1	102	500	1	1
1	103	400	3	2
2	104	300	1	1
2	105	200	2	2
2	106	200	2	2

RANK(): Если два заказа имеют одинаковую сумму, то в следующем ранге будет пропущено одно место (например, два заказа с одинаковым значением получат 1-й ранг, а следующий — 3-й).
DENSE_RANK: В случае одинаковых значений ранки не пропускаются (например, два заказа с одинаковой суммой получат 1-й ранг, следующий будет 2-й).

---

7. LAG() и LEAD()

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

Пример с LAG():

SELECT 
    order_id, 
    order_date, 
    total_amount,
    LAG(total_amount, 1, 0) OVER (ORDER BY order_date) AS prev_order_amount,
    total_amount - LAG(total_amount, 1, 0) OVER (ORDER BY order_date) AS diff_from_prev
FROM orders;

Результат:

order_id	order_date	total_amount	prev_order_amount	diff_from_prev
101	2025-03-01	150	0	150
102	2025-03-01	200	150	50
103	2025-03-02	100	200	-100
104	2025-03-02	300	100	200
105	2025-03-03	250	300	-50

Здесь мы видим разницу между суммой текущего заказа и предыдущего.

---

8. FIRST_VALUE() и LAST_VALUE()

Функции FIRST_VALUE() и LAST_VALUE() позволяют получить первое или последнее значение в окне, что полезно для анализа первой или последней строки в наборе данных.

Пример с FIRST_VALUE():

SELECT 
    customer_id, 
    order_id, 
    order_date, 
    FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_date
FROM orders;

Результат:

customer_id	order_id	order_date	first_order_date
1	101	2025-03-01	2025-03-01
1	102	2025-03-02	2025-03-01
2	103	2025-03-02	2025-03-02
2	104	2025-03-03	2025-03-02
3	105	2025-03-04	2025-03-04

Здесь для каждого покупателя отображается дата его первого заказа.

Заключение и разбор ошибок

Типичные ошибки при использовании оконных функций

1. Отсутствие ORDER BY в оконных функциях – может привести к неожиданным результатам.
2. Неправильное использование ROWS BETWEEN – нужно точно задавать границы окна. Например, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING охватит одну строку до и одну после текущей, а ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW возьмёт все предыдущие строки до текущей.
3. Использование оконных функций в WHERE – они применяются только в SELECT, ORDER BY, HAVING.