DB

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

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

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

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


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

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

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

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

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

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

この記事を書いている人


システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

記事を読むメリット

BigQueryで行(row)に重複考慮してランク付けする方法がわかり、部分データ抽出が得意になる。


BigQueryでランク付けする方法

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

BQの順位付けに使える関数

  • RANK
  • DENSE_RANK
  • ROW_NUMBER
  • それぞれ特徴が異なるので個別に紹介する。



    データ準備

    まずはランク付けの元となるデータを準備する。

    以下の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 順位付けしたいカラム) となる。

    SQL

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


    結果

    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


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

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

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




    DENSE_RANK

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

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

    SQL

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


    結果

    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


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

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

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




    ROW_NUMBER

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

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

    SQL

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


    結果

    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位までの順位付けになっているのがわかる。

    RANK()DENSE_RANK()との違いは val=100 のデータの中に2位や3位がいるところだ。

    つまり ROW_NUMBER は行番号を振るだけなので同率データがあっても別の順位番号が振られる




    比較

    RANKDENSE_RANKROW_NUMBER を比較するとこのようになる。

    関数 説明
    RANK 同率は同じ順位になり、同率の数分次の順位はスキップされる。
    DENSE_RANK 同率は同じ順位になり、同率順位の次はスキップされずに+1の順位が付く。
    ROW_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
    


    結果

    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で行(row)に重複考慮してランク付けする方法について解説した。

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

    BQの順位付けに使える関数

  • RANK
  • DENSE_RANK
  • ROW_NUMBER

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

    関数 説明
    RANK 同率は同じ順位になり、同率の数分次の順位はスキップされる。
    DENSE_RANK 同率は同じ順位になり、同率順位の次はスキップされずに+1の順位が付く。
    ROW_NUMBER 行番号を振るだけなので同率でも別順位を振る。


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


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



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

    ITipsと同じようなブログを作る方法

    ブログに興味がありますか?

    もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

    サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
    同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

    ランキング参加中

    にほんブログ村 IT技術ブログへ

    他にもブログやSNSで紹介してくれると励みになります。

    はてブのコメントで酷評されると泣きます(´;ω;`)

    -DB
    -

    © 2022 ITips