BigQueryのQUALIFY句を使ってみる
はじめに
データアナリティクス事業本部のkobayashiです。
BigQueryのリリースノートをチェックしていたところ QUALIFY clauseがGAされていたので早速試してみました。
Qualify句とは
クエリで分析関数を使用した場合に分析関数の結果でフィルタリングを行えます。
機能を試してみる
早速Qualify句を試してみたいと思います。使うデータは以下のような気象データを扱ってみます。
| date | month | city | w_type | temperature | precipitation | sunlight | cloudage |
|---|---|---|---|---|---|---|---|
| 2021-12-13 | 12 | 名古屋 | 晴 | 10.8 | 0 | 9.7 | 3 |
| 2021-11-12 | 11 | 仙台 | 晴 | 11.3 | 0 | 8.1 | 1.8 |
| 2021-11-13 | 11 | 大阪 | 晴 | 12.1 | 0 | 8.8 | 5 |
| 2021-11-11 | 11 | 札幌 | 晴 | 7.8 | 0 | 1.7 | 7.5 |
| 2021-11-13 | 11 | 長野 | 晴 | 7.3 | 0 | 9.3 | NULL |
| 2021-11-13 | 11 | 京都 | 晴 | 10.5 | 0 | 7.6 | NULL |
| 2021-11-17 | 11 | 東京 | 晴 | 13.1 | 0 | 9.4 | 0.5 |
| 2021-10-13 | 10 | 横浜 | 晴 | 13.6 | 0 | 10.1 | NULL |
| 2021-03-03 | 3 | 京都 | 晴 | 5.9 | 0 | 6.6 | NULL |
| 2021-03-03 | 3 | 那覇 | 晴 | 18.4 | 0 | 8.5 | 4.3 |
| 2021-02-03 | 2 | 大阪 | 晴 | 6.8 | 0 | 8.2 | 5 |
| 2021-03-03 | 3 | 名古屋 | 晴 | 7.3 | 0 | 10.7 | 1 |
| 2021-01-01 | 1 | 横浜 | 晴 | 7.7 | 0 | 11 | NULL |
| 2021-04-12 | 4 | 仙台 | 晴 | 3.1 | 0 | 9.7 | 4.3 |
このデータから各都市の月別の最低気温のレコードを抽出してみます。これをQualify句を使わないパターンと使ったパターンで記述してみます。
基本となるクエリはwindow関数で都市・月カラムのグループ内で気温の昇順にソートしたものにランク付けしています。
SELECT w.*,
RANK() OVER (PARTITION BY w.city,month ORDER BY w.temperature ) AS temp_rn
FROM data_set_test.jp_weather w
クエリ結果
| date | month | city | w_type | temperature | precipitation | sunlight | cloudage | temp_rn |
|---|---|---|---|---|---|---|---|---|
| 2022-01-14 | 1 | 京都 | 雨 | 1.5 | 4.5 | 3.8 | NULL | 1 |
| 2022-01-21 | 1 | 京都 | 雪 | 1.5 | 7.5 | 2.1 | NULL | 1 |
| 2022-01-20 | 1 | 京都 | 雨 | 2.1 | 3.5 | 6.8 | NULL | 3 |
| 2022-01-18 | 1 | 京都 | 晴 | 2.5 | 0 | 4.6 | NULL | 4 |
Qualify句を使わないパターン
はじめにQualify句を使わないパターンで記述してみます。
window関数を使って各都市の月別の気温をランク付けしましたのでtemp_rn=1でフィルタリングする必要があります。
ただ、where句には分析関数を条件に取ることができないためwith句を使って以下の様に記述する必要があります。
with w as (
select w.*,
RANK() over (partition by w.city,month order by w.temperature ) as temp_rn
from data_set_test.jp_weather w
)
select *
from w
where temp_rn = 1
クエリ結果
| date | month | city | w_type | temperature | precipitation | sunlight | cloudage | temp_rn |
|---|---|---|---|---|---|---|---|---|
| 2021-03-07 | 3 | 仙台 | 晴 | 2.5 | 0 | 10.7 | 5.3 | 1 |
| 2021-04-09 | 4 | 札幌 | 雪 | 2.5 | 0 | 5.5 | 6 | 1 |
| 2022-01-21 | 1 | 大阪 | 晴 | 3.4 | 0 | 7.3 | 5.3 | 1 |
| 2021-09-06 | 9 | 仙台 | 雨 | 17.8 | 3 | 0 | 10 | 1 |
| 2021-07-05 | 7 | 札幌 | 雨 | 19.1 | 0.5 | 0.5 | 9 | 1 |
Qualify句を使ったパターン
ではQualify句を使って同じデータを抽出してみます。Qualify句を使って各都市の月別の最低気温のレコードを抽出するには単純にその条件をQualify句に追加すれば良いです。今回のクエリですとtemp_rn=1をQualify句に書くだけです。
SELECT w.*,
RANK() OVER (PARTITION BY w.city,month ORDER BY w.temperature ) AS temp_rn
FROM data_set_test.jp_weather w
QUALIFY temp_rn = 1
クエリ結果
| date | month | city | w_type | temperature | precipitation | sunlight | cloudage | temp_rn |
|---|---|---|---|---|---|---|---|---|
| 2021-10-21 | 10 | 京都 | 曇 | 12.8 | 0 | 2.5 | NULL | 1 |
| 2021-10-21 | 10 | 大阪 | 曇 | 14.3 | 0 | 2.7 | 10 | 1 |
| 2021-06-01 | 6 | 東京 | 晴 | 20.1 | 0.5 | 9.5 | 5 | 1 |
| 2021-06-01 | 6 | 仙台 | 晴 | 17.3 | 0 | 7.1 | 9.3 | 1 |
| 2022-02-17 | 2 | 福岡 | 晴 | 1.7 | 0 | 5.9 | 7.8 | 1 |
または、Qualify句に直接分析関数を書くこともできるので以下の様に記述しても良いです。
SELECT w.* FROM data_set_test.jp_weather w QUALIFY RANK() OVER (PARTITION BY w.city,month ORDER BY w.temperature ) = 1
クエリ結果
| date | month | city | w_type | temperature | precipitation | sunlight | cloudage |
|---|---|---|---|---|---|---|---|
| 2022-01-01 | 1 | 仙台 | 晴 | -2 | 1.5 | 7.3 | 7 |
| 2021-08-10 | 8 | 札幌 | 雨 | 16.7 | 52.5 | 0 | 10 |
| 2021-09-25 | 9 | 札幌 | 晴 | 15.5 | 0 | 11 | 2.5 |
| 2021-06-05 | 6 | 福岡 | 曇 | 21 | 0 | 1 | 10 |
| 2021-06-04 | 6 | 福岡 | 雨 | 21 | 38.5 | 0.2 | 10 |
Qualify句を使わないパターンに比べ非常にスッキリ書くことができました。
まとめ
BigQueryの分析関数の結果でフィルタリングを行えるQualify句を使ってみました。Qualify句を使わない場合に比べ非常にクエリがスッキリ書けるので、今後分析関数の結果でフィルタリングを行う場合は使わない手はないと思います。
最後まで読んで頂いてありがとうございました。
