Функції вікна: рейтинги та підрахунки по групах

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

Це правильний інструмент для рейтингу, прогресивної нумерації, відсотків над сумою групи та багато інших операцій, які є незручними або неможливо з 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 тощо.

Спробуйте

Повторна вправа