[bigquery]ウィンドウ関数で作成した項目をフィルタする方法(QUALIFY句)

2024-03-24 2024-03-24

QUALIFY句は、ウィンドウ関数の結果に基づいて行をフィルタリングする時に利用します。

従来のWHERE句だと指定できませんでしたが、QUALIFY句を利用すればウィンドウ関数によって生成された新しい列を指定できるところが大きなポイントです。

以下はコード例

## product単位で平均価格を算出した上で、平均価格が500以上のもののみ抽出するクエリ例

SELECT
  product,
  AVG(price) AS avgPrice
FROM
  dataset.table
GROUP BY
  1
QUALIFY
  avgPrice >= 500

## userId単位でcreatedAtの連番を付与して、2番目の登録情報を取得するクエリ例

SELECT
  userId,
  createdAt,
  ROW_NUMBER() OVER (PARTITION BY userId ORDER BY createdAt) AS rownum
FROM
  dataset.table
QUALIFY
  rownum = 2

qualifyを利用しなければ、with句を使うか、服問い合わせで階層化してからwhere句に条件追加してましたが、その手間がなくなり、コード量も減らせるのがありがたい感じです。