SQL RANK

Существует множество различных оконных функций ранжирования…ROW_NUMBER, DENSE_RANK, RANK. Давайте начнем с самой базовой (RANK) и поговорим о том, что это такое, как ее использовать и почему она важна в аналитической инженерии.

Функция RANK — это эффективный способ создать столбец с рангами или отфильтровать запрос на основе ранжирования. Более конкретно, функция RANK возвращает ранг значения (начиная с 1) в упорядоченной группе или наборе данных. Важно отметить, что если несколько значений, обрабатываемых функцией ранжирования, одинаковы, они будут иметь одинаковый ранг.

Как использовать функцию RANK

Функция RANK имеет довольно простой синтаксис с необязательным полем разделения и поддержкой настройки порядка:

rank() over ([partition by <field(s)>] order by field(s) [asc | desc])

Некоторые примечания по синтаксису этой функции:

• Поле partition by является необязательным; если вы хотите ранжировать весь набор данных по определенным полям (по сравнению с разделением и ранжированием внутри набора данных), вы просто опустите partition by из вызова функции (см. пример ниже).
• По умолчанию порядок функции ранжирования установлен на возрастание. Чтобы явно установить порядок на убывание, вам нужно передать desc в часть order by функции.

Давайте рассмотрим практический пример использования функции RANK ниже.

Пример функции RANK

select
	order_id,
	order_date,
	rank() over (order by order_date) as order_rank
from {{ ref('orders') }}

Этот простой запрос, использующий таблицу orders из Jaffle Shop, вернет ранг заказов по их order_date:

order_id	order_date	order_rank
1	2018-01-01	1
2	2018-01-02	2
3	2018-01-04	3
4	2018-01-05	4
5	2018-01-05	4
6	2018-01-07	6

Некоторые примечания по этим результатам:

• Заказы, которые имеют одинаковую order_date (например, заказы 4 и 5), имеют одинаковый order_rank (4).
• order_rank заказа 6 равен 6 (если бы вы хотели, чтобы ранг был 5, вы бы использовали функцию DENSE_RANK).

Функции ранжирования, которые стоит знать

RANK — это всего лишь одна из функций ранжирования, которую специалисты по аналитической инженерии будут использовать в своих моделях данных. Существуют также DENSE_RANK и ROW_NUMBER, которые ранжируют строки иначе, чем RANK.

Синтаксис RANK в Snowflake, Databricks, BigQuery и Redshift

Большинство современных хранилищ данных поддерживают RANK и другие подобные функции ранжирования; синтаксис также одинаков для них. Используйте таблицу ниже, чтобы узнать больше о документации по функции RANK в вашем хранилище данных.

Хранилище данных	Поддержка RANK?
Snowflake	✅
Databricks	✅
Amazon Redshift	✅
Google BigQuery	✅

Примеры использования функции RANK

Мы чаще всего видим использование функции RANK в работе с данными для:

• В SELECT-запросах для добавления явного ранжирования строк
• В QUALIFY-запросах для фильтрации запроса по ранжированию без необходимости добавлять ранг в результат запроса

Это не исчерпывающий список того, где ваша команда может использовать функцию RANK в ваших моделях dbt и логике BI-инструментов, но он содержит некоторые общие сценарии, с которыми аналитические инженеры сталкиваются ежедневно.