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

Составляем рейтинг - RANK

На соревнованиях часто определяют места участников, исходя из суммы баллов за выступление. Бывает так, что разные участники набирают одинаковое количество баллов. В такой ситуации на одном месте оказывается несколько человек. В этом задании научимся определять места SQL запросом.

Проанализируем покупки клиентов. Для каждого товара найдем самую дешевую цену, по которой он продавался:

SELECT pi.product_id,
       min (pi.price) AS min_price
  FROM purchase_item pi
 GROUP BY pi.product_id
 ORDER BY min_price
#product_idmin_price
174900.00
2617800.00
3421500.00
4523500.00
5326100.00
6226100.00
7838200.00
8943800.00
91076800.00
1012100000.00
11111321800000.00

Обрати внимание на строки 5 и 6. Товары продавались по одной цене.

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

SELECT pi.product_id,
       row_number () over (order by min (pi.price)) AS position,
       min (pi.price) AS min_price
  FROM purchase_item pi
 GROUP BY pi.product_id
 ORDER BY min_price
#product_idpositionmin_price
1714900.00
26217800.00
34321500.00
45423500.00
53526100.00
62626100.00
78738200.00
89843800.00
910976800.00
101210100000.00
1111111321800000.00

Смотрим на строки 5 и 6

#product_idpositionmin_price
53526100.00
62626100.00

Так дело не пойдет. Чем продукт 2 хуже продукта 3? У них должно быть одинаковое место в рейтинге.

В SQL есть специальная функция ранжирования, которая проставит одинаковое место (ранг) для продуктов с одинаковой стоимостью. Она называется rank.

Заменим row_number() на rank() и посмотрим на результат:

SELECT pi.product_id,
       rank () over (order by min (pi.price)) AS position,
       min (pi.price) AS min_price
  FROM purchase_item pi
 GROUP BY pi.product_id
 ORDER BY min_price
#product_idpositionmin_price
1714900.00
26217800.00
34321500.00
45423500.00
53526100.00
62526100.00
78738200.00
89843800.00
910976800.00
101210100000.00
1111111321800000.00

Продукты 2 и 3 теперь занимают одинаковую позицию в рейтинге.

#product_idpositionmin_price
53526100.00
62526100.00

Обрати внимание, что после 5 позиции в рейтинге сразу идет 7-я, т.к. на пятом месте располагаются два товара.

#product_idpositionmin_price
53526100.00
62526100.00
78738200.00
ПредыдущаяСледующая