Концепция оконной функции и OVER с PARTITION BY

В прошлом уроке вы научились разбивать сложный запрос на именованные шаги с помощью CTE. Сегодня появится новая конструкция, которая работает иначе, чем всё, что вы видели раньше: она считает агрегаты, но не уничтожает детальные строки.

Оконная функция: агрегат без схлопывания строк и отличие от GROUP BY

Когда вы применяете GROUP BY category, база данных берёт все строки одной категории и сворачивает их в единственную строку-итог. Детали — каждый отдельный sale_id, каждый конкретный amount — исчезают: в результате остаётся ровно столько строк, сколько уникальных значений у столбца группировки. Это не побочный эффект, а суть операции.

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

Разница хорошо видна на числах. Возьмём таблицу sales с пятью строками и двумя категориями: три строки с «Книгами» и две с «Электроникой».

• GROUP BY category вернёт 2 строки — по одной на категорию.
• Оконная функция вернёт 5 строк — все исходные записи, у каждой из которых появится дополнительный столбец с суммой по её категории.

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

Когда это нужно на практике? Представьте, что нужно показать каждую продажу вместе с долей, которую она занимает в своей категории. В одном агрегирующем SELECT с GROUP BY детальные строки исчезнут. Вернуть детали и групповой итог в одном SQL-запросе можно через агрегирующий CTE или подзапрос с последующим JOIN, но оконная функция делает это короче и без отдельного соединения: конкретная сумма продажи и итог по категории оказываются в одной строке результата.

Синтаксис функция() OVER (PARTITION BY column) и SUM/COUNT на таблице продаж

Оконная функция записывается непосредственно в SELECT, после обычного аргумента функции добавляется ключевое слово OVER и в скобках — описание окна:

функция(столбец) OVER (PARTITION BY столбец_раздела)

PARTITION BY — это граница раздела. Он указывает базе данных, какие строки считать «связанными» для текущей строки: все строки с тем же значением столбец_раздела. Внутри каждого раздела функция вычисляется независимо.

Подготовим учебную таблицу:

CREATE TABLE sales (
  sale_id  INTEGER PRIMARY KEY,
  category TEXT,
  amount   INTEGER
);

INSERT INTO sales VALUES
  (1, 'Книги',       500),
  (2, 'Книги',       300),
  (3, 'Электроника', 1200),
  (4, 'Электроника', 800),
  (5, 'Книги',       200);

Теперь запрос с двумя оконными функциями:

SELECT
  sale_id,
  category,
  amount,
  SUM(amount)  OVER (PARTITION BY category) AS category_sum,
  COUNT(*)     OVER (PARTITION BY category) AS category_count
FROM sales;

Результат — ровно 5 строк:

sale_id	category	amount	category_sum	category_count
1	Книги	500	1000	3
2	Книги	300	1000	3
3	Электроника	1200	2000	2
4	Электроника	800	2000	2
5	Книги	200	1000	3

Для каждой строки «Книги» SUM просуммировал 500 + 300 + 200 = 1000, а COUNT насчитал три записи в этом разделе. Для «Электроники» — 1200 + 800 = 2000 и две записи. При этом ни одна строка не пропала: sale_id и amount остались на своих местах.

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

Ловушка: ожидание одной строки на категорию вместо полного набора строк

Рассмотрим типичную ошибку: нужен детальный список продаж с итогом по категории, но запрос написан через GROUP BY:

SELECT category, sale_id, SUM(amount) AS category_total
FROM sales
GROUP BY category;

Такой запрос схлопывает строки до одной на категорию. SQLite выполнит его, но возьмёт sale_id из неопределённой строки группы; строгие СУБД могут отклонить такой bare column. В любом случае sale_id не описывает всю группу и не даёт нужной детализации. Исправление начинается не с добавления ещё одного поля в GROUP BY, а с проверки зерна результата. Если одна строка должна означать одну продажу, группировка здесь вообще не нужна:

SELECT
    sale_id,
    category,
    amount,
    SUM(amount) OVER (PARTITION BY category) AS category_total
FROM sales;

Проверяйте запрос по шагам. Сначала выполните SELECT без оконного столбца и посчитайте детальные строки. Затем добавьте SUM(...) OVER и убедитесь, что те же sale_id сохранились, а итог повторяется внутри категории. Если число строк изменилось, ищите причину в других конструкциях: WHERE или HAVING, LIMIT, DISTINCT, GROUP BY либо кратности JOIN. Сама оконная функция входные строки не удаляет и не объединяет.