Оконные функции

В этом уроке ты узнаешь

Объяснять, чем OVER (...) отличается от GROUP BY
Строить рейтинги с ROW_NUMBER, RANK и DENSE_RANK и ранжировать внутри групп с помощью PARTITION BY
Считать нарастающий итог с SUM() OVER (ORDER BY ...) и сравнивать соседние строки с помощью LAG и LEAD

Учитель хочет список по каждому курсу: балл каждого ученика, а рядом — средний балл курса и место ученика в нём. GROUP BY так не умеет: он сворачивает группу в одну строку, и имена теряются. Оконные функции оставляют каждую строку на месте и добавляют к ней значение, вычисленное по соседним строкам. Рейтинги, нарастающие итоги, «изменение по сравнению с прошлым месяцем» — большая часть аналитики пишется именно этим инструментом.

OVER: «окно» из строк

Определение

Оконная функция

Функция, которая для каждой строки вычисляется по набору связанных с ней строк — окну. Запись: функция(...) OVER (PARTITION BY ... ORDER BY ...). PARTITION BY делит окно на группы, а ORDER BY задаёт порядок строк внутри окна.

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

SQL· Измени код и запусти снова

SELECT e.course_id, e.student_id, e.score,
       ROUND(AVG(e.score) OVER (PARTITION BY e.course_id), 1) AS course_avg
FROM enrollments AS e
WHERE e.course_id IN (1, 3)
ORDER BY e.course_id, e.score DESC;

▸ Ожидаемый результат

course_id | student_id | score | course_avg
1 | 11 | 97 | 87.3
1 | 1 | 92 | 87.3
1 | 5 | 85 | 87.3
1 | 2 | 75 | 87.3
3 | 11 | 94 | 80
3 | 2 | 81 | 80
3 | 6 | 77 | 80
3 | 4 | 68 | 80

GROUP BY course_id дал бы здесь две строки, а оконная функция сохраняет все восемь.

ROW_NUMBER, RANK и DENSE_RANK

Три функции ранжирования различаются только при равных значениях. Отсортируем учеников по возрасту: троим 17 лет и троим 16. ROW_NUMBER не смотрит на равенство и выдаёт 1, 2, 3, поэтому добавляем уникальный второй столбец сортировки (id), чтобы результат был стабильным.

SQL· Измени код и запусти снова

SELECT first_name, age,
       ROW_NUMBER() OVER (ORDER BY age DESC, id) AS row_num,
       RANK()       OVER (ORDER BY age DESC)     AS rnk,
       DENSE_RANK() OVER (ORDER BY age DESC)     AS dense
FROM students
ORDER BY age DESC, id
LIMIT 7;

▸ Ожидаемый результат

first_name | age | row_num | rnk | dense
Elvin | 17 | 1 | 1 | 1
Tural | 17 | 2 | 1 | 1
Fidan | 17 | 3 | 1 | 1
Murad | 16 | 4 | 4 | 2
Rəşad | 16 | 5 | 4 | 2
Səbinə | 16 | 6 | 4 | 2
Aysel | 15 | 7 | 7 | 3

Функция	При равенстве	Для 17, 17, 16
ROW_NUMBER()	даёт каждой строке свой номер	1, 2, 3
RANK()	одинаковое место, затем пропуск	1, 1, 3
DENSE_RANK()	одинаковое место без пропусков	1, 1, 2

PARTITION BY: свой рейтинг в каждой группе

PARTITION BY course_id превращает каждый курс в отдельное окно, и нумерация на каждом курсе начинается с 1. Так решается задача «лучший ученик каждого курса»: сначала вычисляем места в CTE, затем оставляем строки с pos = 1.

SQL· Измени код и запусти снова

WITH ranked AS (
  SELECT c.title, s.first_name, e.score,
         RANK() OVER (PARTITION BY e.course_id ORDER BY e.score DESC) AS pos
  FROM enrollments AS e
  JOIN students AS s ON s.id = e.student_id
  JOIN courses  AS c ON c.id = e.course_id
)
SELECT title, first_name, score
FROM ranked
WHERE pos = 1
ORDER BY title;

▸ Ожидаемый результат

title | first_name | score
Algebra | Fidan | 97
English B1 | Leyla | 90
Geometry | Leyla | 95
Mechanics | Fidan | 94
Organic Chemistry | Elvin | 72
Python Basics | Rəşad | 99
World History | Nigar | 93

Нарастающий итог: SUM() OVER (ORDER BY ...)

Если написать внутри OVER предложение ORDER BY, окно становится «от начала до текущей строки». Поэтому SUM даёт нарастающий итог: выручка каждого месяца прибавляется к сумме предыдущих. Ниже мы сначала считаем выручку по месяцам в CTE, а потом применяем к ней оконную функцию.

SQL· Измени код и запусти снова

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

▸ Ожидаемый результат

month | revenue | running_total
2025-01 | 1691 | 1691
2025-02 | 963.99 | 2654.99
2025-03 | 124.98 | 2779.97
2025-04 | 935.99 | 3715.96
2025-05 | 42 | 3757.96
2025-06 | 152.1 | 3910.06
2025-07 | 32 | 3942.06

Есть тонкость: при ORDER BY рамка по умолчанию работает в режиме RANGE и берёт строки с одинаковым ключом сортировки (равноправные строки) вместе. Если у двух заказов одна дата, оба покажут полный итог за этот день. Чтобы итог рос построчно, добавь уникальный ключ или задай рамку явно: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SQL· Измени код и запусти снова

SELECT id, order_date, quantity,
       SUM(quantity) OVER (ORDER BY order_date)     AS by_range,
       SUM(quantity) OVER (ORDER BY order_date, id) AS by_row
FROM orders
WHERE order_date < '2025-05-01'
ORDER BY order_date, id;

▸ Ожидаемый результат

id | order_date | quantity | by_range | by_row
1 | 2025-01-15 | 1 | 3 | 1
2 | 2025-01-15 | 2 | 3 | 3
3 | 2025-02-02 | 20 | 23 | 23
4 | 2025-02-10 | 1 | 24 | 24
5 | 2025-03-05 | 1 | 25 | 25
6 | 2025-03-18 | 2 | 27 | 27
7 | 2025-04-09 | 1 | 31 | 28
8 | 2025-04-09 | 3 | 31 | 31

Заказы 1 и 2 сделаны в один день: by_range показывает 3 для обоих, а by_row — 1 и 3.

LAG и LEAD: сравнение с соседней строкой

LAG(x) возвращает значение из предыдущей строки окна, а LEAD(x) — из следующей. С ними «изменение по сравнению с прошлым месяцем» считается одним запросом. У первого месяца предыдущего нет, поэтому LAG там даёт NULL. LAG(x, 2) смотрит на две строки назад, а LAG(x, 1, 0) вместо NULL вернёт 0.

SQL· Измени код и запусти снова

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

▸ Ожидаемый результат

month | revenue | prev_revenue | change
2025-01 | 1691 | NULL | NULL
2025-02 | 963.99 | 1691 | -727.01
2025-03 | 124.98 | 963.99 | -839.01
2025-04 | 935.99 | 124.98 | 811.01
2025-05 | 42 | 935.99 | -893.99
2025-06 | 152.1 | 42 | 110.1
2025-07 | 32 | 152.1 | -120.1

Задание

Проранжируй товары по цене внутри каждой категории: самый дорогой получает 1-е место, пропусков в местах быть не должно. Выведи category, name, price и price_rank, отсортировав по категории, затем по месту.

Задание · SQL· Измени код и запусти снова

SELECT category, name, price
       -- add price_rank here
FROM products
ORDER BY category, price DESC;

▸ Ожидаемый результат

category | name | price | price_rank
Accessories | Backpack | 55 | 1
Accessories | Water bottle | 12 | 2
Electronics | Laptop | 1450 | 1
Electronics | Smartphone | 899.99 | 2
Electronics | Monitor | 310 | 3
Electronics | Headphones | 120.5 | 4
Games | Chess set | 42 | 1
Home | Desk lamp | 34.99 | 1
Stationery | Pen set | 7.9 | 1
Stationery | Notebook | 3.2 | 2

Задание

Для заказов покупателей 1, 2 и 3 вычисли собственный номер заказа каждого покупателя (order_no: 1, 2, 3... по дате, затем по id) и нарастающий итог количества товаров этого покупателя (running_qty). Выведи customer_id, order_date, quantity, order_no, running_qty, отсортировав по покупателю и номеру заказа.

Задание · SQL· Измени код и запусти снова

SELECT customer_id, order_date, quantity
       -- order_no and running_qty
FROM orders
WHERE customer_id IN (1, 2, 3)
ORDER BY customer_id, order_date, id;

▸ Ожидаемый результат

customer_id | order_date | quantity | order_no | running_qty
1 | 2025-01-15 | 1 | 1 | 1
1 | 2025-01-15 | 2 | 2 | 3
1 | 2025-07-07 | 10 | 3 | 13
2 | 2025-02-02 | 20 | 1 | 20
2 | 2025-06-30 | 1 | 2 | 21
3 | 2025-02-10 | 1 | 1 | 1
3 | 2025-05-21 | 1 | 2 | 2

Главное

Оконная функция не сворачивает строки: она добавляет к каждой строке значение, вычисленное по окну OVER (...).
PARTITION BY делит окно на группы, ORDER BY задаёт порядок внутри него.
При равенстве: ROW_NUMBER 1, 2, 3; RANK 1, 1, 3; DENSE_RANK 1, 1, 2.
SUM(x) OVER (ORDER BY ...) даёт нарастающий итог; при одинаковых ключах нужна рамка ROWS или уникальный ключ.
LAG возвращает значение предыдущей строки, LEAD — следующей; чтобы фильтровать по оконной функции, нужен CTE или подзапрос.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

В чём главное отличие оконной функции от GROUP BY?