tgoop.com/big_data_systems_analysis/134
Last Update:
Ранжирующие функции в SQL: как создавать рейтинги и топы
Привет! Сегодня поговорим о ранжирующих оконных функциях в SQL. С ними вы легко сможете находить лучшие продукты, оценивать эффективность сотрудников или составлять списки топовых клиентов.
Ранжирующие функции — это особый вид оконок. Они присваивают каждой строке таблицы номер (ранг) в рамках группы данных, определенной оператором OVER(). Этот номер может быть уникальным или учитывать равенство значений в строках.
В SQL есть три основные ранжирующие функции:
- ROW_NUMBER() или простая нумерация — присваивает уникальный номер каждой строке. Даже если значения в строках одинаковы, номера будут различаться.
- RANK() или ранжирование с пропусками — присваивает одинаковый ранг строкам с одинаковыми значениями. Следующая строка получает номер с пропуском на количество одинаковых значений (т.е., например, 1 1 1 4). Можно использовать, когда важно показать, сколько объектов находится выше по рейтингу.
- DENSE_RANK() или ранжирование без пропусков — похожа на RANK(), но не пропускает номера. Если несколько строк имеют одинаковый ранг, следующая строка получит номер, идущий непосредственно за ними (1 1 1 2). Пригодится для создания категорий или групп на основе значений.
Пример ранжирования с пропусками:
SELECT
product_name,
sales_amount,
DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM product_sales;
Результат:
| product_name | sales_amount | sales_rank |
|--------------|--------------|------------|
| iPhone | 100000 | 1 |
| MacBook | 100000 | 1 |
| AirPods | 80000 | 2 |
| iPad | 60000 | 3 |
Если нужна нумерация внутри групп, необходимо скомбинировать ранжирующие функции с
PARTITION BY. Например, разобъём данные на группы по категориям:
SELECT
category,
product_name,
sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_rank
FROM product_sales;
Функция присваивает ранг каждой строке в пределах группы (категории). Если две строки имеют одинаковое значение sales_amount, они получат одинаковый ранг, а следующая строка пропустит номер и возьмёт следующий. Не понятно?) Посмотрим на примере вывода:
| category | product_name | sales_amount | category_rank |
|----------|----------------|--------------|---------------|
| Phones | iPhone 13 | 150000 | 1 |
| Phones | Galaxy S21 | 130000 | 2 |
| Phones | Pixel 6 | 130000 | 2 |
| Phones | OnePlus 9 | 90000 | 4 |
| Laptops | MacBook Pro | 200000 | 1 |
| Laptops | Dell XPS | 180000 | 2 |
| Laptops | ThinkPad X1 | 150000 | 3 |
| Laptops | MateBook 14 | 150000 | 3 |
Ранжирующие функции полезны, если нужно создавать рейтинги или анализировать данные с учетом их позиции в наборе. Например, если нужно найти первую строчку в группе, определить топ-продавцов, сравнить позиции или ранжировать сотрудников по их результатам. Эти функции помогают решать задачи быстрее и проще, чем с использованием сложных подзапросов.
В следующих статьях мы разберем каждую функцию подробнее и посмотрим на более сложные примеры их применения. А пока попробуйте применить их к своим данным
#sql #оконные_функции