SnowflakeのQUALIFYで実現する、ウィンドウ関数の効率的なフィルタリング

SnowflakeのQUALIFY句(SQL全般でも同様)は、ウィンドウ関数の結果に基づいて行を絞り込むための句です。ウィンドウ関数はwhere句で直接使えないため、ウィンドウ関数の結果でフィルタリングしたいときはqualifyを使います。

ウィンドウ関数の簡単なおさらい

ウィンドウ関数とは、現在の行を基準とした一連の行に対して処理を行う関数です。順位付け、累計、移動平均、パーティション単位のカウントなどの計算でよく使われます。現在の行より上や下の行を参照する必要があるときには、ウィンドウ関数が欠かせません。そしてQUALIFY句は、そのウィンドウ関数の結果を手早く絞り込むためのショートカットなのです。

QUALIFYの構文

qualifyの構文は次のとおりです。

SELECT <column_list>
FROM <data_source>
where <conditions>
[GROUP BY ...]
[HAVING ...]
QUALIFY  <predicate>
ORDER BY

ご覧のとおり、qualifyはSQLクエリの末尾、任意のorder by句の直前に記述します。

以下のデータをもとに、2つのビジネス上の問いに答えてみましょう。

1. 各地域・各日のトップ営業担当者は誰か?

select
	salesperson_id,
	region,
	sales_amount,
	sales_date
from sales
qualify row_number() over (partition by region, sales_date order by sales_amount desc) = 1

Snowflakeはハイライトされた行を返します。

2. 各日のトップ営業担当者は誰か?

select
	salesperson_id,
	region,
	sales_amount,
	sales_date
from sales
qualify row_number() over (partition by sales_date order by sales_amount desc) = 1

Snowflakeはハイライトされた行を返します。

ワンポイント: QUALIFY句ではエイリアスをそのまま再利用できます。ウィンドウ関数を絞り込みに使うだけでなく、結果としてSELECTしたい場合は、ウィンドウ関数につけたエイリアスを再利用できます。

select
	salesperson_id,
	region,
	sales_amount,
	sales_date,
	row_number() over (partition by sales_date order by sales_amount desc) sales_date_rank
from sales
qualify sales_date_rank = 1

qualify句は、dbtのuniqueテストで失敗しているレコードを特定したり、複数カラムの組み合わせで重複を洗い出したりする際の最短ルートです。

たとえば、idというカラムにユニーク性のテストを設定していて、それが失敗したとします。すべての重複レコードを、同じidの行が隣り合うように並べて分析したいケースです。

失敗したテスト対象のレコードをすべて取り出す最も手早い方法はこちらです。

select *
from my_table
qualify count(*) over (partition by id) > 1
order by id

同じ重複レコードの問いを、別の方法で解いてみましょう。

方法1:

-- use CTE with Group By instead
with duplicates as
(
	select id
	group by 1
	having count(*) > 1
)
select *
from
my_table join
duplicates using (id);

方法2:

-- use CTE to create a window function, then filter on the window function
with window_count as
(
	select *, count(*) over (partition by id) as id_count
	from my_table
)
select * exclude (id_count)
from window_count
where id_count > 1;

同じ問題を解く方法は他にもいくつかありますが、qualify句ほど手早くシンプルに書ける方法はありません。

ポイント:qualify句の最大の価値は、ウィンドウ関数で絞り込む際にCTEを書かずに済むことです。ウィンドウ関数はwhere句で直接使えないため、もしqualify句がなければ、絞り込みのたびにCTEを用意する必要が出てきます。

すでに見てきたとおり、qualifyはウィンドウ関数の結果を絞り込むためのもので、where句では使えません。ここからは、SQLの処理順序にもう少し踏み込んでみましょう。

Snowflakeがselect文を処理する順序は次のとおりです。

ここで押さえておきたいのは、from句とwhere句での絞り込みはqualifyでの絞り込みより先に実行されるという点です。さらに、集計とその結果に対する絞り込み(group byとhaving)もqualifyより前に処理されます。