はじめに
はじめまして、たかひらめぐみと申します。
実はQiitaもアドカレも人生初投稿です。お手柔らかにお願いします。
こんな私ですが公私共にBigQuery様には大変お世話になっております。
ちゃちゃっと取り込んでクエリで処理できるというメリットを活かし、なんかしたいデータはなんでも、たいしてBigじゃないデータでもとりあえず条件反射的にBigQueryにつっこませて頂いてます。
今日はそんな感じで、もらったデータをとりあえず眺めてみるための小技をご紹介します。
皆さんは、目的の異なるカテゴリを複数種保持している商品等のマスタに対し、ぱぱッとマトリクスで分布を見たい、それも計数ではなくてどの商品が属しているのか商品名を見たいんだよぉー、って事ありませんかね？私はあるんです。
ちょっと何を言っているのかよくわからないと思うので、クエリで語らせて下さい。
※この記事は、基本的なBigQueryの使用方法とSQLの構文については学習済のユーザを対象とさせて頂いています。
本日のデータ
今回使うデータはこんな感じの恐竜データになります。
データセットを公開しておりますので、ご自由にご利用ください。
SELECT * FROM `gcpugjoshi.demo.dinosaur`
| 行 | binomen | category | period |
|---|---|---|---|
| 1 | エオラプトル・ルネンシス | 脊椎動物門 恐竜竜盤目 獣脚亜目 | 中生代三畳紀 |
| 2 | ロトサウルス・アデンタス | 脊椎動物門 爬虫綱 双弓亜綱 主竜類 ラウイスクス科 | 中生代三畳紀 |
| 3 | シャンシスクス・シャンシスクス | 脊椎動物門 爬虫綱 双弓亜綱 主竜類 エリトロスクス科 | 中生代三畳紀 |
| 4 | テリジノサウルス | 脊椎動物門 恐竜竜盤目 獣脚亜目 | 中生代白亜紀 |
| 5 | アーケオケラトプス・オオシマイ | 脊椎動物門 恐竜鳥盤目 周飾頭亜目 角竜下目 | 中生代白亜紀 |
| ・・・ |
私が独断と偏見で集めたデータですので、偏りがある事を予めご了承ください。
みて頂くとわかるとおり、学名(binomen)・カテゴリ(category)・生息していた時代(period)が格納されています。
これを、カテゴリ×生息していた時代 でマトリクスにして、恐竜の名前を羅列しようというのが今回の主旨になります。
また、カテゴリはレコードにより最大5階層で格納されているので、分解してみていこうと思います。
SPLIT関数を使ってスペース区切りのカテゴリを分解する
ではまずカテゴリを分解してカウントをとり、ざっくり分布をみてみます。
統一された区切り文字を内包している文字列を分解するにはSPLIT関数が便利です。
今回はカテゴリに対してこのSPLIT関数をを使用し、区切り文字に半角スペース' 'を指定して配列にしたものを、オフセットを指定して取り出します。
SELECT
SPLIT(category,' ')[OFFSET (0)] AS category1,
SPLIT(category,' ')[OFFSET (1)] AS category2,
SPLIT(category,' ')[OFFSET (2)] AS category3,
SPLIT(category,' ')[OFFSET (3)] AS category4,
COUNT(*)
FROM
`gcpugjoshi.demo.dinosaur`
GROUP BY
1, 2, 3, 4
ORDER BY
5 DESC
怒られてしまいました。ぴえん。
カテゴリのOFFSET (3)は、レコードによっては存在していない事が原因なので、SAFE_OFFSETで要素が存在する場合のみ取得することにします。
SELECT
SPLIT(category,' ')[OFFSET (0)] AS category1,
SPLIT(category,' ')[OFFSET (1)] AS category2,
SPLIT(category,' ')[OFFSET (2)] AS category3,
SPLIT(category,' ')[SAFE_OFFSET (3)] AS category4,
COUNT(*)
FROM
`gcpugjoshi.demo.dinosaur`
GROUP BY
1, 2, 3, 4
ORDER BY
5 DESC
| 行 | category1 | category2 | category3 | category4 | f0_ |
|---|---|---|---|---|---|
| 1 | 脊椎動物門 | 恐竜鳥盤目 | 鳥脚亜目 | ハドロサウルス科 | 8 |
| 2 | 脊椎動物門 | 恐竜鳥盤目 | 鳥脚亜目 | イグアノドン類 | 7 |
| 3 | 脊椎動物門 | 恐竜鳥盤目 | 周飾頭亜目 | 角竜下目 | 5 |
| 4 | 脊椎動物門 | 恐竜竜盤目 | 装盾亜目 | ヨロイ竜下目 | 3 |
| 5 | 脊椎動物門 | 恐竜竜盤目 | 獣脚亜目 | アロサウルス上科 | 3 |
| ・・・ |
今度はうまくいきました。
せっかくがんばって出力したcategory4ですが、ちょっと分類が細かすぎるので、今回はcategory3まででマトリクスを作ってみようと思います。
STRING_AGG関数を使って恐竜の名前を羅列する
次はSTRING_AGGを使っていきます。
この関数はSUMやCOUNTのようにGROUP BYと組み合わせて使用でき、GROUP BYで指定した項目を軸に、文字列を結合して出力する事ができます。
今回は、改行コード\nを指定して、カテゴリ毎にひたすら恐竜の名前を羅列したいと思います。
SELECT
SPLIT(category,' ')[OFFSET (0)] AS category1,
SPLIT(category,' ')[OFFSET (1)] AS category2,
SPLIT(category,' ')[OFFSET (2)] AS category3,
STRING_AGG(DISTINCT binomen ,'\n') AS binomen
FROM
`gcpugjoshi.demo.dinosaur`
GROUP BY
1, 2, 3
ORDER BY
1, 2, 3
| 行 | category1 | category2 | category3 | binomen |
|---|---|---|---|---|
| 1 | 脊椎動物門 | 恐竜竜盤目 | 獣脚亜目 | アロサウルス・フラギリス アーケオルニトミムス エオラプトル・ルネンシス オビラプトル オルニトミムス・エドモントニクス ケラトサウルス・ナシコルニス コンコラプトル・グラシリス シノサウルス・トライアシクス シンラプトル・ドンギ タルボサウルス・バタール ティラノサウルス・レックス テリジノサウルス デイノニクス・アンティルロプス フクイラプトル・キタダニエンシス ベロキラプトル モノロフォサウルス・ジャンギ |
| 2 | 脊椎動物門 | 恐竜竜盤目 | 竜脚形亜目 | クンミンゴサウルス・ウディンゲンシス ベルサウルス・スイ マメンチサウルス・ホチュアネンシス ルーフェンゴサウルス・フェネイ |
| 3 | 脊椎動物門 | 恐竜竜盤目 | 装盾亜目 | クライトンサウルス・ボーリニ ピナコサウルス・グランゲリ ユーオプロケファルス・ツツス |
| ・・・ |
CASEを使って生息した時代を横軸に展開する
最後に、SQL黒魔術界隈では有名？な、CASEを使った行列変換術を使って、生息した時代を横軸に展開しマトリクスに仕上げていきます。
とその前に、生息時代の分布を見ておきます。
SELECT
period,
COUNT(*)
FROM
`gcpugjoshi.demo.dinosaur`
GROUP BY
1
ORDER BY
2 DESC
| 行 | period | f0_ |
|---|---|---|
| 1 | 中生代白亜紀 | 36 |
| 2 | 中生代ジュラ紀 | 13 |
| 3 | 中生代三畳紀 | 3 |
| 4 | 古生代ペルム紀 | 1 |
データ数に深い意味はないのですが、このテーブルは新しい時代ほどデータ数を多く持っています。
(古生代ペルム紀に恐竜はいないですよね、ごめんなさい。ちょっとだけ爬虫綱 盤竜目を忍び込ませております。)
横軸に展開したい項目がわかったところで、愚直にCASEを量産していきます。
SELECT
SPLIT(category,' ')[OFFSET (0)] AS category1,
SPLIT(category,' ')[OFFSET (1)] AS category2,
SPLIT(category,' ')[OFFSET (2)] AS category3,
STRING_AGG(DISTINCT CASE WHEN period = '古生代ペルム紀' THEN binomen ELSE NULL END ,'\n') AS Permian,
STRING_AGG(DISTINCT CASE WHEN period = '中生代三畳紀' THEN binomen ELSE NULL END ,'\n' ) AS Triassic,
STRING_AGG(DISTINCT CASE WHEN period = '中生代ジュラ紀' THEN binomen ELSE NULL END ,'\n') AS Jurassic,
STRING_AGG(DISTINCT CASE WHEN period = '中生代白亜紀' THEN binomen ELSE NULL END ,'\n') AS Cretaceous
FROM
`gcpugjoshi.demo.dinosaur`
GROUP BY
1, 2, 3
ORDER BY
1, 2, 3
これを実行すると、こんなマトリクスが出力できます。
三畳紀のエオラプトル・ルネンシスと白亜紀のフクイラプトル・キタダニエンシスが同じ獣脚亜目に属するという事が一目でわかるようになりましたね！
おわりに
今回は配列を扱う関数を使った小技をご紹介させて頂きました。
クエリで扱おうとすると少しややこしくて敬遠しがちな配列まわりの関数ですが、慣れるととても便利です。
今回は全くBigではないデータを扱いましたが(中の生き物はBigだけど)、BigQueryならBigなデータで同じ事をしても爆速で返してくれます。
みなさまもぜひご自身のデータを使ってお試しください。