慣れないと使うのを忘れがちなQUALIFY句、実は非常に便利

SQLにおけるQUALIFY句

WINDOW関数は、SQLで一番最初につまづくポイントかもしれません。

OVER PARTITION BY ああ、憂鬱…そんな初学者の方も多いのではないでしょうか。

今回はそんなWINDOW関数の結果に基づいたフィルタをかけるのに便利な QUALIFY 句についてです。

使えるプラットフォームも限られていて、現状では MySQLやPostgreSQLのようなアプリケーション系のDBでは実装されておらず、Redshift, BigQuery, Snowflakeのような分析用のDBでしか使えないものでもあるため意外と知らない人も多い？のかもしれません。

しかし個人的にはかなり便利で抑えておきたい句の一つだと思っています。

QUALIFY句とは

早速QUALIFY句とはなんぞや、というところを説明します。

めぼしいプラットフォームの公式ドキュメントは以下の通りです。

BigQuery: 「Filtering results with the QUALIFY clause」
Redshift: 「QUALIFY句 - Amazon Redshift」
Snowflake: 「QUALIFY - Snowflake Documentation」

QUALIFY句の役割を端的に言うと、WINDOW関数の結果に対してのフィルタリングを行うということです。

WINDOW関数というと、 RANK とか ROW_NUMBER のような、順位系が一番使われる印象ですが

これらを直接 WHERE や HAVING に含めて絞り込みに活用することはできなかったのを解決しているということですね。

よく使うパターン: IDにつき複数のレコードがあるテーブルから最新のtimestampのレコードだけを拾う

ここからは実際にどういうときにどうやって使うのか、の話です。

よくあるのが テーブルにIDごとに複数のレコードがあって、そこからIDごとに最新のレコードを拾いたい というシチュエーションです。

これは本当によくあるパターンで、とりあえずこれだけ覚えて帰って！という感じです。

たとえば、 Google Analytics 4 の BigQuery エクスポートデータ などでも使える話ではないでしょうか。

例

ここからは実際のサンプル用データセットを使ってみていきましょう。

たとえば、user_activity というテーブル名で以下のようなシンプルなデータセットがあるとします。

id activity activity_timestamp の3カラムからなるとてもシンプルで美しいデータセットですね。

ここから、IDごとに最後のアクティビティを抽出するクエリを書く必要があるとき、どうなるでしょうか？

😞 QUALIFY句がないとき

まず、「ないとき」です。

WITH ranked_activity AS (
  SELECT
    id,
    activity,
    activity_timestamp,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY activity_timestamp DESC) AS row_num
  FROM
    user_activity
)
SELECT
  id,
  activity,
  activity_timestamp
FROM
  ranked_activity
WHERE
  row_num = 1;

このようにすれば良いかと思いますが、WITH句登場により可読性も下がってしまい、暗い雰囲気が漂いますよね。

WITHをなくすとしても、サブクエリを使ったりとかになるかと思います。

実際、こういったクエリは目にする機会が非常に多いです。

😄 QUALIFY句があるとき

しかし、QUALIFY句が「あるとき」は違います！

SELECT
  id,
  activity,
  activity_timestamp
FROM
  user_activity
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY activity_timestamp DESC) AS row_num = 1

このように、WINDOW関数自体をそのままQUALIFY句に入れてしまうことができます！

WITH句がなくなって、クエリの可読性も上がり、思わず笑みがこぼれますね。

もうないときには戻れません。リピ確定です。

こんな感じで、問題無くIDごとに最新のレコードを拾えていることが確認出来ます。

ちなみに：実行順序について

実行順序はSQLにおいて重要な要素の一つだと思いますが

From
Where
Group by
Having
Window
QUALIFY
Distinct
Order by
Limit

の順となります。Windowの直下に入るということですね。

まとめ

今回はTipsとして、QUALIFY句の使い方について書きました。

QUALIFY句は使えるプラットフォームも限定されていますし、意外と知らなかったり使っていなかったりする方も多いかと思うのですが、便利なので使えるときはぜひ使っていきたいですね。

ただ、逆に言うとクエリの汎用性は下がってしまうので、ユースケースに応じて使いどころを考えるようにはする必要があります。

とはいえ、分析系のプラットフォームでは大概実装されてきているので、分析用途であれば大丈夫かと思います。

可読性を上げ、誰かのストレスを緩和する一助となれば幸いです！