BigQueryのqualify句の使い方

qualify句を使うと、今までできなかった「ウィンドウ関数の結果に対する条件指定による絞り込み」ができるようになり、クエリのネストを減らすことができる。

たとえば、各クラスごとにスコアが3位以上の生徒を抽出したい場合のクエリは以下のように改善できる。

従来のクエリ

with
  score_with_rank as (
    select
      student_id
      , class
      , score
      , rank() over (
        partition by class
        order by score desc
      ) as rank
    from
      scores
  )
select
  *
from
  score_with_rank
where
  rank >= 3
;

BigQueryではwhere句の中でウィンドウ関数を含む条件指定ができないため、一旦with句でウィンドウ関数の結果をカラムとして持ってから条件指定している。

qualify句を使用したクエリ

select
  student_id
  , class
  , score
  , rank() over (
    partition by class
    order by score desc
  ) as rank
from
  scores
where
  true
qualify
  rank >= 3
;

一方で、qualify句を使用するとwith句なしでネストせず条件指定を行うことができる。

備考

• qualify句を使用する場合、以下のいずれかがクエリ内で使用されている必要がある。よって、いずれにも該当しない場合は、面倒だが今回のように where true のような指定をすることになる。
  • where
  • group by
  • having
• qualify句で指定する条件は、selectの対象になっている必要はない。たとえば、各クラスの上位1位のみを取得するが、ランク自体はすべて1となり不要なのでselectしない、といった使い方も可能。この場合、ウィンドウ関数はqualify句に直接書くことになる。

References

1. 標準 SQL のクエリ構文