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

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

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

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

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

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

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

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_idproduct_idpricemax_price_in_store
1201617800.0023500.00
2201421500.0023500.00
3201322900.0023500.00
4201523500.0023500.00
5300110500.0010500.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_idproduct_idpricemax_price_in_storemax_price_total
1201617800.0023500.001321800000.00
2201421500.0023500.001321800000.00
3201322900.0023500.001321800000.00
4201523500.0023500.001321800000.00
5300110500.0010500.001321800000.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_idproduct_idpricemax_price_productmax_price_in_storemax_price_total
1201617800.0017900.0023500.001321800000.00
2201421500.0022000.0023500.001321800000.00
3201322900.0022900.0023500.001321800000.00
4201523500.0024600.0023500.001321800000.00
5300110500.0012500.0010500.001321800000.00
.....................

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

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

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