おはようございます。本日のBigQuery演習のテーマはこちら！
SQLで配列をフラットに加工する
BigQueryでは以下のように、配列でデータを持つことができます。データを管理する側としては便利ですが、集計する側からしてみれば使いにくい状態かなと思います。
この配列を変換する以下の問題①②にチャレンジしましょう！配列の基本的な使い方があればとっても簡単に変換できます。
①配列を縦にフラット化する
②配列をカンマ区切りで1カラムにまとめる
ルール
- ①、②ともに1つのSQLで実現すること
- 参照するテーブルはナシ
- 配列データは、以下のSQLテンプレートのWITH句にある都道府県データを用いること
WITH tmp AS ( SELECT '北海道地方' AS division,['北海道'] AS prefectures UNION ALL SELECT '東北地方' AS division, ['青森県','岩手県','宮城県','秋田県','山形県','福島県'] AS prefectures UNION ALL SELECT '関東地方' AS division, ['茨城県','栃木県','群馬県','埼玉県','千葉県','東京都','神奈川県'] AS prefectures UNION ALL SELECT '中部地方' AS division, ['新潟県','富山県','石川県','福井県','山梨県','長野県','岐阜県','静岡県','愛知県'] AS prefectures UNION ALL SELECT '近畿地方' AS division, ['三重県','滋賀県','京都府','大阪府','兵庫県','奈良県','和歌山県'] AS prefectures UNION ALL SELECT '中国地方' AS division, ['鳥取県','島根県','岡山県','広島県','山口県'] AS prefectures UNION ALL SELECT '四国地方' AS division, ['徳島県','香川県','愛媛県','高知県'] AS prefectures UNION ALL SELECT '九州地方' AS division, ['福岡県','佐賀県','長崎県','熊本県','大分県','宮崎県','鹿児島県','沖縄県'] AS prefectures ) SELECT FROM tmp
①配列を縦にフラット化する
答え
WITH tmp AS ( SELECT '北海道地方' AS division,['北海道'] AS prefectures UNION ALL SELECT '東北地方' AS division, ['青森県','岩手県','宮城県','秋田県','山形県','福島県'] AS prefectures UNION ALL SELECT '関東地方' AS division, ['茨城県','栃木県','群馬県','埼玉県','千葉県','東京都','神奈川県'] AS prefectures UNION ALL SELECT '中部地方' AS division, ['新潟県','富山県','石川県','福井県','山梨県','長野県','岐阜県','静岡県','愛知県'] AS prefectures UNION ALL SELECT '近畿地方' AS division, ['三重県','滋賀県','京都府','大阪府','兵庫県','奈良県','和歌山県'] AS prefectures UNION ALL SELECT '中国地方' AS division, ['鳥取県','島根県','岡山県','広島県','山口県'] AS prefectures UNION ALL SELECT '四国地方' AS division, ['徳島県','香川県','愛媛県','高知県'] AS prefectures UNION ALL SELECT '九州地方' AS division, ['福岡県','佐賀県','長崎県','熊本県','大分県','宮崎県','鹿児島県','沖縄県'] AS prefectures ) SELECT division, prefecture FROM tmp, UNNEST(tmp.prefectures) prefecture
解説
配列を縦にフラットにするには、UNNEST(配列の列名)を用います。UNNESTは関数ではなく予約語で、FROM句に書きます。
FROM 配列を含むテーブル, UNNEST(配列の列名)のように、配列を含むテーブルの後ろにカンマで続けて書くことで、配列を含むテーブルをフラット化することができます。
FROM句内で用いられるカンマは、CROSS JOINと同義です。UNNESTした項目は、エイリアス名をつけてSELECT句で参照できます。
②配列をカンマ区切りで1カラムにまとめる
答え
WITH tmp AS ( SELECT '北海道地方' AS division,['北海道'] AS prefectures UNION ALL SELECT '東北地方' AS division, ['青森県','岩手県','宮城県','秋田県','山形県','福島県'] AS prefectures UNION ALL SELECT '関東地方' AS division, ['茨城県','栃木県','群馬県','埼玉県','千葉県','東京都','神奈川県'] AS prefectures UNION ALL SELECT '中部地方' AS division, ['新潟県','富山県','石川県','福井県','山梨県','長野県','岐阜県','静岡県','愛知県'] AS prefectures UNION ALL SELECT '近畿地方' AS division, ['三重県','滋賀県','京都府','大阪府','兵庫県','奈良県','和歌山県'] AS prefectures UNION ALL SELECT '中国地方' AS division, ['鳥取県','島根県','岡山県','広島県','山口県'] AS prefectures UNION ALL SELECT '四国地方' AS division, ['徳島県','香川県','愛媛県','高知県'] AS prefectures UNION ALL SELECT '九州地方' AS division, ['福岡県','佐賀県','長崎県','熊本県','大分県','宮崎県','鹿児島県','沖縄県'] AS prefectures ) SELECT division, ARRAY_TO_STRING(prefectures, ',') AS prefectures FROM tmp
解説
こちらはシンプルにARRAY_TO_STRING(配列の項目[, 区切り文字])関数を用いただけです。
今回は配列関数1つのみを紹介しましたが、公式ドキュメントには他にもいくつかの配列関数が掲載されているので、配列データを用いた集計をする際は、公式ドキュメントを読み解きつつ、SQLを書いていきましょう！
標準 SQL の配列関数 | BigQuery | Google Cloud
分析用SQLを学ぶには、演習形式で学べる以下の書籍がおすすめです！
