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

Синтаксис функция() 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 (), раздел станет единым для всей таблицы — функция посчитает значение по всем пяти строкам сразу. Это иногда полезно, например для вычисления доли каждой продажи от общего итога, но смысл механизма тот же: строки никуда не деваются.