Оконные функции: ранжирование и расчеты по группам

Оконная функция — это функция, которая работает с «группой строк». (окно), но — в отличие от GROUP BY — не уменьшает количество строки: значение, вычисленное в окне, прикрепляется к каждой строке окончательный результат.

Это правильный инструмент для ранжирования, прогрессивной нумерации и процентов. над общей суммой группы и многие другие операции, которые неудобны или невозможно с GROUP BY.

Синтаксис: function() OVER (…).

ROW_NUMBER() OVER (PARTITION BY colonna ORDER BY colonna2)

Компоненты:

• PARTITION BY — определяет «группы» (например, GROUP BY, но строки оставайтесь отдельно);
• ORDER BY внутри OVER(…) — определяет порядок внутри перегородка.

OVER () (пустые скобки) означает «вся таблица как одно окно». Полезно для вычислений типа «процент от общего количества».

Функции ранжирования

ROW_NUMBER() OVER (PARTITION BY p ORDER BY o)
RANK()       OVER (PARTITION BY p ORDER BY o)
DENSE_RANK() OVER (PARTITION BY p ORDER BY o)

Отличия (со значениями 100, 100, 90, 80):

Пример: рейтинг продукта по категории.

SELECT name,
       category_id,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM   products
ORDER BY category_id, rank;

Для каждого category_id RANK перезапускается с 1 и упорядочивается по убыванию цена: вы получаете рейтинг продуктов внутри каждой категории, в одной выстрел.

Прогрессивная нумерация

-- Progressive order number for each customer:
SELECT id,
       customer_id,
       ordered_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS row_num
FROM   orders
ORDER BY customer_id, row_num;

Первый заказ каждого клиента будет иметь row_num = 1, второй 2 и т. д.

Попробуйте это

Обзор упражнения