
BigQuery(BQ) очень полезен для анализа или обработки данных.
Он хорошо справляется с большими объемами данных. Он возвращает итоговый результат в короткие сроки.
При обработке данных иногда мы хотели бы сделать нумерацию.
Например, извлеките с первой по третью запись в каждой категории.
Чтобы получить рейтинг по всем данным, мы должны использовать предложение «order by».
Но как мы можем получить номер ранга?
А также, если 4 записи имеют одинаковую оценку, трудно выбрать 3 записи.
Как мы можем нумеровать записи без дублирования?
Итак, сегодня я расскажу о том, "как ранжировать данные с учетом дублирования в BigQuery".
Author

Преимущество для чтения
Вы можете понять «Как ранжировать данные с учетом дублирования в BigQuery». Тогда вам не нужно беспокоиться о нумерации.
Как ранжироваться в BigQuery
Есть несколько методов ранжирования в BigQuery.
Numbering in BQ
- RANK
- DENSE_RANK
- ROW_NUMBER
Это разные функции.
Итак, представьте каждую функцию.
Данные
В любом случае, мы должны подготовить данные.
Импортируйте этот CSV-файл как таблицу rank_sample.
Data
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 <имя столбца, которое вы хотите ранжировать>)
.
SQL
SELECT *, RANK() OVER(ORDER BY val) as rank FROM test.rank_sample order by id
Result
Row | id | val | rank |
1 | 1 | 100 | 1 |
2 | 2 | 120 | 5 |
3 | 3 | 130 | 7 |
4 | 4 | 100 | 1 |
5 | 5 | 110 | 4 |
6 | 6 | 160 | 8 |
7 | 7 | 170 | 9 |
8 | 8 | 100 | 1 |
9 | 9 | 120 | 5 |
10 | 10 | 200 | 10 |
В соответствии с результатом мы можем видеть с 1-го по 10-е место в столбце рангов.
Все записи val=100 являются первыми, а следующая запись val=110 — четвертой.
Это означает, что функция rank
рассматривает один и тот же результат как один и тот же рейтинг и пропускает следующий номер.
Reference
DENSE_RANK
DENSE_RANK
– это функция нумерации, которая не пропускает повторяющиеся ранги.
Мы можем использовать его как DENSE_RANK() OVER(ORDER BY <имя столбца>)
.
SQL
SELECT *, DENSE_RANK() OVER(ORDER BY val) as dense_rank FROM test.rank_sample order by id
Result
Row | id | val | dense_rank |
1 | 1 | 100 | 1 |
2 | 2 | 120 | 3 |
3 | 3 | 130 | 4 |
4 | 4 | 100 | 1 |
5 | 5 | 110 | 2 |
6 | 6 | 160 | 5 |
7 | 7 | 170 | 6 |
8 | 8 | 100 | 1 |
9 | 9 | 120 | 3 |
10 | 10 | 200 | 7 |
Мы видим, что ранг от ast до 7.
Отличие от RANK() состоит в том, что запись val=110
не 4-я, а 2-я.
Таким образом, DENSE_RANK
не пропускает повторяющиеся ранги.
Reference
ROW_NUMBER
ROW_NUMBER
не для ранжирования, а для установки номера строки.
Но мы можем использовать его как ROW_NUMBER() OVER(ORDER BY <имя столбца>)
так же, как функция RANK()
.
SQL
SELECT *, ROW_NUMBER() OVER(ORDER BY val) as dense_rank FROM test.rank_sample order by id
Result
Row | id | val | row_number |
1 | 1 | 100 | 1 |
2 | 2 | 120 | 5 |
3 | 3 | 130 | 7 |
4 | 4 | 100 | 2 |
5 | 5 | 110 | 4 |
6 | 6 | 160 | 8 |
7 | 7 | 170 | 9 |
8 | 8 | 100 | 3 |
9 | 9 | 120 | 6 |
10 | 10 | 200 | 10 |
По результату мы видим с 1-го по 10-е.
Уникальным моментом является то, что есть 2-я и 3-я записи в данных val=100
.
ROW_NUMBER
просто помещает номер строки. Таким образом, у них разное число даже в одинаковых записях результатов.
Reference
Сравнение
Мы классифицируем RANK, DENSE_RANK, ROW_NUMBER, как показано ниже.
Function | Description |
---|---|
RANK | Same rank in same score. If there are three 1st records,next is 4th. |
DENSE_RANK | Same rank in same score. If there are three 1st records,next is 2nd. |
ROW_NUMBER | Even in same score, it numbers different number. |
SQL
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
Result
Row | id | val | rank | dense_rank | row_number |
1 | 1 | 100 | 1 | 1 | 1 |
2 | 2 | 120 | 5 | 3 | 5 |
3 | 3 | 130 | 7 | 4 | 7 |
4 | 4 | 100 | 1 | 1 | 2 |
5 | 5 | 110 | 4 | 2 | 4 |
6 | 6 | 160 | 8 | 5 | 8 |
7 | 7 | 170 | 9 | 6 | 9 |
8 | 8 | 100 | 1 | 1 | 3 |
9 | 9 | 120 | 5 | 3 | 6 |
10 | 10 | 200 | 10 | 7 | 10 |
Заключение
Сегодня я рассказал о том, "как ранжировать данные с учетом дублирования в BigQuery".
Ниже приведены некоторые функции ранжирования.
Numbering function in BQ
Каждая функция следующая.
Function | Description |
---|---|
RANK | Same rank in same score. If there are three 1st records,next is 4th. |
DENSE_RANK | Same rank in same score. If there are three 1st records,next is 2nd. |
ROW_NUMBER | Even in same score, it numbers different number. |

ROW_NUMBER
, позволяющее избежать дублирования.Есть и другие статьи о BigQuey.
Если вы заинтересованы в них, пожалуйста, прочитайте их.
Read more