BigQuery縦持ちデータを動的に横持ちデータにする方法

データ分析/データサイエンス

はじめに

ドワンゴ教育事業でデータアナリストとして働いている小林です。
一般的にデータアナリストはデータの収集・分析を通して組織の意思決定を支援する役割を期待されることが多く、ドワンゴ教育事業における私のミッションもKPI動向の可視化やダッシュボード / レポートの作成・提供を通してデータドリブンな組織に貢献するところにあります。
私たち教育事業には施策を実行する企画者やビジネス上の意思決定者だけでなく、サービスを活用して教育の現場に立っている方々、サービスに展開している教材を制作しているチームなど多様な方面からデータ収集・分析の需要があります。それだけにやりがいも大きく楽しい日々を過ごしています。

課題について（導入に代えて）

クエリを書いて、結果を分析して、資料にまとめて、展開して、共有して・・・みたいな仕事をしているとSQLで抽出した縦持ちのデータを横持ちに作り変えたいことがよくあります。
ドワンゴ教育事業ではGoogleCloudConsoleからBigQuery（以下BQ）を使ってデータ集計することが多いのですが、BQ業務でも頻繁にそんな場面が生まれます。
典型的な縦持ちデータは以下のような出力テーブルが想定できます。

縦持ちテーブルの例

Title : 月間ビュー数（2023年4月～2024年3月）

ページ名 年月 ビュー数
TOP 2023-04 1,000
登録ページ 2023-04 100
商品ページA 2023-04 600
商品ページB 2023-04 200
TOP 2023-05 1,500
登録ページ 2023-05 200
商品ページA 2023-05 500
商品ページB 2023-05 400
・・・ ・・・ ・・・
TOP 2024-03 5,000
商品ページD 2024-03 1,000

今どきはBIツール（TableauやLooker Studioなど）にそのまま投げて良い感じに可視化してもらうことも増えましたが、アドホックな分析や不特定多数に向けた資料を作成する場合、手元で手早く整形をするべき場面が出てきます。
横持ちデータとは、具体的には以下のような出力テーブルです。

横持ちテーブルの例

Title : 月間ビュー数（2023年4月～2024年3月）

ページ名\年月 2023-04 2023-05 ・・・ 2024-03
TOP 1,000 1,500 ・・・ 5,000
商品ページA 600 500 ・・・ 300
商品ページB 200 400 ・・・ 600
登録ページ 100 200 ・・・ 300
商品ページD ・・・ ・・・ ・・・ 1,000

本記事では、このような縦持ちテーブル→横持ちテーブルを実現する手段の中から個人的に好きな手段を3つ提示します。

手段目次

  1. 表計算アプリケーションの関数で解決
  2. SQL完結でも可用性に疑問!? PIVOT構文を使ってみる
  3. 完全動的！ LOOP構文で動的に横に伸ばす
  4. （後日談）これが正解

手段①：表計算アプリケーションで解決

なんじゃそりゃ？！という感じですが、自分の業務の中で特にアドホックな分析業務だと、90%はこの手段をとります。ご存知の通りピボット機能やコピペなどの様々な手段が考えられるところ、ここでは関数（＆関数のコピペ）を使った解決法を例示します。

以下、抽出結果をコピペしたシートとして横持ち変換のために適切な関数を考えてみます。

作業するシート例

sheet_image

まず、側を用意するためE列にページ名を並べたとします。具体的には = UNIQUE(A:A) みたいな関数を利用してもよし、手作りっぽく資料に必要なページ名だけを並べてもよし。同様にF列以右も適当にやります。

この時、F2セルに良い感じの関数を書いて表内にコピペすることで横持ちの表を作成します。さて、どんな関数を書けばよいでしょうか？
本題ではないのでさらっといきます。

関数例
= SUMPRODUCT(($A:$A = $E2) * ($B:$B = F$1) , $C:$C)
結果が正しく出ていそうなのを確認したら、関数をコピペして解決です。

メリット

  • 手早い

デメリット

  • アドホックな業務に限定される（動的な横持ちとは程遠い）

手段②：SQL完結！ PIVOT構文を使ってみる

表計算？それじゃないんだよ！ という方にはこちらもご検討ください。
想定ケースとしてテーブル「monthly_pv」には以下のようなカラム構成でレコードが入っているとします。

TableName : monthly_pv

カラム名 型 例
page_title STRING TOP
access_month STRING 2023-04
monthly_view INT64 123

つまり、 SELECT * FROM monthly_pv ORDER BY access_month; というクエリを投げた結果が冒頭の縦持ちデータの例です。

GoogleSQLにはPIVOT構文が用意されていて、集計関数と組み合わせることでスマート＆パワフルに縦→横を実現することが出来ます。
具体的には以下のようなクエリです。

SELECT
  *
FROM
  `monthly_pv`
  PIVOT (
    MAX(monthly_view)
    FOR access_month IN ('2023-04','2023-05','2023-06','2023-07','2023-08','2023-09','2023-10','2023-11','2023-12','2024-01','2024-02','2024-03')
  );

この構文は集計関数と組み合わせることで実力を発揮します。上の例だと仮に四半期ごとの総view数を足し込みたい（そして仮にquarterみたいなカラムがある）とすると、MAX関数→SUM関数に変更しFOR ~ INの部分を修正するだけです。
実際の業務データはもうちょっと複雑で一筋縄ではいかないかもしれませんが、知らないとSELECT文として延々とCASE WHENを書き連ねることになるので、マスターしたいところです。

ところで、ここまでやったらFOR access_month IN ～のところを動的に書きたい、と思うのは自然なことです。FOR access_month IN (SELECT DISTINCT access_month FROM monthly_pv ORDER BY 1) みたいな構文が許されるなら最高なのですが・・・これは通りません（よく見るとめちゃくちゃな構文ですが、愚直に文字列を生成して変数としてINの中に突っ込んでもうまくいきません）。そのため、横幅となるカラムを動的に生成することが難しくスケジュールタスクに乗せて自動化したりするのは適当ではありません。この手段の最大の不満ポイントですね。

メリット

  • BigQuery完結

デメリット

  • INの中身を手書きしなければならない（動的に作れない）

手段③：LOOPと一時テーブルを使って動的に横持ちテーブルを生成する！（本題）

上の例だと12か月ほどの横持ちカラムを手書きするだけでよいですが、例えば大量の教材の各テストデータについて受講してくれた生徒を1レコードとして各テストの点数を横で持つテーブルを作りたい、というようなケースでやろうとすると、PIVOT構文を書くだけで1時間の集中を要する、みたいなことになりかねません。あるいは日次で更新されるデータマートに対して集計結果を自動で可視化する際の抽出クエリとしても適当とはいえません（新たな月を迎えるたびにクエリの保守が必要になってしまいます）。

そこでLOOP構文を使って解決しましょう！というのが、本資料の本題です。

分析業務としてPythonや他のプログラミング言語を利用されている方にはFOR文としてなじみ深いものですが、SQLでLOOPを利用できることを最近知りました（この記事を書いてみようと思った動機になっています）。さっそく具体的なクエリを書いてみます、仮想テーブルとして前述のmonthly_pvを利用します。

--ループ変数
DECLARE idx INT64 DEFAULT 0;
--横に伸ばすための月ラベルの配列
DECLARE month_labels ARRAY<STRING> ;

--まずは横に伸ばす月ラベルを管理するために配列化する
SET month_labels = (
    SELECT ARRAY_AGG(i.access_month) 
    FROM (SELECT DISTINCT access_month FROM `monthly_pv` ORDER BY 1) AS i 
); 

--次に出力用のテーブルを作成する。この時点ではページ名のみカラムで持つ
CREATE OR REPLACE TABLE monthly_pv_output AS ( 
    SELECT DISTINCT page_title FROM `monthly_pv` 
);

--LOOP処理開始、月数に応じて動的にカラムを生成してJOINしていく
LOOP

--カラム操作をする前に出力用テーブルを一時テーブル（TEMP TABLE）に退避
CREATE TEMP TABLE output_table AS ( SELECT * FROM `monthly_pv_output` );

--出力用テーブルにカラムを追加。BQのカラム命名規則に従い_を先頭に追加し-を_に置き換える
EXECUTE IMMEDIATE FORMAT("""
    ALTER TABLE `monthly_pv_output` ADD COLUMN %s INT64
""" , REPLACE(CONCAT('_',month_labels[idx]),'-','_')) ;

--出力用テーブルを上書き。まずは全データクリア
DELETE FROM `monthly_pv_output` WHERE page_title IS NOT NULL ;

--退避した一時テーブルとJOINしたいカラムを使って出力用テーブルを更新。OUTER JOINが望ましい
INSERT INTO `monthly_pv_output` 
SELECT i.*,j.monthly_view 
FROM output_table AS i LEFT OUTER JOIN ( 
    SELECT page_title,monthly_view 
    FROM `monthly_pv` 
    WHERE access_month = month_labels[idx] 
) AS j ON i.page_title = j.page_title 
;

--一時退避テーブルをDROP
DROP TABLE output_table;

--LOOPの継続可否。index = 配列長となったら終了
SET idx = idx + 1;
IF idx = ARRAY_LENGTH(month_labels) THEN
    LEAVE; 
END IF;

END LOOP;

ポイントとしてはLOOP と CREATE TEMP TABLEを組み合わせて出力用テーブルを退避→カラム追加→退避データ＋新カラム分JOINで全更新・・・を繰り返すことで、BQの順序付きなArrayをインデックスと連動して用いています。

これがLOOPの推奨された利用法なのか、というのは分かりませんが、構文を使った自由度の高いクエリバリュエーションをお伝えできれば光栄です。

メリット

  • BigQuery完結＆動的に横持ちを実現

デメリット

  • 少し複雑

後日談

以上までの内容をチームの同僚に見てもらったところ「PIVOTは動的に使えますよ、EXECUTE IMMEDIATE FORMATを利用して！」と教えてもらい、膝から崩れ落ちました。
PIVOT構文の中身を強引に文字列生成しただけではうまくいかないですが、EXECUTE IMMEDIATE FORMATは動的に生成した文字列を含むクエリ文字列を解釈してくれるので、これで解決できます。なんで思いつかなかった…。
具体的には以下のように書きます。

--横に伸ばすための月ラベルの配列
DECLARE month_labels ARRAY<STRING> ;

--まずは横に伸ばす月ラベルを管理するために配列化する
SET month_labels = (
    SELECT ARRAY_AGG(i.access_month) 
    FROM (SELECT DISTINCT access_month FROM `monthly_pv` ORDER BY 1) AS i 
); 

--配列をUNNEST展開しながら文字列生成する
EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM `monthly_pv`
  PIVOT (MAX(monthly_view) FOR access_month IN %s);
""", (SELECT CONCAT("(",STRING_AGG(CONCAT("'",months,"'"),","),")") FROM UNNEST(month_labels) AS months))

良い感じにシンプルに書けました。いやあ、これぞ学び！これぞチームで働いている意味！

しかもLOOPでクエリを書いて動かしてみると、少し長い横持ちデータを作ろうと思うとrateLimitExceededエラー（短時間で同一テーブルの書き換えを連続して行うと出現します）が出てしまうこともあるという不完全さ…。ということでBQに登録されていた自分のPROCEDUREは無事にリファクタリングされました。

メリット

  • BigQuery完結＆動的に横持ちを実現。しかも簡潔。

デメリット

  • なし（これを正解とさせてください）

おわりに

最後はオチが付いてしまいましたが、とっても楽しくSQLで遊ぶことが出来ました。 「魔法は探し求めている時が一番たのしい」と有名な書物にも書いてありましたが、完全に同意です。
以上をもってSQLの実験記録みたいな記事を終えます。お読みくださり、ありがとうございました。

We are hiring!

株式会社ドワンゴの教育事業では、一緒に未来の当たり前の教育をつくるメンバーを募集しています。
私の所属するチームは企画職とエンジニア、データサイエンティストが混在して様々な実験的な取り組みを一緒にドライブするとっても刺激的な職場です（指摘をくれたのは同僚のデータサイエンティストです）。異なるスキルセットを持った同じ志のメンバーが集まっているというだけでワクワクしますよね！

カジュアル面談も行っています。お気軽にご連絡ください！

カジュアル面談応募フォームはこちら

www.nnn.ed.nico

開発チームの取り組み、教育事業の今後については、他の記事や採用資料をご覧ください。

speakerdeck.com