【BigQuery】どこよりもやさしい｜QUALIFY句の使い方

はじめに

今回は、BigQueryのQUALIFY句について紹介します。
QUALIFY句の何が良いかというと、WINDOW関数の結果に対して絞り込みをかけることができるので、クエリの記述を短くすることができます。

QUALIFY句はもともとBigQueryが2021年にリリースしていますが、現在ではAzureやSnowflakeなど、その他のSQLエンジンでも使えるようです。
この解説の中ではBigQueryのコンソール画面を使用して解説していきます。

QUALIFY句の使用例

例えば下記のようなデータがあった場合、qty(個数)の合計が6になるname（名前）のレコードだけを集約せずに抽出するにはどうしたらいいでしょうか。

WINDOW関数を使った下記の書き方が想像つきます。

SELECT
 name
 ,qty
 ,sum_qty
FROM
(
 SELECT
  name
  ,qty
--name単位でqtyを合計
  ,SUM(qty) OVER(PARTITION BY name) AS sum_qty
 FROM sample_data
)
WHERE sum_qty=6

しかしQUALIFY句の特徴として、HAVING句のように集計関数の使用後にフィルタをかけるのではなく、WINDOW関数で返す結果に対して絞り込みをかけることができます。
従って、下記のような書き方となります。

SELECT
 name
 ,qty
--name単位でqtyを合計
 ,SUM(qty) OVER(PARTITION BY name) AS sum_qty
FROM sample_data
QUALIFY sum_qty = 6

サブクエリとして入れ子構造にしてから結果を絞っていたところ、WINDOW関数とQUALIFY句を併用することで、これだけスマートな書き方をできるようになりました。

ちなみに実行順序としては下記の順番になります。期待した結果が返ってこなかった場合は下記も参考にしてみてください。

まとめ

今回はQUALIFY句を使って、クエリの記述を省略する方法を紹介しました。私自身も使う場面が結構あるため、今後SQLを扱う方はこの機会に使えるようになっておくとよいでしょう。

それではここまでお読みいただきありがとうございました！

この記事を書いた人

松原 健太
（まつばら けんた）

株式会社UNCOVER TRUTH
ビジネスデベロップメントグループ DX-Acceleratorチーム
2022年、UNCOVER TRUTHに中途入社。
大手GMS・外資系SPA・大手百貨店といった小売業界を経験後、一念発起してIT業界に転身。
広告代理店・マーケティング支援会社などのデータ業務を支援。よく使うツールはBigQueryとLooker Studio、ときどきTableau。