Как ранжировать данные с учетом дублирования в BigQuery

Как ранжировать данные в BigQuery?

BigQuery(BQ) очень полезен для анализа или обработки данных.

Он хорошо справляется с большими объемами данных. Он возвращает итоговый результат в короткие сроки.

При обработке данных иногда мы хотели бы сделать нумерацию.

Например, извлеките с первой по третью запись в каждой категории.

Чтобы получить рейтинг по всем данным, мы должны использовать предложение «order by».

Но как мы можем получить номер ранга?

А также, если 4 записи имеют одинаковую оценку, трудно выбрать 3 записи.

Как мы можем нумеровать записи без дублирования?

Итак, сегодня я расскажу о том, "как ранжировать данные с учетом дублирования в BigQuery".

Author

Инженер среднего звена (ИИ, системы). Хорошо владеет Python и SQL.

Как ранжироваться в BigQuery

Есть несколько методов ранжирования в BigQuery.

Это разные функции.

Итак, представьте каждую функцию.

Данные

В любом случае, мы должны подготовить данные.

Импортируйте этот CSV-файл как таблицу rank_sample.

id,val
001,100
002,120
003,130
004,100
005,110
006,160
007,170
008,100
009,120
010,200

Используя эту таблицу, проверьте каждую функцию нумерации.

RANK

Как следует из названия, RANK — это функция ранжирования.

Мы можем использовать его как RANK() OVER(ORDER BY <имя столбца, которое вы хотите ранжировать>).

SELECT
*,
RANK() OVER(ORDER BY val) as rank
FROM test.rank_sample
order by id

В соответствии с результатом мы можем видеть с 1-го по 10-е место в столбце рангов.

Все записи val=100 являются первыми, а следующая запись val=110 — четвертой.

Это означает, что функция rank рассматривает один и тот же результат как один и тот же рейтинг и пропускает следующий номер.

DENSE_RANK

DENSE_RANK – это функция нумерации, которая не пропускает повторяющиеся ранги.

Мы можем использовать его как DENSE_RANK() OVER(ORDER BY <имя столбца>).

SELECT
*,
DENSE_RANK() OVER(ORDER BY val) as dense_rank
FROM test.rank_sample
order by id

Мы видим, что ранг от ast до 7.

Отличие от RANK() состоит в том, что запись val=110 не 4-я, а 2-я.

Таким образом, DENSE_RANK не пропускает повторяющиеся ранги.

ROW_NUMBER

ROW_NUMBER не для ранжирования, а для установки номера строки.

Но мы можем использовать его как ROW_NUMBER() OVER(ORDER BY <имя столбца>) так же, как функция RANK().

SELECT
*,
ROW_NUMBER() OVER(ORDER BY val) as dense_rank
FROM test.rank_sample
order by id

По результату мы видим с 1-го по 10-е.

Уникальным моментом является то, что есть 2-я и 3-я записи в данных val=100.

ROW_NUMBER просто помещает номер строки. Таким образом, у них разное число даже в одинаковых записях результатов.

Сравнение

Мы классифицируем RANK, DENSE_RANK, ROW_NUMBER, как показано ниже.

SELECT
*,
RANK() OVER(ORDER BY val) as rank,
DENSE_RANK() OVER(ORDER BY val) as dense_rank,
ROW_NUMBER() OVER(ORDER BY val) as row_number
FROM test.rank_sample
order by id

Заключение

Сегодня я рассказал о том, "как ранжировать данные с учетом дублирования в BigQuery".

Ниже приведены некоторые функции ранжирования.

Каждая функция следующая.

Впечатляет использование ROW_NUMBER, позволяющее избежать дублирования.

Есть и другие статьи о BigQuey.

Если вы заинтересованы в них, пожалуйста, прочитайте их.