BigQuery : qualify句を簡単な具体例で理解する

qualify句の目的

ネストを減らしてクエリをシンプルなもの

なぜシンプルにできるか

ウィンドウ関数を使った結果に対して、条件を指定することで絞り込みができるから

テストデータ

以下のような試験データが入ったテーブルがあったとする
カラムには（試験を受けた）日、（試験を受けた人の）名前、点数がある

day	name	score
2024/01/01	A	80
2024/01/01	B	75
2024/01/01	C	50
2024/01/01	D	89
2024/02/01	A	70
2024/02/01	B	30
2024/02/01	C	40
2024/03/01	D	70
2024/03/01	A	66
2024/03/01	B	93
2024/03/01	C	88
2024/03/01	D	58

テストデータから何をするか

このデータから日別の最高得点者のレコードを抽出する

Qualify句を使わないパターン

with score_rank as(
select
  *,
  rank() over(partition by day order by score desc) as high_score_ranking
from
  Test
)
select *
from score_rank
where high_score_ranking = 1
;

score_rankというwith句の中で、日毎のランキングを作成した
score_rankの時点では以下の状態

day	name	score	high_score_ranking
2024/01/01	D	89	1
2024/01/01	A	80	2
2024/01/01	B	75	3
2024/01/01	C	50	4
2024/02/01	A	70	1
2024/02/01	C	40	2
2024/02/01	B	30	3
2024/03/01	B	93	1
2024/03/01	C	88	2
2024/03/01	D	70	3
2024/03/01	A	66	4
2024/03/01	D	58	5

そこからhigh_score_rankingが1のレコードのみ抽出した
最終的なクエリ結果はこちら

day	name	score	high_score_ranking
2024/01/01	D	89	1
2024/02/01	A	70	1
2024/03/01	B	93	1

Qualify句を使ったパターン

select
  *,
  rank() over(partition by day order by score desc) as high_score_ranking
from
  Test
  qualify high_score_ranking = 1
;

score_rankというwith句を作らなくても、日毎の最高得点者のみを表示できた

まとめ

ウィンドウ関数を使った結果に対して、条件を指定することで絞り込みができるqualify句は、
ネストを減らしてクエリをシンプルなものとする