こんにちは、BigQueryで好きな関数、RANK() OVER(PARTITION BY A ORDER BY B)のとりゅふです。
BigQueryでデータ分析といえば、やっぱりWindow関数ですよね。
このWindow関数、今まではSELECT句にしか書けなかったのですが、2021年5月10日にプレビューリリースされたQUALIFY句によって、なんと条件として指定することができるようになったのです！
SUM()などの集計関数の結果をHAVING区でフィルタリングするときと同じような使い方ができます。早速SQLを書いて検証していこうと思います！
今回使うデータセット、テーブル
本記事に記載のSQLで参照しているデータセット、テーブルは、私の個人プロジェクトにある以下のものを利用しています。
- データセット：
truefly - テーブル：
truefly.census
Window関数のおさらい
知っている！って方は次の「QUALIFYでフィルタリングする」までジャンプ！
Window関数ってなんやねん
通常の関数は１行ごとに値を計算して結果を返しますが、WIndow関数は、1行ではなく複数行の値に対して計算し、各行に1つの結果を返します。
例えば以下のテーブルがあったとします。
| Name |
|---|
| Red |
| Blue |
| Green |
Nameに対して、文字長を返すLENGTH()関数を使用すると、以下のようになります。
| Name | LENGTH(Name) |
|---|---|
| Red | 3 |
| Blue | 4 |
| Green | 5 |
LENGTH()関数は1行ずつ値を受け取り、1行ずつ結果を返していますね。ではこの名前の長さに対してランク付けするとき、どうしましょう？
Blueが自分が2番目だと知るには、Red,Greenの値を知らないといけません。他の行の値を参照しないとランク付けができないわけです。関数は1行の値しか読み込みしないのでできません。
そしてここで登場するのが、Window関数です。
RANK() OVER(ORDER BY column)
ここからは実際のテーブルにSQLを実行してみます。
例えば、市町村毎の人口ランキングを作りたい場合、SQLでは以下のように記載します。２行目のRANK() OVER(ORDER BY column)の箇所がWindow関数です。
SELECT RANK() OVER(ORDER BY population DESC) AS rank, prefecture, city, FROM truefly.census WHERE prefecture != city AND city != '特別区部' AND city NOT LIKE '%区' ORDER BY rank, prefecture_code
結果（7行のみ表示）
| rank | prefecture | city |
|---|---|---|
| 1 | 神奈川県 | 横浜市 |
| 2 | 大阪府 | 大阪市 |
| 3 | 愛知県 | 名古屋市 |
| 4 | 北海道 | 札幌市 |
| 5 | 福岡県 | 福岡市 |
| 6 | 神奈川県 | 川崎市 |
| 7 | 兵庫県 | 神戸市 |
RANK() OVER(PARTITION BY column ORDER BY column)
都道府県毎の市町村の人口ランキングを作りたい場合、OVER句内にPARTITION BY columnと書くことで、指定したカラム毎に集計をすることができます。
SELECT RANK() OVER(PARTITION BY prefecture ORDER BY population DESC) AS rank, prefecture, city, FROM truefly.census WHERE prefecture != city AND city != '特別区部' AND city NOT LIKE '%区' ORDER BY rank, prefecture_code
結果（7行のみ表示）
| rank | prefecture | city |
|---|---|---|
| 1 | 北海道 | 札幌市 |
| 1 | 青森県 | 青森市 |
| 1 | 岩手県 | 盛岡市 |
| 1 | 宮城県 | 仙台市 |
| 1 | 秋田県 | 秋田市 |
| 1 | 山形県 | 山形市 |
| 1 | 福島県 | いわき市 |
QUALIFYでフィルタリングする
QUALIFYでSELECTしたWindow関数の結果でフィルタリング
本題です。QUALIFYで、SELECTに書いたWindow関数の結果でフィルタリングできるとのことなので試します。
次は都道府県毎の2番目に人口が多い市町村を出してみます。
SELECT RANK() OVER(PARTITION BY prefecture ORDER BY population DESC) AS rank, prefecture, city, FROM truefly.census WHERE prefecture != city AND city != '特別区部' AND city NOT LIKE '%区' QUALIFY -- ここでSELECT句のWindow関数のエイリアス名を指定 rank = 2 ORDER BY prefecture_code
結果（7行のみ表示）
| rank | prefecture | city |
|---|---|---|
| 2 | 北海道 | 旭川市 |
| 2 | 青森県 | 八戸市 |
| 2 | 岩手県 | 奥州市 |
| 2 | 宮城県 | 石巻市 |
| 2 | 秋田県 | 横手市 |
| 2 | 山形県 | 鶴岡市 |
| 2 | 福島県 | 郡山市 |
見事にフィルタリングされました。従来は以下のようにサブクエリ内にWindow関数を書いて、外側でWHEREでフィルタリングしていたので、SQLもだいぶシンプルになりました。
-- QUALIFYを利用しない場合 SELECT rank, prefecture, city, FROM ( SELECT RANK() OVER(PARTITION BY prefecture ORDER BY population DESC) AS rank, prefecture_code, prefecture, city, FROM truefly.census WHERE prefecture != city AND city != '特別区部' AND city NOT LIKE '%区' ) WHERE rank = 2 ORDER BY prefecture_code
QUALIFY句に書いたWindow関数の結果でフィルタリング
WIndow関数はSELECTに書かなくても、直接QUALIFY句内に書けるので、次はその書き方で、都道府県毎の3番目に人口が多い市町村を出してみます。
SELECT prefecture, city, FROM truefly.census WHERE prefecture != city AND city != '特別区部' AND city NOT LIKE '%区' QUALIFY -- ここにWindow関数 RANK() OVER(PARTITION BY prefecture ORDER BY population DESC) = 3 ORDER BY prefecture_code
結果（7行のみ表示）
| prefecture | city |
|---|---|
| 北海道 | 函館市 |
| 青森県 | 弘前市 |
| 岩手県 | 一関市 |
| 宮城県 | 大崎市 |
| 秋田県 | 大仙市 |
| 山形県 | 酒田市 |
| 福島県 | 福島市 |
まとめ
Window関数を使ってのデータの集計が便利なBigQueryに、新しい書き方が仲間入りしてますます便利になりました。
BigQueryはかなり愛用しているので、面白いSQLの書き方だったり、BigQueryの新しい機能を試しては、また記事にまとめようと思います！
