BigQueryのループ処理で月次集計を自動化しよう

はじめに

データ集計の仕事をしていると、こんな作業に遭遇しませんか？

-- 2024年1月分
WHERE transaction_date >= '2024-01-01' AND transaction_date < '2024-02-01'

これを毎月コピペして、日付だけ書き換えて実行。結果をスプレッドシートに貼り付け。翌月になったらまた同じことを繰り返す...めんどくさい。

ですが、BigQueryのループ処理を使うことで、一気に過去分から集計することができます。今回はその実践例を紹介します。

課題例

あるオンライン書店の会員向けキャンペーンを例とします。毎月、以下のような流れでレポートを作成するとします。

月次の作業フロー

1. 会員ランクの判定（例：2024年1月購入実績で判定）

   WHERE transaction_datetime >= '2024-01-01' 
     AND transaction_datetime < '2024-02-01'
   

2. 判定後の購入実績の追跡（2月〜3月の購入を集計）

   WHERE transaction_datetime >= '2024-02-01' 
     AND transaction_datetime < '2024-04-01'
   

3. スプレッドシートに貼り付けて月次推移を更新

毎月手作業のタスクです。コピペミスで日付を間違えることもしばしば...

「スケジュールクエリ使えばいいじゃん？」への反論

ここまで読んで「それってスケジュールクエリで毎月自動実行すればいいのでは？」と思った方、鋭いです。

実際、私も最初はそう考えました。でもうまくいかないのです。

スケジュールクエリの限界

スケジュールクエリは確かに便利ですが、以下のような制約があります。

1. 過去データの再集計ができない

-- スケジュールクエリで毎月実行
INSERT INTO monthly_report
SELECT ...
WHERE transaction_datetime >= DATE_TRUNC(CURRENT_DATE(), MONTH)
  AND transaction_datetime < DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)

これで今月分は自動化できます。でも：

• 「去年のキャンペーン効果、ロジック変えて全部集計し直して」
• 「2020年からの全期間のトレンドを見たい」
• 「過去のデータに不備があったから再計算して」

こういう要望が来たとき、スケジュールクエリでは対応できません。結局、過去の分は手作業で月ごとにクエリを回すことになります。

2. 複雑な期間ロジックに弱い

今回の要件は単純な「先月分を集計」ではありません：

• 1月の購入実績で会員ランクを判定
• その会員の2月〜3月の購入を追跡
• 結果は「1月」のデータとして記録

つまり、「N月」のデータを作るのに「N+1月〜N+3月」のデータが必要なんです。

スケジュールクエリで書くとこうなります：

-- 2024年4月1日に実行される想定
WHERE transaction_datetime >= '2024-02-01'  -- 2ヶ月前
  AND transaction_datetime < '2024-03-01'   -- 先月
-- でも結果は「2024年1月分」として記録したい

このロジックを正しく実装するには、複雑な日付計算が必要で、可読性が著しく低下します。

3. テスト・検証がしづらい

スケジュールクエリは「定期実行」が前提なので：

• ロジックを変更したときに、全期間で正しく動くか確認できない
• 1ヶ月待たないと次の実行結果が見られない
• バグがあっても気づくのが遅れる

スケジュールクエリでバグを仕込んでないか不安になります。

ループ処理との使い分け

シチュエーション	スケジュールクエリ	ループ処理
当月分だけ自動追加	⭕	△（オーバースペック）
過去データの再集計	❌	⭕
複雑な期間ロジック	△（読みづらい）	⭕
アドホック分析	❌	⭕
全期間の一貫性担保	❌	⭕

私の場合、「全期間を一気に再計算できる」ことが最重要だったので、ループ処理を選びました。

そして、このループクエリ自体をスケジュールクエリに設定することで、両方のメリットを享受できます：

• 今: ループクエリで2020年〜現在までを一気に集計
• 毎月: スケジュールクエリとして自動実行され、最新月が追加される
• 将来: ロジック変更時も、ループクエリを修正して再実行すれば全期間を再計算

なぜ単純な変数置換ではダメなのか

次に「変数使えばいいじゃん」という疑問にも答えておきます。

最初は私もこんな感じで書いてみました：

DECLARE target_month DATE DEFAULT '2024-01-01';

SELECT ...
WHERE transaction_datetime >= target_month
  AND transaction_datetime < DATE_ADD(target_month, INTERVAL 1 MONTH)

でも、これだと1ヶ月分しか取得できません。

今回の要件は：

• 2020年1月から現在まで、すべての月のデータが欲しい
• 各月で「会員ランク判定期間」と「その後の購入追跡期間」が異なる
• 過去のデータも含めて一気に集計し直したい

つまり、同じロジックを月の数だけ繰り返す必要があるわけです。

「じゃあUNION ALLで繋げばいいじゃん？」と思うかもしれませんが...

SELECT ... WHERE ... -- 2020年1月
UNION ALL
SELECT ... WHERE ... -- 2020年2月
UNION ALL
SELECT ... WHERE ... -- 2020年3月
-- ... (あと50回以上続く)

これは地獄。クエリが数百行になり、月が増えるたびに追記が必要です。

ループ処理による解決策

そこで登場するのがBigQueryのスクリプト機能です。

基本的な考え方

-- 開始月を決める
DECLARE analysis_month DATE DEFAULT DATE '2020-01-01';
-- 今月まで繰り返す
DECLARE current_month DATE DEFAULT DATE_TRUNC(CURRENT_DATE(), MONTH);

-- 結果を溜めておく箱を用意
CREATE TEMP TABLE temp_sales_summary (...);

-- ループで各月のデータを集計して箱に入れていく
LOOP
  IF analysis_month > current_month THEN 
    LEAVE;  -- 現在月を超えたら終了
  END IF;

  -- この月のデータを集計して一時テーブルに追加
  INSERT INTO temp_sales_summary (...)
  SELECT ...
  
  -- 次の月へ
  SET analysis_month = DATE_ADD(analysis_month, INTERVAL 1 MONTH);
END LOOP;

-- 最後に溜めたデータを整形して出力
SELECT ... FROM temp_sales_summary;

実装のポイント

1. 各月で異なる期間を動的に設定

LOOP
  -- 今見ている月が2024年1月だとする
  
  -- 会員ランク判定期間: 2024-02-01 〜 2024-03-01
  SET start_date = DATETIME(DATE_ADD(analysis_month, INTERVAL 1 MONTH));
  SET end_date = DATETIME(DATE_ADD(analysis_month, INTERVAL 2 MONTH));
  
  -- 購入実績追跡期間: 2024-02-01 〜 2024-04-01
  SET end_date_2 = DATETIME(DATE_ADD(analysis_month, INTERVAL 3 MONTH));

この例では、1月を分析するときに：

• 2月の購入で会員ランクを判定
• 2月〜3月の購入実績を追跡

というロジックになっています。月ごとに「1ヶ月ずつズレた期間」を自動で計算してくれるわけです。

2. 会員ランクの判定

  -- ループ内のサブクエリ
  INNER JOIN (
    SELECT DISTINCT customer_id,
      CASE
        WHEN campaign_name LIKE 'シルバー会員%' THEN '2シルバー会員'
        WHEN campaign_name LIKE 'ゴールド会員%' THEN '3ゴールド会員'
        WHEN campaign_name LIKE 'プラチナ会員%' THEN '4プラチナ会員'
      END AS membership_tier
    FROM `bookstore-analytics.sales_data.customer_transactions`
    WHERE transaction_datetime >= start_date 
      AND transaction_datetime < end_date  -- ★ここが毎回変わる
      AND (campaign_name LIKE 'シルバー会員%' OR ...)
  ) c ON a.customer_id = c.customer_id

各月でstart_dateとend_dateが変わるので、同じクエリでも対象期間が自動で切り替わります。

3. 結果を一時テーブルに蓄積

  INSERT INTO temp_sales_summary (
    analysis_month,
    campaign_name,
    membership_tier,
    revenue_amount
  )
  SELECT
    analysis_month,
    a.campaign_name,
    c.membership_tier,
    SUM(sales_amount) AS revenue_amount
  FROM ...
  GROUP BY 1, 2, 3;

ループが回るたびに、一時テーブルに1ヶ月分のデータが追加されていきます。

最終的なレポート作成

ループが終わったら、蓄積したデータを使って集計結果を算出します：

WITH tier_summary AS (
  SELECT
    analysis_month,
    SUM(CASE WHEN membership_tier = '2シルバー会員' 
        THEN revenue_amount ELSE 0 END) AS silver_revenue,
    SUM(CASE WHEN membership_tier = '3ゴールド会員' 
        THEN revenue_amount ELSE 0 END) AS gold_revenue,
    SUM(CASE WHEN membership_tier = '4プラチナ会員' 
        THEN revenue_amount ELSE 0 END) AS platinum_revenue
  FROM temp_sales_summary
  GROUP BY analysis_month
)
SELECT * FROM tier_summary
ORDER BY analysis_month;

これで、過去のある時点から現在までの全期間の会員ランク別売上が一気に取得できます！

ループ処理に関する要点整理

良い点

1. 完全自動化

ひと月分ずつ手作業で実行していたのが、クエリを1回実行するだけになりました。

2. ヒューマンエラーによるミスがなくなった

「あれ、この月だけ日付間違えてる...」みたいなことがゼロに。

3. 過去データの再集計が楽

「去年のキャンペーン、ロジック変えて集計し直して」と言われても、クエリをちょっと修正して実行するだけ。これがスケジュールクエリとの最大の違いです。

4. 新しい月が自動で追加される

current_monthを使っているので、翌月になれば自動的に最新月まで集計されます。このクエリ自体をスケジュールクエリに設定すれば、完全自動化も可能。

5. ロジック変更時の検証が簡単

全期間を一気に再計算できるので、「このロジックで本当に合ってるかな？」というテストがすぐできます。スケジュールクエリだと1ヶ月待つ必要がありますが、ループなら数分で確認完了。

注意点

コストには気をつけよう

ループ内で毎回フルスキャンが走ると、コストが跳ね上がります。下記のように期間指定を忘れず行い、スキャン量を削減するようにしましょう。

WHERE transaction_datetime >= start_date 
  AND transaction_datetime < end_date

まとめ

BigQueryのループ処理は、こんな時に本当に便利です：

• 毎月同じクエリをコピペして日付だけ変えている
• 過去の全期間を一気に集計し直したい
• 月ごとに異なる期間範囲で集計が必要
• 将来のデータ追加にも自動対応したい
• ロジック変更時の検証を素早く行いたい

スケジュールクエリは「定期実行」に特化していますが、ループ処理は「全期間の一貫した再計算」に強いです。両方をうまく組み合わせることで、いい感じのデータパイプラインが作れます。

ぜひ試してみてください！

参考リンク

• BigQuery スクリプトの公式ドキュメント
• BigQuery のパーティショニング
• BigQuery スケジュールクエリ
• BigQuery のループ制御文の使いどころを自分なりに整理してみた