Занятие 34. Агрегирующие оконные функции

📁 Блок: SQL / MySQL / Оконные функции ⏱️ Время изучения: ~90 мин 🎯 Сложность: Продвинутая

⚡ Кратко: суть темы

Агрегирующие оконные функции считают SUM, AVG, MIN, MAX, COUNT по заданному окну строк. PARTITION BY делит строки на группы, а ORDER BY внутри окна включает накопление от начала до текущей строки.

Без ORDER BY в OVER функция возвращает одно значение на всё окно.
С ORDER BY получается кумулятивный или скользящий результат.

Частая ошибка: путать сортировку строк результата и сортировку строк внутри окна.

📖 Агрегирующие оконные функции

Это обычные агрегаты SUM, AVG, MIN, MAX, COUNT, к которым добавлено ключевое слово OVER. Они вычисляют итог по набору строк (окну), но, в отличие от GROUP BY, не убирают детальные строки.

Отличие от GROUP BY

GROUP BY customer_id сворачивает все заказы клиента в одну строку и выводит только агрегат. Оконная функция SUM(amount) OVER (PARTITION BY customer_id) добавляет столбец с суммой к каждому заказу клиента, сохраняя все строки.

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

SUM(expression) — сумма значений в окне.
AVG(expression) — среднее арифметическое.
MIN(expression) / MAX(expression) — минимум и максимум.
COUNT(*) — число строк; COUNT(expression) — число не-NULL значений.

Окно только с PARTITION BY

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

Кумулятивные значения

Кумулятивная сумма — это сумма значений от начала до текущей строки. Чтобы получить её, в OVER добавляют ORDER BY. Например, SUM(sale_amount) OVER (ORDER BY sale_date) накапливает продажи по дате.

⚠️ Проверить по документации: в конспекте сказано, что кумулятивные значения рассчитываются «только при использовании столбца с датами». На самом деле ORDER BY может ссылаться на любой столбец; даты используются чаще всего, потому что накопление обычно идёт во времени.

Скользящее (текущее) среднее

Комбинация PARTITION BY и ORDER BY позволяет считать накопленное среднее внутри каждой группы. Например, AVG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) покажет средний чек клиента на текущую дату с учётом всех его предыдущих заказов.

✨ Современные практики

Оконные функции часто заменяют самописные JOIN с подзапросами и делают запрос читаемее.
Для долей и отклонений используют value * 1.0 / SUM(value) OVER () или (MAX(...) OVER () - value) / MAX(...) OVER ().
Если нужен именно свёрнутый результат — одна строка на группу — используйте GROUP BY, а не окно.