ドワンゴ教育サービス開発者ブログドワンゴ教育サービス開発者ブログ読者になるドワンゴ教育サービス開発者ブログ2024-07-17BigQuery縦持ちデータを動的に横持ちデータにする方法データ分析/データサイエンスはじめにドワンゴ教育事業でデータアナリストとして働いている小林です。レポートの作成・提供を通してデータドリブンな組織に貢献するところにあります。私たち教育事業には施策を実行する企画者やビジネス上の意思決定者だけでなく、サービスを活用して教育の現場に立っている方々、サービスに展開している教材を制作しているチームなど多様な方面からデータ収集・分析の需要があります。それだけにやりがいも大きく楽しい日々を過ごしています。課題について(導入に代えて)クエリを書いて、結果を分析して、資料にまとめて、展開して、共有して・・・みたいな仕事をしているとSQLで抽出した縦持ちのデータを横持ちに作り変えたいことがよくあります。ドワンゴ教育事業ではGoogleCloudConsoleからBigQuery(以下BQ)を使ってデータ集計することが多いのですが、BQ業務でも頻繁にそんな場面が生まれます。典型的な縦持ちデータは以下のような出力テーブルが想定できます。Studioなど)にそのまま投げて良い感じに可視化してもらうことも増えましたが、アドホックな分析や不特定多数に向けた資料を作成する場合、手元で手早く整形をするべき場面が出てきます。横持ちデータとは、具体的には以下のような出力テーブルです。手段1:表計算アプリケーションで解決なんじゃそりゃ?!という感じですが、自分の業務の中で特にアドホックな分析業務だと、90%はこの手段をとります。ご存知の通りピボット機能やコピペなどの様々な手段が考えられるところ、ここでは関数(&関数のコピペ)を使った解決法を例示します。以下、抽出結果をコピペしたシートとして横持ち変換のために適切な関数を考えてみます。作業するシート例まず、側を用意するためE列にページ名を並べたとします。みたいな関数を利用してもよし、手作りっぽく資料に必要なページ名だけを並べてもよし。同様にF列以右も適当にやります。この時、F2セルに良い感じの関数を書いて表内にコピペすることで横持ちの表を作成します。どんな関数を書けばよいでしょうか?本題ではないのでさらっといきます。$C:$C)結果が正しく出ていそうなのを確認したら、関数をコピペして解決です。いう方にはこちらもご検討ください。想定ケースとしてテーブル「monthly_pv」には以下のようなカラム構成でレコードが入っているとします。いうクエリを投げた結果が冒頭の縦持ちデータの例です。GoogleSQLにはPIVOT構文が用意されていて、集計関数と組み合わせることでスマート&パワフルに縦→横を実現することが出来ます。具体的には以下のようなクエリです。);この構文は集計関数と組み合わせることで実力を発揮します。INの部分を修正するだけです。WHENを書き連ねることになるので、マスターしたいところです。~のところを動的に書きたい、と思うのは自然なことです。みたいな構文が許されるなら最高なのですが・・・これは通りません(よく見るとめちゃくちゃな構文ですが、愚直に文字列を生成して変数としてINの中に突っ込んでもうまくいきません)。そのため、横幅となるカラムを動的に生成することが難しくスケジュールタスクに乗せて自動化したりするのは適当ではありません。この手段の最大の不満ポイントですね。メリットBigQuery完結デメリットINの中身を手書きしなければならない(動的に作れない)手段3:LOOPと一時テーブルを使って動的に横持ちテーブルを生成する!(本題)上の例だと12か月ほどの横持ちカラムを手書きするだけでよいですが、例えば大量の教材の各テストデータについて受講してくれた生徒を1レコードとして各テストの点数を横で持つテーブルを作りたい、というようなケースでやろうとすると、PIVOT構文を書くだけで1時間の集中を要する、みたいなことになりかねません。日次で更新されるデータマートに対して集計結果を自動で可視化する際の抽出クエリとしても適当とはいえません(新たな月を迎えるたびにクエリの保守が必要になってしまいます)。そこでLOOP構文を使って解決しましょう!というのが、本資料の本題です。分析業務としてPythonや他のプログラミング言語を利用されている方にはFOR文としてなじみ深いものですが、SQLでLOOPを利用できることを最近知りました(この記事を書いてみようと思った動機になっています)。具体的なクエリを書いてみます、仮想テーブルとして前述のmonthly_pvを利用します。--次に出力用のテーブルを作成する。);--出力用テーブルにカラムを追加。;--出力用テーブルを上書き。;--退避した一時テーブルとJOINしたいカラムを使って出力用テーブルを更新。output_table;--LOOPの継続可否。TABLEを組み合わせて出力用テーブルを退避→カラム追加→退避データ+新カラム分JOINで全更新・・・を繰り返すことで、BQの順序付きなArrayをインデックスと連動して用いています。これがLOOPの推奨された利用法なのか、というのは分かりませんが、構文を使った自由度の高いクエリバリュエーションをお伝えできれば光栄です。FORMATを利用して!」と教えてもらい、膝から崩れ落ちました。FORMATは動的に生成した文字列を含むクエリ文字列を解釈してくれるので、これで解決できます。なんで思いつかなかった...。具体的には以下のように書きます。months))良い感じにシンプルに書けました。いやあ、これぞ学び!これぞチームで働いている意味!しかもLOOPでクエリを書いて動かしてみると、少し長い横持ちデータを作ろうと思うとrateLimitExceededエラー(短時間で同一テーブルの書き換えを連続して行うと出現します)が出てしまうこともあるという不完全さ...。いうことでBQに登録されていた自分のPROCEDUREは無事にリファクタリングされました。メリットBigQuery完結&動的に横持ちを実現。デメリットなし(これを正解とさせてください)おわりに最後はオチが付いてしまいましたが、とっても楽しくSQLで遊ぶことが出来ました。「魔法は探し求めている時が一番たのしい」と有名な書物にも書いてありましたが、完全に同意です。以上をもってSQLの実験記録みたいな記事を終えます。お読みくださり、ありがとうございました。hiring!株式会社ドワンゴの教育事業では、一緒に未来の当たり前の教育をつくるメンバーを募集しています。私の所属するチームは企画職とエンジニア、データサイエンティストが混在して様々な実験的な取り組みを一緒にドライブするとっても刺激的な職場です(指摘をくれたのは同僚のデータサイエンティストです)。異なるスキルセットを持った同じ志のメンバーが集まっているというだけでワクワクしますよね!カジュアル面談も行っています。お気軽にご連絡ください!カジュアル面談応募フォームはこちらwww.nnn.ed.nico開発チームの取り組み、教育事業の今後については、他の記事や採用資料をご覧ください。Railsを触って、JavaScriptで開発した...2022-05-16エンジニアが企画開発をやってみたこんにちは。(3)引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。