とりゅふの森

GCPデータエンジニアとして生きる

【BigQuery】QUALIFYフィルタリングを試してみた

f:id:true-fly:20210728223614p:plain

こんにちは、BigQueryで好きな関数、RANK() OVER(PARTITION BY A ORDER BY B)のとりゅふです。

BigQueryでデータ分析といえば、やっぱりWindow関数ですよね。
このWindow関数、今まではSELECT句にしか書けなかったのですが、2021年5月10日にプレビューリリースされたQUALIFY句によって、なんと条件として指定することができるようになったのです!

cloud.google.com

SUM()などの集計関数の結果をHAVING区でフィルタリングするときと同じような使い方ができます。早速SQLを書いて検証していこうと思います!

今回使うデータセット、テーブル

本記事に記載のSQLで参照しているデータセット、テーブルは、私の個人プロジェクトにある以下のものを利用しています。

  • データセット:truefly
  • テーブル:truefly.census

f:id:true-fly:20210723174126p:plain
※「令和2年国勢調査 人口速報集計」(総務省統計局) を加工して作成しています。

Window関数のおさらい

知っている!って方は次の「QUALIFYでフィルタリングする」までジャンプ!

Window関数ってなんやねん

通常の関数は1行ごとに値を計算して結果を返しますが、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では以下のように記載します。2行目の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の新しい機能を試しては、また記事にまとめようと思います!