Агрегатные оконные функции

Все агрегатные функции работают по одному принципу: на наборе данных выполняют арифметические вычисления и возвращают итоговое значение.

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

Главная прелесть оконных функций, что на одни и те же данные можно смотреть в разных разрезах в рамках одного запроса.

Например, проанализируем цены на товары.

Допустим, хотим знать самую большую стоимость товара в магазине:

SELECT pp.store_id,
       pp.product_id,
       pp.price,
       max(pp.price) over (PARTITION BY pp.store_id) as max_price_in_store
  FROM product_price pp
 ORDER BY pp.store_id, pp.price

#	store_id	product_id	price	max_price_in_store
1	201	6	17800.00	23500.00
2	201	4	21500.00	23500.00
3	201	3	22900.00	23500.00
4	201	5	23500.00	23500.00
5	300	1	10500.00	10500.00
...	...	...	...	...

Забыл, как работает PARTITION BY? Загляни сюда.

Посмотрели маркетологи на наш отчет и говорят: "Покажи-ка еще самый дорогой продукт по всем магазинам. А то самый дорогой продукт в магазине ни о чем не говорит..."

Ты им в ответ:

SELECT pp.store_id,
       pp.product_id,
       pp.price,
       max(pp.price) over (PARTITION BY pp.store_id) as max_price_in_store,
       max(pp.price) over () as max_price_total
  FROM product_price pp
 ORDER BY pp.store_id, pp.price

#	store_id	product_id	price	max_price_in_store	max_price_total
1	201	6	17800.00	23500.00	1321800000.00
2	201	4	21500.00	23500.00	1321800000.00
3	201	3	22900.00	23500.00	1321800000.00
4	201	5	23500.00	23500.00	1321800000.00
5	300	1	10500.00	10500.00	1321800000.00
...	...	...	...	...	...

"Выведи еще максимальную стоимость по этому продукту во всех магазинах..."

SELECT pp.store_id,
       pp.product_id,
       pp.price,
       max(pp.price) over (PARTITION BY pp.product_id) as max_price_product,
       max(pp.price) over (PARTITION BY pp.store_id) as max_price_in_store,
       max(pp.price) over () as max_price_total
  FROM product_price pp
 ORDER BY pp.store_id, pp.price

#	store_id	product_id	price	max_price_product	max_price_in_store	max_price_total
1	201	6	17800.00	17900.00	23500.00	1321800000.00
2	201	4	21500.00	22000.00	23500.00	1321800000.00
3	201	3	22900.00	22900.00	23500.00	1321800000.00
4	201	5	23500.00	24600.00	23500.00	1321800000.00
5	300	1	10500.00	12500.00	10500.00	1321800000.00
...	...	...	...	...	...	...

Агрегатных функций великое множество. Все их можно вызывать как с GROUP BY, так и в варианте оконной функции, добавив OVER (...).

В большинстве задач можно обойтись джентельменским набором, разобранным в теме агрегатных функций:

avg(выражение) - арифметическое среднее;
min(выражение) - минимальное значение выражения;
max(выражение) - маскимальное значение выражения;
sum(выражение) - сумма значений выражения;
count(*) - количество строк в результате запроса;
count(выражение) - количество значений выражения, не равных NULL.