BigQueryのQUALIFY句を使ってみる

はじめに

データアナリティクス事業本部のkobayashiです。

BigQueryのリリースノートをチェックしていたところ QUALIFY clauseがGAされていたので早速試してみました。

• Release notes  |  BigQuery  |  Google Cloud
• Query syntax QUALIFY clause  |  BigQuery  |  Google Cloud

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句を使わない場合に比べ非常にクエリがスッキリ書けるので、今後分析関数の結果でフィルタリングを行う場合は使わない手はないと思います。

最後まで読んで頂いてありがとうございました。