SnowflakeのQUALIFY句を試してみる

日本ビジネスシステムズでデータエンジニアをしてる土井です。

データエンジニアとしてデータマート作成等を行う際に、SQLでデータ変換処理を実装することがあります。上記処理の実装に際して、ウィンドウ関数は非常に便利でよく利用されるオプションの一つかと思います。

今回はそんなウィンドウ関数を用いたクエリの可読性向上に使える、SnowflakeのQUALIFY句を検証してみました。

本記事は2024年4月26日時点の以下の公式ドキュメントの記載に基づいて執筆しております。

docs.snowflake.com

QUALIFY句とは？
QUALIFY句を試してみる
まとめ

QUALIFY句とは？

公式ドキュメントでは以下のように説明されています。

SELECT ステートメントでは、 QUALIFY 句はウィンドウ関数の結果をフィルタリングします。
QUALIFY はウィンドウ関数を使用して、 HAVING が集計関数およびGROUP BY 句を使用して実行することを実行します。

QUALIFY句を使うことで、ウィンドウ関数を使ったクエリの結果セットに対してフィルタを適用することができるそうです。

（あれ…？それってWHERE句じゃダメなんだっけ…？）

実は、WHERE句では同じネストレベルにあるウィンドウ関数の結果をフィルタすることはできません。

これはSQLステートメントの実行順序に起因するもので、SELECT句内のウィンドウ関数はWHERE句の後に実行されるため、同じネストレベルでフィルタしようとするとエラーになってしまいます。

-- Error
select
    rank() over(order by points desc, goal_diff desc) as position,
    club
from
    pl_table_0426
where
    position <= 4
;

ステートメントの実行順序の詳細についてもドキュメントに記載があるのでご参照ください。

From

Where

Group by

Having

Window

QUALIFY

Distinct

Order by

Limit

この仕様を踏まえ、ウィンドウ関数にフィルタを適用する方法を見ていきましょう。

QUALIFY句を試してみる

データ準備

テストデータには日本時間4/26 15:00時点でのサッカーのイングリッシュプレミアリーグの順位表(Top 10)を使います。

create or replace table pl_table_0426
(
    club        varchar NOT NULL,
    points      number  NOT NULL,
    goal_diff   number  NOT NULL
)
as
select
    club,
    points,
    goal_diff
from (
    values  ('Newcastle United',50,15),
            ('West Ham United',48,-9),
            ('Chelsea',47,4),
            ('Aston Villa',66,21),
            ('Bournemouth',45,-11),
            ('Arsenal',77,56),
            ('Manchester United',53,1),
            ('Liverpool',74,41),
            ('Manchester City',76,48),
            ('Tottenham Hotspur',60,16) as pl_table_0426 (club,points,goal_diff)
);

完成したテーブル

CLUB	POINTS	GOAL_DIFF
Newcastle United	50	15
West Ham United	48	-9
Chelsea	47	4
Aston Villa	66	21
Bournemouth	45	-11
Arsenal	77	56
Manchester United	53	1
Liverpool	74	41
Manchester City	76	48
Tottenham Hotspur	60	16

（余談）筆者はニューカッスルのサポーターなんですが、本記事を日を跨いで執筆してたらミッドウィークの試合でチームが負けてしまい、悲しい気持ちでデータを修正しました。

このデータに対して勝ち点(points)と得失点差(goal_diff)を使用して順位付けしたうえで、上位4位までをフィルタして表示するシナリオを考えてみることにします。

ではやっていきましょう。

QUALIFY句を使わない場合

まずはQUALIFY句を使わずに実装してみます。

QUALIFY句を使わずにウィンドウ関数の結果セットをフィルタする場合は、サブクエリまたはCTEを使う必要があります。

-- Subquery
select
    *
from (
    select
        rank() over(order by points desc, goal_diff desc) AS position,
        club
    from
        pl_table_0426
) ranked
where
    position <= 4
;

-- CTE
with ranked
as (
    select
        rank() over(order by points desc, goal_diff desc) as position,
        club
    from
        pl_table_0426
)
select
    *
from
    ranked
where
    position <= 4
;

実行結果（サブクエリ）

QUALIFY句を使った場合

QUALIFY句の構文は以下の通りになります。順番的にはHAVING句の下、ORDER BY句の上に書きます。

SELECT <column_list>
FROM <data_source>
  [GROUPBY ...]
  [HAVING ...]
  QUALIFY <predicate>
  [ ... ]

では試してみましょう。

-- QUALIFY
select
    rank() over(order by points desc, goal_diff desc) as position,
    club
from
    pl_table_0426
qualify
    position <= 4
;

実行結果

サブクエリやCTEを使用したものと比較して、ネストがなくなってスッキリしました。

なお、プロファイルはどのパターンで実行した時も以下の画像と同じだったことから、少なからず今回のような少量のデータかつシンプルな処理においては、実行計画やパフォーマンスに明確な差はないようです。

まとめ

SnowflakeのQUALIFY句を試してみました。

本記事程度の行数であればさほど気になりませんが、行数が多いクエリでネストが深くなると読解に認知負荷がかかります。QUALIFY句を有効活用することでウィンドウ関数を含むクエリのネストを排除し、可読性を向上させることができます。

今後もSnowflakeを始めデータ系のサービス／プロダクトに関する検証を記事にしていきたいと思います。