BigQueryでANY_VALUE関数とHAVING_MAX,HAVING_MIN句を使ってみる
はじめに
データアナリティクス事業本部のkobayashiです。
BigQueryで扱える集計関数のANY_VALUE関数にHAVING MAXとHAVING MIN句の追加と同じ挙動の集計関数のMAX_BYとMIN_BYの追加がGAされたので改めてANY_VALUEの関数の使い所とHAVING MAXとHAVING MIN句を使ってみました。
ANY_VALUE関数とは
ANY_VALUE関数は集計関数の一つでGroup byを使った集計処理の際の結果に非集計列を含めることができる集計関数になります。もう少し具体的に説明すると、通常、クエリにはGROUP BY句や集計関数に含まれていない列がある場合に問題が生じることがあります。ANY_VALUE()関数は、このような状況を解決するために使われます。これを使用すると、グループ化の方法に影響を与えずに、グループ化された結果セットに説明的な列を含めることができます。つまり、グループ化された結果セットに、集計やグループ化の対象外の列を含めたい場合に、ANY_VALUE()関数を使うことで解決ができます。
ANY_VALUE関数の例
例えば以下のような都道府県・都市・人口・面積データを持つテーブルがあるとします。
# select pref_name,city_name, population, area from data_set_sample.major_results; | pref\_name | city\_name | population | area | | :--- | :--- | :--- | :--- | | 01\_北海道 | 01103\_札幌市東区 | 265379 | 56.97 | | 01\_北海道 | 01101\_札幌市中央区 | 248680 | 46.42 | | 01\_北海道 | 01109\_札幌市手稲区 | 142625 | 56.77 | | 01\_北海道 | 01107\_札幌市西区 | 217040 | 75.10 | | 01\_北海道 | 01104\_札幌市白石区 | 211835 | 34.47 | | 01\_北海道 | 01106\_札幌市南区 | 135777 | 657.48 | | 01\_北海道 | 01110\_札幌市清田区 | 112355 | 59.87 | | 01\_北海道 | 01108\_札幌市厚別区 | 125083 | 24.38 | | 01\_北海道 | 01102\_札幌市北区 | 289323 | 63.57 | | 01\_北海道 | 01105\_札幌市豊平区 | 225298 | 46.23 | | 04\_宮城県 | 04101\_仙台市青葉区 | 311590 | 302.24 | | 04\_宮城県 | 04105\_仙台市泉区 | 212149 | 146.61 | | 04\_宮城県 | 04104\_仙台市太白区 | 234758 | 228.39 |8.39 | | 11\_埼玉県 | 11102\_さいたま市北区 | 149242 | 16.86 | | 11\_埼玉県 | 11110\_さいたま市岩槻区 | 111815 | 49.17 | | 11\_埼玉県 | 11104\_さいたま市見沼区 | 165049 | 30.69 | | 11\_埼玉県 | 11108\_さいたま市南区 | 191563 | 13.82 | | 11\_埼玉県 | 11106\_さいたま市桜区 | 98661 | 18.64 | | 12\_千葉県 | 12104\_千葉市若葉区 | 146940 | 84.19 | | 12\_千葉県 | 12105\_千葉市緑区 | 129421 | 66.25 | | 12\_千葉県 | 12102\_千葉市花見川区 | 177328 | 34.19 | | 12\_千葉県 | 12106\_千葉市美浜区 | 148944 | 21.20 | | 12\_千葉県 | 12101\_千葉市中央区 | 211736 | ...
都道府県別ごとにグループ化して各都道府県の人口を取得する際には以下のSQLを実行することで取得できます。
# select pref_name, SUM(population) population from data_set_sample.major_results group by pref_name; | pref\_name | population | | :--- | :--- | | 00\_全国 | 126146099 | | 01\_北海道 | 12389797 | | 02\_青森県 | 2475968 | | 03\_岩手県 | 2421068 | | 04\_宮城県 | 5700696 | | 05\_秋田県 | 1919004 | | 06\_山形県 | 2136054 | | 07\_福島県 | 3666304 | | 08\_茨城県 | 5734018 | | 09\_栃木県 | 3866292 | | 10\_群馬県 | 3878220 | | 11\_埼玉県 | 16013555 | | 12\_千葉県 | 13543911 | | 13\_東京都 | 37828464 |
次に上記同様に都道府県ごとにグループ化して各都道府県の人口を求めます。ただし、グループ化項目に都市名を含めないが都道府県ごとにサンプルの都市名を含めた結果を取得したいと思います。これをそのままSQLにして実行すると当然エラーが出ます。
# select pref_name, city_name, SUM(population) population from data_set_sample.major_results group by pref_name [HY000][100032] [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: SELECT list expression references column city_name which is neither grouped nor aggregated at [1:19]
そのような場合にANY_VALUE関数を使うことで解決ができます。
# select pref_name,ANY_VALUE(city_name) population, SUM(population) population from data_set_sample.major_results group by pref_name; | pref\_name | city\_name | population | | :--- | :--- | :--- | | 00\_全国 | 00000\_全国 | 126146099 | | 01\_北海道 | 01235\_石狩市 | 12389797 | | 02\_青森県 | 02201\_青森市 | 2475968 | | 03\_岩手県 | 03215\_奥州市 | 2421068 | | 04\_宮城県 | 04101\_仙台市青葉区 | 5700696 | | 05\_秋田県 | 05202\_能代市 | 1919004 | | 06\_山形県 | 06206\_寒河江市 | 2136054 | | 07\_福島県 | 07548\_葛尾村 | 3666304 | | 08\_茨城県 | 08217\_取手市 | 5734018 | | 09\_栃木県 | 09202\_足利市 | 3866292 |
取得する非集計を制御する
ANY_VALUE関数ではそのまま使うとどのレコードの非集計列かは制御できませんが今回追加されたHAVING MAXとHAVING MIN句を使うことでどのレコードの非集計列をサンプルとして取得するかを制御することができます。
はじめに先程のSQLをHAVING MAX句を使ってみます。処理内容としては都道府県ごとにグループ化して各都道府県の人口を求めますが、表示されるサンプル都市名としては人口が一番多い都市名を取得しています。
# select pref_name,ANY_VALUE(city_name HAVING MAX population), SUM(population) population from data_set_sample.major_results group by pref_name; | pref\_name | f0\_ | population | | :--- | :--- | :--- | | 01\_北海道 | 01204\_旭川市 | 2304256 | | 02\_青森県 | 02201\_青森市 | 965568 | | 03\_岩手県 | 03201\_盛岡市 | 999799 | | 04\_宮城県 | 04202\_石巻市 | 858601 | | 05\_秋田県 | 05201\_秋田市 | 871092 | | 06\_山形県 | 06201\_山形市 | 859465 | | 07\_福島県 | 07204\_いわき市 | 1511007 | | 08\_茨城県 | 08201\_水戸市 | 2608123 | | 09\_栃木県 | 09201\_宇都宮市 | 1699187 | | 10\_群馬県 | 10202\_高崎市 | 1656920 | | 11\_埼玉県 | 11203\_川口市 | 5532157 | | 12\_千葉県 | 12204\_船橋市 | 5112695 | | 13\_東京都 | 13201\_八王子市 | 4234381 |
このようにグループ化項目には都市名を含みませんが、都市名としては都道府県ごとに人口が一番多い都市名を表示することができました。
ANY_VALUE(x HAVING MAX y)のように使いましたが集計関数としてMAX_BY(x, y)があります。これはANY_VALUE(x HAVING MAX y)のエイリアスなので全く同じ結果を得ることができます。
# select pref_name,MAX_BY(city_name, population), SUM(population) population from data_set_sample.major_results group by pref_name; | pref\_name | f0\_ | population | | :--- | :--- | :--- | | 01\_北海道 | 01204\_旭川市 | 2304256 | | 02\_青森県 | 02201\_青森市 | 965568 | | 03\_岩手県 | 03201\_盛岡市 | 999799 | | 04\_宮城県 | 04202\_石巻市 | 858601 | | 05\_秋田県 | 05201\_秋田市 | 871092 | | 06\_山形県 | 06201\_山形市 | 859465 | | 07\_福島県 | 07204\_いわき市 | 1511007 | | 08\_茨城県 | 08201\_水戸市 | 2608123 | | 09\_栃木県 | 09201\_宇都宮市 | 1699187 | | 10\_群馬県 | 10202\_高崎市 | 1656920 | | 11\_埼玉県 | 11203\_川口市 | 5532157 | | 12\_千葉県 | 12204\_船橋市 | 5112695 | | 13\_東京都 | 13201\_八王子市 | 4234381 |
次に先程のSQLをHAVING MIN句を使ってみます。処理内容としては都道府県ごとにグループ化して各都道府県の人口を求めますが、表示されるサンプル都市名としてはが面積が一番小さい都市名を取得しています。
# select pref_name,ANY_VALUE(city_name HAVING MIN area), SUM(population) population from data_set_sample.major_results group by pref_name; | pref\_name | f0\_ | population | | :--- | :--- | :--- | | 01\_北海道 | 01227\_歌志内市 | 2304256 | | 02\_青森県 | 02207\_三沢市 | 965568 | | 03\_岩手県 | 03216\_滝沢市 | 999799 | | 04\_宮城県 | 04203\_塩竈市 | 858601 | | 05\_秋田県 | 05211\_潟上市 | 871092 | | 06\_山形県 | 06210\_天童市 | 859465 | | 07\_福島県 | 07214\_本宮市 | 1511007 | | 08\_茨城県 | 08224\_守谷市 | 2608123 | | 09\_栃木県 | 09216\_下野市 | 1699187 | | 10\_群馬県 | 10207\_館林市 | 1656920 | | 11\_埼玉県 | 11223\_蕨市 | 5532157 | | 12\_千葉県 | 12227\_浦安市 | 5112695 | | 13\_東京都 | 13219\_狛江市 | 4234381 |
ANY_VALUE(x HAVING MIN y)のように使いましたが集計関数としてMIN_BY(x, y)があります。これもMAX_BYと同じくANY_VALUE(x HAVING MIN y)のエイリアスなので全く同じ結果を得ることができます。
# select pref_name,MIN_BY(city_name, area), SUM(population) population from data_set_sample.major_results group by pref_name; | pref\_name | f0\_ | population | | :--- | :--- | :--- | | 01\_北海道 | 01227\_歌志内市 | 2304256 | | 02\_青森県 | 02207\_三沢市 | 965568 | | 03\_岩手県 | 03216\_滝沢市 | 999799 | | 04\_宮城県 | 04203\_塩竈市 | 858601 | | 05\_秋田県 | 05211\_潟上市 | 871092 | | 06\_山形県 | 06210\_天童市 | 859465 | | 07\_福島県 | 07214\_本宮市 | 1511007 | | 08\_茨城県 | 08224\_守谷市 | 2608123 | | 09\_栃木県 | 09216\_下野市 | 1699187 | | 10\_群馬県 | 10207\_館林市 | 1656920 | | 11\_埼玉県 | 11223\_蕨市 | 5532157 | | 12\_千葉県 | 12227\_浦安市 | 5112695 | | 13\_東京都 | 13219\_狛江市 | 4234381 |
まとめ
ANY_VALUEの関数の使い所とHAVING MAXとHAVING MIN句を使ってみました。グループ化したい項目には含まないが取得するデータには非集計項目として含ませたい場合に使える集計関数です。
最後まで読んで頂いてありがとうございました。
