BigQueryで行（row）に重複考慮してランク付けする方法

BigQueryでデータに順位を付けるにはどうするんだっけ？

データの分析や処理に便利なBigQuery(BQ)。

大規模データの処理に優れており、巨大なデータに対しても短時間でSQLを実行することができる。

データを扱っていると、データの中で順位付けをしたいことがある。

用途としては、カテゴリ別のデータの中で１～３位だけ抽出して表示したいみたいなものだ。

全体での順番は order by すればいいが、順位を表示するにはどうしたらいいのか。

順位を振れたとしても同率１位が４個あった場合、その中から３つ選ぶのは難しい。

重複無く順位を振るにはどうしたらいいのか。

そこで今回はBigQueryで行（row）に重複考慮してランク付けする方法について紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。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

結果を見ると、rank列に１位から１０位まで順位付けされている。

特徴は val=100 のデータが全て１位となっており、その次の val=110 のデータは４位となっているところだ。

つまり同率は全て同じ順位として扱い、同率の数分だけ次の順位をスキップしている。

DENSE_RANK

DENSE_RANK は順位をスキップしないランク付け関数だ。

使い方はとしてはRANKと同じで DENSE_RANK() OVER(ORDER BY 順位付けしたいカラム) となる。

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

結果を見ると、１位から７位までの順位付けになっているのがわかる。

RANK() との違いは val=110 のデータが４位ではなく２位となっているところだ。

つまり DENSE_RANK は同率は順位が複数あっても、次の順位はスキップせずに＋１として扱う。

ROW_NUMBER

ROW_NUMBER は順位付けというよりは行番号を振る関数だ。

だけど実は順位付けにも使えて、使い方はとしてはRANKと同じで ROW_NUMBER() OVER(ORDER BY 順位付けしたいカラム) となる。

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

結果を見ると、１位から１０位までの順位付けになっているのがわかる。

RANK() や DENSE_RANK()との違いは 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）に重複考慮してランク付けする方法について解説した。

BigQueryでデータにランク付けする方法には以下のような方法がある。

それぞれ特徴が以下の通り。

同率順位を許すかどうか、同率順位の次の順位をスキップするかどうかで使い分けると良いだろう。

ROW_NUMBER が重複対策に使えるのは意外でしたね。

他にもBigQueryのテクニックに関する記事もあるので、興味があれば見てみて欲しい。