よく使う便利なSQL~QUALIFY~

SQL を書くとき、以下のようなニーズが起きることがあります。

・履歴データのように、同じテーブル内に入っている特定のデータから、最新のものだけを取り出したい ・GROUP BY しつつ、別のグループ分けした集計値を使って絞り込みをしたい

こういう場合は、QUALIFY 句を使うと、節を分けずに一回で値を絞り込むことができます。

以下のような試験に関するデータがあったとします。

ユーザID   ステータス スコア   試験日
user_id state   score   exam_date
1   passed  90  2024-01-01
1   failed  10  2024-01-15
1   pending 60  2024-02-01
2   pending 58  2024-01-01
2   failed  40  2024-01-15
2   passed  74  2024-02-01
3   passed  78  2024-01-01
3   passed  84  2024-01-15
3   failed  49  2024-02-01

以下のようなクエリで絞り込むことができます。 ウインドウ関数を使って、絞り込み条件ごとに独自のグループ化ができるところがポイントです。

SELECT
  *
FROM
  `healthcare-1335.qualify_clause_examples.exam_results`
QUALIFY
  -- Select most recent result for every user
  -- ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY exam_date DESC) = 1

  -- Select all results which has lower score than average
  -- score < (AVG(score) OVER (PARTITION BY NULL))

  -- Select all results which has lower score than average 
  score < (AVG(score) OVER (PARTITION BY NULL))
ORDER BY
  user_id ASC