業務BigQuery 12クエリパターン — 営業集計／LTV／Cohort／ウィンドウ関数で使う実装テンプレ（2026年版）

業務でSQLを書く人が結局よく使うのは12パターンに収束する

BigQueryで業務クエリを書いていると、同じパターンを何度も書いていることに気づく。営業の月次集計、契約LTV、Cohort分析、離脱検知、ファネル分析——テーブル設計とビジネス文脈は案件ごとに違っても、書くクエリの「骨格」は驚くほど共通している。本稿はその骨格を12パターンに整理し、それぞれBigQuery で実際に書くSQLとともに提示する。1パターンずつ「いつ使うか」と「典型ミス」も併記したので、業務でSQLを書く頻度が高い人は、自分の引き出しの抜けをチェックする目的で読んでほしい。

対象読者は、BigQueryでSELECT文は書けるが、「ウィンドウ関数」「CTE」「ピボット」あたりで詰まる中堅エンジニア・データアナリスト・コンサルである。リクルートのデータブログでも整理されているように、BigQueryの業務SQLは「集合演算 + ウィンドウ関数 + 配列処理」の3層を組み合わせて書くのが基本になっている。

パターン1: 月次の集計 — SUM + GROUP BY + DATE_TRUNC

もっとも頻繁に書くクエリ。請求月ごとの売上、契約月ごとの新規獲得など、業務レポートの土台になる。

SELECT
  DATE_TRUNC(contract_date, MONTH) AS month,
  SUM(amount) AS monthly_revenue,
  COUNT(DISTINCT customer_id) AS active_customers
FROM `project.dataset.contracts`
WHERE contract_date BETWEEN '2026-01-01' AND CURRENT_DATE()
GROUP BY month
ORDER BY month;

典型ミス: GROUP BY contract_date で書いてしまい日単位で集計されてしまうケース。月次集計は必ず DATE_TRUNC で粒度を揃える。

パターン2: 多条件集計 — CASE WHEN を SUM の中に入れる

「同じ月に、商品Aの売上と商品Bの売上を別列で出したい」のようなクエリ。CASE WHEN を SUM の中に書くのが定石で、ピボット集計の最小実装になる。

SELECT
  DATE_TRUNC(order_date, MONTH) AS month,
  SUM(CASE WHEN product = 'A' THEN amount ELSE 0 END) AS revenue_a,
  SUM(CASE WHEN product = 'B' THEN amount ELSE 0 END) AS revenue_b
FROM `project.dataset.orders`
GROUP BY month;

典型ミス: WHERE product IN ('A','B') で絞ってから集計すると、A・B同時に出せず2回クエリを書くハメになる。CASE WHEN なら1クエリで横並びに出せる。

パターン3: 累計（Running Total） — ウィンドウ関数 SUM OVER

「契約月単位の売上を、契約開始から累計でも見たい」シーン。ウィンドウ関数の累積計算解説でも頻出パターンとして取り上げられている。

SELECT
  contract_month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY contract_month) AS cumulative_revenue
FROM monthly_summary;

典型ミス: GROUP BY で累計を出そうとして自己結合で書いてしまうケース。データ量が増えるとパフォーマンスが指数的に劣化する。ウィンドウ関数で書けば線形時間で済む。

パターン4: 前期比（YoY/MoM）— LAG ウィンドウ関数

「先月比の伸び率」「前年同月比の増減」を1行ずつ出すクエリ。LAG を覚えると自己結合で書く必要がなくなる。

SELECT
  contract_month,
  monthly_revenue,
  LAG(monthly_revenue, 1) OVER (ORDER BY contract_month) AS prev_month_revenue,
  SAFE_DIVIDE(
    monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY contract_month),
    LAG(monthly_revenue, 1) OVER (ORDER BY contract_month)
  ) AS mom_growth_rate
FROM monthly_summary;

典型ミス: NULL / 0 割り算でクエリが失敗するケース。SAFE_DIVIDE を使って初月の null をハンドルする。

パターン5: ランキング — ROW_NUMBER / RANK / DENSE_RANK

「顧客別売上Top10」「営業担当別契約数ランキング」など、順位を付けるクエリ。3関数の使い分けを覚えると、業務要件に合わせて選べる。

SELECT
  customer_id,
  total_revenue,
  ROW_NUMBER() OVER (ORDER BY total_revenue DESC) AS rank_unique,
  RANK()       OVER (ORDER BY total_revenue DESC) AS rank_skip_tied,
  DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS rank_no_skip
FROM customer_summary;

使い分け: 同点を許す/許さない、同点後の連番をどう振るかで使う関数が変わる。「営業ボーナス支給の上位10名（同点は両方支給）」なら RANK、「ダッシュボードで Top 10 を表示（同点でも10名まで）」なら ROW_NUMBER。

パターン6: 重複行の除去 — ROW_NUMBER + QUALIFY

「顧客IDあたり最新の契約レコードだけ取りたい」シーン。BigQuery 限定の QUALIFY 句を使うと一発で書ける。

SELECT *
FROM `project.dataset.contracts`
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY contract_date DESC
) = 1;

典型ミス: サブクエリで書いて GROUP BY と MAX を組み合わせる古典的な書き方。読みづらく、性能も劣化しやすい。BigQuery なら QUALIFY が最短。

パターン7: LTV計算 — CTE + 集約結合

「顧客別の生涯売上」を出す。複数テーブル（契約・解約・追加課金）を結合する必要があり、CTE で中間結果に名前を付けると可読性が上がる。CTE / WITH句の解説でも、ビジネスロジックを段階分解する手段として推奨されている。

WITH first_purchase AS (
  SELECT customer_id, MIN(contract_date) AS first_date
  FROM contracts GROUP BY customer_id
),
total_spend AS (
  SELECT customer_id, SUM(amount) AS lifetime_revenue
  FROM contracts GROUP BY customer_id
)
SELECT
  fp.customer_id,
  fp.first_date,
  ts.lifetime_revenue,
  DATE_DIFF(CURRENT_DATE(), fp.first_date, DAY) AS days_since_first
FROM first_purchase fp
JOIN total_spend ts USING (customer_id);

パターン8: Cohort分析 — DATE_TRUNC + 月次差分

「2026年1月に新規獲得した顧客の、3ヶ月後の継続率」のような Cohort 分析。BI ツールで作ると重くなりがちな処理が、SQL で書けば BigQuery に処理を寄せられる。

WITH cohorts AS (
  SELECT
    customer_id,
    DATE_TRUNC(first_purchase_date, MONTH) AS cohort_month
  FROM customers
),
activities AS (
  SELECT
    c.cohort_month,
    DATE_DIFF(DATE_TRUNC(o.order_date, MONTH), c.cohort_month, MONTH) AS months_since,
    COUNT(DISTINCT o.customer_id) AS active_customers
  FROM cohorts c
  JOIN orders o USING (customer_id)
  GROUP BY cohort_month, months_since
)
SELECT * FROM activities ORDER BY cohort_month, months_since;

典型ミス: cohort 単位の正規化を忘れて絶対人数で比較してしまうケース。新規顧客数の異なる cohort 同士を比較するなら、初月の人数で割って継続率に変換する。

パターン9: ピボット — PIVOT 構文（BigQuery 専用）

「月を横軸、商品カテゴリを縦軸にした集計表」を SQL で作りたい。BigQuery には PIVOT が組み込まれており、複雑な CASE WHEN を書かなくて済む。

SELECT *
FROM (
  SELECT category, DATE_TRUNC(order_date, MONTH) AS month, amount
  FROM orders
)
PIVOT (
  SUM(amount) FOR month IN (
    '2026-01-01', '2026-02-01', '2026-03-01', '2026-04-01'
  )
);

典型ミス: PIVOT の月リストを動的にしたいケース。BigQuery の PIVOT は固定リスト前提なので、動的にするには EXECUTE IMMEDIATE でクエリ自体を生成する必要がある。

パターン10: ファネル分析 — STEP集計 + WIDOW最小値

「ステップ1（サインアップ）→ ステップ2（プラン選択）→ ステップ3（決済）」と進む顧客率を出すファネル分析。LAG ではなく、ステップごとの到達フラグを CTE で組んで集計する。

WITH funnel AS (
  SELECT
    customer_id,
    MAX(CASE WHEN event = 'signup' THEN 1 ELSE 0 END) AS step_1,
    MAX(CASE WHEN event = 'plan_select' THEN 1 ELSE 0 END) AS step_2,
    MAX(CASE WHEN event = 'payment' THEN 1 ELSE 0 END) AS step_3
  FROM events
  GROUP BY customer_id
)
SELECT
  SUM(step_1) AS reached_1,
  SUM(CASE WHEN step_1=1 THEN step_2 END) AS reached_2,
  SUM(CASE WHEN step_2=1 THEN step_3 END) AS reached_3
FROM funnel;

パターン11: 離脱検知 — DATE_DIFF + 閾値判定

「最終アクションから30日以上空いた顧客」を検出する。WHERE 句で DATE_DIFF を使うのが最短だが、BigQuery ではパーティション分割テーブルなら絞り込み式を工夫してスキャンコストを下げる。

WITH last_activity AS (
  SELECT customer_id, MAX(action_date) AS last_action
  FROM user_actions
  WHERE action_date > DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)  -- パーティション絞り込み
  GROUP BY customer_id
)
SELECT customer_id, last_action,
  DATE_DIFF(CURRENT_DATE(), last_action, DAY) AS inactive_days
FROM last_activity
WHERE DATE_DIFF(CURRENT_DATE(), last_action, DAY) >= 30;

パターン12: ARRAY 集約 — STRING_AGG / ARRAY_AGG

「顧客ごとに購入商品名をカンマ区切りで列挙」のような、横方向の集約。BigQuery では STRING_AGG / ARRAY_AGG で1行に集約できる。BigQuery 公式の aggregate functions リファレンスに挙動と引数の詳細がある。

SELECT
  customer_id,
  STRING_AGG(DISTINCT product_name, ', ' ORDER BY product_name) AS purchased_products,
  ARRAY_AGG(STRUCT(product_name, amount) ORDER BY amount DESC LIMIT 3) AS top3_orders
FROM orders
GROUP BY customer_id;

典型ミス: GROUP_CONCAT で書こうとするケース（これは MySQL の関数で、BigQuery にはない）。BigQuery では STRING_AGG が等価。

12パターンを業務シーン3つに当てはめる

シーン1: 月次の経営ダッシュボード

パターン1（月次集計）+ パターン2（多条件集計）+ パターン4（前期比）を組み合わせて、経営報告用ダッシュボードのバックエンドクエリを作る。BIツールに渡す前段の集計テーブルが、これだけで7割完成する。

シーン2: 顧客分析（LTV / Cohort / 離脱）

パターン7（LTV）+ パターン8（Cohort）+ パターン11（離脱検知）を1組のクエリ群として整備しておくと、マーケ・CS の質問の大半に SQL 1本で答えられる。

シーン3: 操作ログからの行動分析

パターン10（ファネル）+ パターン6（最新レコード）+ パターン12（ARRAY 集約）の組合せで、ユーザー行動ログから顧客プロファイルを SQL だけで生成できる。Python の pandas に渡す前の前処理をすべて BigQuery で済ませると、コスト・再現性ともに改善する。

業務BigQuery で「やってはいけない」3パターン

12パターンの逆として、業務でやりがちだが避けたい書き方を3つ。

• SELECT * を本番クエリで使う: BigQuery はスキャン課金のため、必要な列だけ書くのが基本。テスト時のみ SELECT * を許す
• WHERE 句にパーティション列の絞り込みを書かない: 数年分の全行をスキャンしてしまい、コストが跳ねる。日付パーティションを使うテーブルでは必ず WHERE date_col >= '...' を書く
• 巨大テーブル同士の JOIN を1回で書く: BigQuery は分散実行するが、両側が巨大だとシャッフルコストが指数的に増える。片方を WHERE で絞ってから JOIN する、または BROADCAST hint を検討する

関連ツールとの境界

12パターンを使えれば、業務クエリの8割は SQL で完結する。残り2割で詰まる場面は別ツールに移す:

• 機械学習で予測したい → BigQuery ML、または Python に出してから scikit-learn
• 図表で見たい → Looker Studio / Tableau に渡す（SQL は集計済みのテーブルを返す側に徹する）
• 毎日同じクエリを回したい → BigQuery Scheduled Queries / dbt でクエリ管理を分離する

SQL で全部やろうとすると、可読性とメンテナンス性が破綻する。12パターンは「業務の8割を高速化する道具箱」と捉え、それ以上の領域には別ツールに移すのが筋がよい。本稿が、その道具箱の輪郭をつくる助けになれば嬉しい。

あわせて読みたい

• AI導入支援会社の比較ガイド2026 — 5つの型と12の評価軸で最適なパートナーを選ぶ方法
• AI導入の契約で失敗しないための注意点 — 知的財産・データ所有権・PoC契約・内製化条項の実務ガイド【2026年版】
• 踏切のある在来線のGoA 2.5と山手線GoA 3──McKinsey予知保全と中国CR450 2030工程表