【SQL】2021年リリース！QUALIFY句の使い方

【SQL】2021年リリース！QUALIFY句の使い方

どうも、hosigakiです！

今日は、「QUALIFY句」の使い方を紹介します！

QUALIFYって何？

QUALIFY句とは、ウィンドウ関数の結果で抽出結果を絞り込むことができる関数です！
（ウィンドウ関数について知りたい方は、こちらの記事を参考ください！）

2021年にBigQueryからリリースされた機能で、使いこなすとクエリをすっきり書くことができます！

どんな関数かのイメージを持っていただくために、具体例を元に使い方を見ていきましょう！

QUALIFY句の使い方

今回は、前回の記事「ウィンドウ関数でPARTITION BY を使いこなそう！」で取り上げたのと同じ例を元に説明します。

user_namepurchase_itempurchased_at
太郎干し柿2022-1-1
よしお干し柿2022-1-1
太郎干し柿ジュース2022-1-2
ジム干し柿2022-1-3
よしお柿まんじゅう2022-1-3
太郎柿まんじゅう2022-1-3
太郎干し柿2022-1-4
ジム干し柿2022-1-4
purchasesテーブル

前回は、上記のpurchaseテーブルに対して、以下のようにpurchase_timesカラムをつける例を取り上げました。

user_namepurchase_itempurchased_atpurchase_times
太郎干し柿2022-1-11
よしお干し柿2022-1-11
太郎干し柿ジュース2022-1-22
ジム干し柿2022-1-31
よしお柿まんじゅう2022-1-32
太郎柿まんじゅう2022-1-33
太郎干し柿2022-1-44
ジム干し柿2022-1-42
抽出結果イメージ。
例えば、よしおは一回目に干し柿を購入し、二回目に柿まんじゅうを購入した ということが分かる。

purchase_timesカラムを追加するためのクエリは、以下になります。

SELECT
    *,
    RANK() OVER(PARTITION BY user_name ORDER BY purchased_at) AS purchase_times
FROM
    purchases

本日はここからが本題！

この結果から、「2回目のユーザーの購入レコード」（すなわち purchase_times = 2 のレコード）のみを抽出するためには、どんなクエリを書くことができるでしょうか？

今回は、QUALIFY句を用いないクエリと、用いたクエリ、2通り紹介します！

QUALIFY句を用いないクエリ

/*QUALIFY句を用いない例*/

WITH
p AS (
SELECT
    *,
    RANK() OVER(PARTITION BY user_name ORDER BY purchased_at) AS purchase_times
FROM
    purchases
)

SELECT
    *
FROM
    p
WHERE
    purchase_times = 2

上記クエリでは、WITH句を用いることで、以下のように2回に分けて抽出しています。

  • purchase_timesカラムが追加されたテーブルの抽出
  • 上記の内、purchase_times = 2 のレコードだけの抽出

ちなみに、WITH句ではなくサブクエリを使う場合は、以下のようなクエリになりますが、WITH句を使う時と同様に、2回に分けて抽出しています。

/*QUALIFY句を用いない例*/

SELECT
    *
FROM (
    SELECT
        *,
        RANK() OVER(PARTITION BY user_name ORDER BY purchased_at) AS purchase_times
    FROM
        purchases
    )
WHERE
    purchase_times = 2

QUALIFY句を用いたクエリ

/*QUALIFY句を用いたクエリ*/

SELECT
    *,
    RANK() OVER(PARTITION BY user_name ORDER BY purchased_at) AS purchase_times
FROM
    purchases
QUALIFY
    purchase_times = 2

先ほどと違うのは、WITH句やサブクエリを用いず、QUALIFY句の中で purchase_times = 2 を記載することで、以下二つの処理を同時に行っている点です。

  • purchase_timesカラムが追加されたテーブルの抽出
  • 上記の内、purchase_times = 2 のレコードだけの抽出

先ほどのクエリと比べると、ずいぶんスッキリして見やすい印象ですね！

QUALIFY句を用いる上での注意点

QUALIFY句を使う際は、QUALIFY句もしくはSELECT句の中で、ウィンドウ関数を書く必要があります。

例えば以下のように、ウィンドウ関数を書かずにWHERE句のような使い方をした場合は、エラーになりますので注意しましょう！

SELECT
    *
FROM
    purchases
QUALIFY
    user_name = "よしお"

今回はQUALIFY句について紹介しました！
使いこなすとクエリをスッキリ書けるので、ぜひぜひ積極的に使ってみてください！

ここまで読んでいただき、ありがとうございました！
また次の記事でお会いしましょう(^^)/

-SQL
-, , , ,