BigQueryのQUALIFY句をWHEREもGROUP BYもHAVINGもない時に使う方法

今年にBigQueryにQUALIFY句が追加されました。
これを使うことで、今までならサブクエリを使って行う必要のあったRANKなどの分析関数の結果に基づいた絞り込みができるようになります。

しかし、QUALIFYには以下の3つのどれかと併用する必要があるという制約があります。

• WHERE
• GROUP BY
• HAVING

これらのいずれもない時には以下のエラーが発生します。

select start_station_name, bikeid, duration_minutes,
rank() over(partition by start_station_name order by duration_minutes desc) as rank
from `bigquery-public-data.austin_bikeshare.bikeshare_trips`
qualify rank <= 3

Error: googleapi: Error 400: QUALIFY clause must be used in conjunction with WHERE or GROUP BY or HAVING clause at [XX:XX], invalidQuery

答え

ダミーのWHERE句を追加する。
確実にTRUEになるWHERE句を追加し、syntax的にはWHERE句が存在するように見せかけることで、この制約を回避できます。

select start_station_name, bikeid, duration_minutes,
rank() over(partition by start_station_name order by duration_minutes desc) as rank
from `bigquery-public-data.austin_bikeshare.bikeshare_trips`
where 1 = 1 # 追加
qualify rank <= 3

参考：