BigQuery の Numbering functions メモ

よく忘れるので調べてメモをする

Numbering functions

分析関数の一部

PARTITION BYで分類した各集合の順序を取得することができる

例：「アクセスログの中から各ユーザーが最初にアクセスした時刻を知りたい」ときとか

テンプレ

1SELECT 
2  * EXCEPT(rank)
3FROM (
4  SELECT 
5    *,  
6    RANK() OVER ( PARTITION BY user_id ORDER BY accessed_at ASC) as rank
7  FROM 
8    log
9)
10WHERE rank = 1

解説

  • 大きい構造としては、from 句の中のサブクエリで Numbering function を呼び、順序の情報を持ったテーブルを作ってから取り出したい順番の行だけを where 句で指定している

  • EXCEPTを指定することで順番に関する情報を消している

  • OVER句の中では以下を設定する

    • PARTITION BY：何で分類するか。複数カラムを設定することもできる

    • ORDER BY：何のカラムをもとにソートするか

  • RANKは名前の通り順位を返してくれる。そのため、同率のものがあった場合は同じrankになる行ができる。

    • 例：10, 20, 20, 40の値に対して小さい順でソートすることを考えたとき、 1, 2, 2, 4とrank付けしてくれる

  • DENSE_RANKは同率のものが存在しても順位の増加がかならず1つずつになる。

    • 例：10, 20, 20, 40の値に対して小さい順でソートすることを考えたとき、 1, 2, 2, 3とrank付けしてくれる

  • PERCENT_RANKはパーセンタイルを出してくれるイメージ。厳密な定義を見るとちょっと感覚が違うかもしれない。あまり使う機会無いかな

  • CUME_DISTは上位XXパーって感じ

  • NTILEは引数を一つもつ。分類した結果を引数の数で分割した結果を返してくれる。引数に3を設定したら、上位33%の行には1が、下位33%の行には3が、残りは2が入るイメージ。

  • ROW_NUMBERは純粋な行番号。同率を区別したいときとか確実に一個の行だけ取得したいときとかは使う

← Back to home