Занятие 36. Summary session 9

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

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

Оконная функция выполняет вычисления для набора строк, объединённых по признаку. OVER задаёт окно, PARTITION BY делит строки на группы, ORDER BY в окне задаёт порядок.

GROUP BY сворачивает строки; окно — добавляет столбец.
Агрегирующие оконные функции: SUM, AVG, MIN, MAX, COUNT.
Кумулятивная сумма строится через ORDER BY внутри OVER.

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

📖 Что такое оконная функция

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

Выражение OVER

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

SELECT
    column1,
    window_function() OVER () AS result_column
FROM table_name;

PARTITION BY

PARTITION BY разделяет строки на группы (разделы) по значению выбранного поля. Вычисления выполняются для каждой группы независимо, но все строки сохраняются:

SELECT
    column1,
    window_function() OVER (PARTITION BY column2) AS result_column
FROM table_name;

✨ Отличие GROUP BY и оконных функций

Критерий	GROUP BY	Оконные функции
Цель	Группировка строк по одному или нескольким столбцам	Вычисления на основе набора строк, определяемых окном
Результат	Одна строка для каждой группы	Одна строка для каждой исходной строки с дополнительным столбцом
Детальные данные	Теряются — строки сворачиваются	Сохраняются

🔤 Синтаксис оконных функций

SELECT
    column1,
    window_function() OVER (
        PARTITION BY column2
        ORDER BY column3
    ) AS result_column
FROM table_name;

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

🧩 Основные концепции

Окно (Window) — набор строк, на который применяется оконная функция для текущей строки. Может охватывать все строки или их часть.
PARTITION BY — разделяет строки на разделы, аналогично GROUP BY, но сохраняет все строки.
ORDER BY — определяет порядок строк в каждом разделе перед применением функции.

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

Это функции, которые агрегируют данные в рамках окна, сохраняя при этом детальные строки:

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

📈 Кумулятивная сумма

Кумулятивная сумма — это сумма значений от начала до текущей строки. Чтобы получить её, в OVER добавляется ORDER BY:

SELECT
    sale_id,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM sales;

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

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

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