🗂️
積み上げ計算を再帰SQLなしで書くクエリ

2025/12/24に公開
 背景事業計画を策定する際、「将来（Nヶ月後）の売上目標」をシミュレーションしたいというニーズがあります。とくに、SaaSビジネスなどのように顧客が継続利用し続けてくれることで、単純な掛け算ではなく「顧客の積み上げ（ストック）」を計算する必要があります。
「今月獲得した顧客は、平均3ヶ月は契約維持する」
「その後は継続率50%で更新し、平均単価はX円になる」
「2回目以降の更新は継続率80%に向上する」
といったロジックをSQLで実装したい。計算ロジックをSQLやdbtに移管することで、Gitでのバージョン管理が可能になり、予実管理の再現性が高まります。
実装の方法として、WITH RECURSIVE（再帰クエリ）が考えられます。しかし、再帰クエリには「デバッグが辛い」「無限ループのリスク」「可読性が低く属人化しやすい」という課題があります（自分は苦手です...）。そこで今回は、AIエディタとペアプロをしている中で知った再帰を使わずに GENERATE_ARRAYを使った配列展開アプローチがよかったので自分の備忘録がてら紹介します。個人的には、再帰クエリよりも圧倒的にデバッグがしやすく、可読性が高い点が気に入っています。
また作成したロジックを検算しつつも、今後運用していくにあたってロジックが意図せず壊れてないかを検知するために、dbt unit testsも導入してみました。

 ロジックの全体像今回の要件は、以下のような「将来の月次売上目標値」を算出することです。
入力: 商品ごとの顧客(プロジェクト)獲得計画（例：2025年9月に5件獲得）。その他にも初回契約期間、継続率、更新ごとの単価変動など（時期や商品により異なる）複数のパラメータがある
出力: 月次×商品ごとの積み上げ売上予測。
処理の流れは以下のとおりです。
サイクル展開: そのプロジェクトが最大何回更新されるか（サイクル）の分だけ行を増やす。ここで「何回目の更新か」が決まるため、継続率や単価を計算できます。
月次展開: 各サイクルの期間（例: 3ヶ月）の分だけ、さらに行を増やしてカレンダー上の月にマッピングします。
集計: 最後に GROUP BY で月ごとに足し合わせます。
この方法の最大のメリットは、中間CTE（Common Table Expression）を SELECT * すれば、「どのプロジェクトがいつまで生存している計算になっているか」が行単位で完全に可視化できる点です。これは予実管理のロジックをデバッグする上で非常に強力です。

 実装ステップと解説実際にCursorと一緒に作成したSQLをベースに解説します。 ※わかりやすくするため、データはCTE内で生成しています。
BigQuery SQL全文はこちら（クリックして展開）WITH

-- 商品・月ごとのパラメータ（単価や継続率など）
import_parameters AS (
  SELECT
    plan_month,
    product_name,
    project_contract_users,  -- 新規契約顧客数
    initial_project_period,   -- 初回契約期間(月)
    initial_retention_rate,   -- 初回継続率
    recurring_project_period, -- 継続契約期間(月)
    second_retention_rate,    -- 2回目以降継続率
    max_continuation_count,   -- 最大継続回数
    initial_monthly_price,    -- 初回月額単価
    recurring_monthly_price   -- 継続月額単価
  FROM
    UNNEST([
      STRUCT(
        DATE '2025-09-01' AS plan_month,
        'product_a' AS product_name,
        5 AS project_contract_users,
        3 AS initial_project_period,
        0.5 AS initial_retention_rate,
        6 AS recurring_project_period,
        0.8 AS second_retention_rate,
        5 AS max_continuation_count,
        100 AS initial_monthly_price,
        200 AS recurring_monthly_price
      )
      -- 省略: 他の商品や月のパラメータもここで定義
    ])
),

-- 初回と継続サイクル（0=初回, 1..N=継続）を商品ごとに展開
logic_cycle_expansion AS (
  SELECT
    product_name,
    plan_month,
    continuation_index, -- 0なら初回、1以降は継続
    -- サイクルの開始月を計算（前のサイクルの期間を足し合わせる）
    DATE_ADD(
      DATE_ADD(plan_month, INTERVAL 1 MONTH), --翌月契約開始
      INTERVAL CASE
        WHEN continuation_index = 0 THEN 0
        WHEN continuation_index = 1 THEN initial_project_period
        ELSE initial_project_period + (continuation_index - 1) * recurring_project_period
      END
      MONTH
    ) AS cycle_start_month,
    -- 継続率を掛けて期待プロジェクト数を計算（POW関数で累乗）
    CASE
      WHEN continuation_index = 0 THEN project_contract_users
      WHEN continuation_index = 1 THEN project_contract_users * initial_retention_rate
      ELSE project_contract_users * initial_retention_rate * POW(second_retention_rate, continuation_index - 1)
      END AS expected_projects,
    -- 期間や単価もサイクルによって切り替え
    CASE
      WHEN continuation_index = 0 THEN initial_project_period
      ELSE recurring_project_period
      END AS cycle_duration_months,
    CASE
      WHEN continuation_index = 0 THEN initial_monthly_price
      ELSE recurring_monthly_price
    END AS monthly_price
  FROM
    import_parameters
    -- ★ここで配列展開して行を増やす
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, max_continuation_count)) AS continuation_index
),

logic_cycle_months AS (
  SELECT
    product_name,
    continuation_index,
    DATE_ADD(cycle_start_month, INTERVAL month_offset MONTH) AS active_month,
    ROUND(expected_projects, 2) AS expected_projects,
    CAST(ROUND(expected_projects, 2) * monthly_price AS INT64) AS expected_monthly_sales,
  FROM
    logic_cycle_expansion
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, cycle_duration_months - 1)) AS month_offset
),

final AS (
  SELECT
    product_name,
    FORMAT_DATE('%Y-%m', active_month) AS target_month,
    SUM(CASE WHEN continuation_index = 0 THEN expected_projects ELSE 0 END) AS expected_new_projects,
    SUM(CASE WHEN continuation_index > 0 THEN expected_projects ELSE 0 END) AS expected_continuation_projects,
    SUM(expected_projects) AS expected_total_projects,
    SUM(CASE WHEN continuation_index = 0 THEN expected_monthly_sales ELSE 0 END) AS expected_new_sales,
    SUM(CASE WHEN continuation_index > 0 THEN expected_monthly_sales ELSE 0 END) AS expected_continuation_sales,
    SUM(expected_monthly_sales) AS expected_total_sales,
  FROM
    logic_cycle_months
  GROUP BY
    product_name,
    target_month
)

SELECT
  *,
FROM
  final
ORDER BY
  product_name,
  target_month

 1.計画値の準備まず、パラメータが時期によって変わることを考慮し、plan_month（適用開始月）を持たせた構造にします。パラメータの値は架空の数値をいれています。
-- 商品・月ごとのパラメータ（単価や継続率など）
import_parameters AS (
  SELECT
    plan_month,
    product_name,
    project_contract_users,  -- 新規契約顧客数
    initial_project_period,   -- 初回契約期間(月)
    initial_retention_rate,   -- 初回継続率
    recurring_project_period, -- 継続契約期間(月)
    second_retention_rate,    -- 2回目以降継続率
    max_continuation_count,   -- 最大継続回数
    initial_monthly_price,    -- 初回月額単価(円)
    recurring_monthly_price   -- 継続月額単価(円)
  FROM
    UNNEST([
      STRUCT(
        DATE '2025-09-01' AS plan_month,
        'product_a' AS product_name,
        5 AS project_contract_users,
        3 AS initial_project_period,
        0.5 AS initial_retention_rate,
        6 AS recurring_project_period,
        0.8 AS second_retention_rate,
        5 AS max_continuation_count,
        100 AS initial_monthly_price,
        200 AS recurring_monthly_price
      )
      -- 省略: 他の商品や月のパラメータもここで定義
    ])
),

 2.サイクルの展開ここが肝となる処理です。GENERATE_ARRAY(0, max_continuation_count) を使って、1つの計画行を「初回 + 継続N回」分の行に爆発させます。この段階で、サイクルごとの「期待顧客数（減衰後）」や「単価」を計算してしまいます。
-- 初回と継続サイクル（0=初回, 1..N=継続）を商品ごとに展開
logic_cycle_expansion AS (
  SELECT
    product_name,
    plan_month,
    continuation_index, -- 0なら初回、1以降は継続
    -- サイクルの開始月を計算（前のサイクルの期間を足し合わせる）
    DATE_ADD(
      DATE_ADD(plan_month, INTERVAL 1 MONTH), --翌月契約開始
      INTERVAL CASE
        WHEN continuation_index = 0 THEN 0
        WHEN continuation_index = 1 THEN initial_project_period
        ELSE initial_project_period + (continuation_index - 1) * recurring_project_period
      END
      MONTH
    ) AS cycle_start_month,
    -- 継続率を掛けて期待プロジェクト数を計算（POW関数で累乗）
    CASE
      WHEN continuation_index = 0 THEN project_contract_users
      WHEN continuation_index = 1 THEN project_contract_users * initial_retention_rate
      ELSE project_contract_users * initial_retention_rate * POW(second_retention_rate, continuation_index - 1)
      END AS expected_projects,
    -- 期間や単価もサイクルによって切り替え
    CASE
      WHEN continuation_index = 0 THEN initial_project_period
      ELSE recurring_project_period
      END AS cycle_duration_months,
    CASE
      WHEN continuation_index = 0 THEN initial_monthly_price
      ELSE recurring_monthly_price
      END AS monthly_price
  FROM
    import_parameters
    -- ★ここで配列展開して行を増やす
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, max_continuation_count)) AS continuation_index
),
この時点のCTEの中身は以下です。


product_name
plan_month
continuation_index
cycle_start_month
expected_projects
cycle_duration_months
monthly_price


product_a
2025-09-01
0
2025-10-01
5.0
3
100

product_a
2025-09-01
1
2026-01-01
2.5
6
200

product_a
2025-09-01
2
2026-07-01
2.0
6
200

product_a
2025-09-01
3
2027-01-01
1.600
6
200

product_a
2025-09-01
4
2027-07-01
1.280
6
200

product_a
2025-09-01
5
2028-01-01
1.024
6
200


 3.月次の展開と集計サイクルごとの行ができたら、さらにその期間（3ヶ月なら3行）分だけ行を増やし、カレンダー上の active_month に変換します。これで準備完了です。

logic_cycle_months AS (
  SELECT
    product_name,
    continuation_index,
    DATE_ADD(cycle_start_month, INTERVAL month_offset MONTH) AS active_month,
    ROUND(expected_projects, 2) AS expected_projects,
    CAST(ROUND(expected_projects, 2) * monthly_price AS INT64) AS expected_monthly_sales,
  FROM
    logic_cycle_expansion
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, cycle_duration_months - 1)) AS month_offset
),
この時点のCTEの中身は以下です。


product_name
continuation_index
active_month
expected_projects
expected_monthly_sales


product_a
0
2025-10-01
5.0
500

product_a
0
2025-11-01
5.0
500

product_a
0
2025-12-01
5.0
500

product_a
1
2026-01-01
2.5
500

product_a
1
2026-02-01
2.5
500

product_a
1
2026-03-01
2.5
500

product_a
1
2026-04-01
2.5
500

product_a
1
2026-05-01
2.5
500

product_a
1
2026-06-01
2.5
500

product_a
2
2026-07-01
2.0
400

...





product_a
5
2028-06-01
1.02
204

あとは GROUP BY するだけで積み上げ計算が完了します
final AS (
  SELECT
    product_name,
    FORMAT_DATE('%Y-%m', active_month) AS target_month,
    SUM(CASE WHEN continuation_index = 0 THEN expected_projects ELSE 0 END) AS expected_new_projects,
    SUM(CASE WHEN continuation_index > 0 THEN expected_projects ELSE 0 END) AS expected_continuation_projects,
    SUM(expected_projects) AS expected_total_projects,
    SUM(CASE WHEN continuation_index = 0 THEN expected_monthly_sales ELSE 0 END) AS expected_new_sales,
    SUM(CASE WHEN continuation_index > 0 THEN expected_monthly_sales ELSE 0 END) AS expected_continuation_sales,
    SUM(expected_monthly_sales) AS expected_total_sales,
  FROM
    logic_cycle_months
  GROUP BY
    product_name,
    target_month
)

SELECT
  *,
FROM
  final
ORDER BY
  product_name,
  target_month

 dbt unit testsでの品質保証例として上記SQLを parametersをデータソース(input)として作成し、sales_simulation というdbt modelでロジックを作成したときに行うdbt unit testsの実装例も以下に示します。dbt unit testsのおかげで、実装時に検算した結果が運用時も維持できてるのかを担保できるので便利です。
unit_tests:
  - name: test_future_sales_value_calculation
    description: "将来の売上計算のロジックテスト"
    model: sales_simulation
    given:
      - input: ref('parameters')
        format: dict
        rows:
          - {plan_month: '2025-09-01', business_name: 'A事業', product_name: '商品A', 'project_contract_users': 5, initial_project_period: 3, initial_retention_rate: 0.5, recurring_project_period: 6, second_retention_rate: 0.8', initial_monthly_price: 100, recurring_monthly_price: 200}
    expect:
      format: dict
      rows:
        - {target_month: '2025-10-01', expected_new_projects: 5.0, expected_continuation_projects: 0.0, expected_new_sales: 500, expected_continuation_sales: 0}
        - {target_month: '2025-11-01', expected_new_projects: 5.0, expected_continuation_projects: 0.0, expected_new_sales: 500, expected_continuation_sales: 0}
        - {target_month: '2025-12-01', expected_new_projects: 5.0, expected_continuation_projects: 0.0, expected_new_sales: 500, expected_continuation_sales: 0}
        - {target_month: '2026-01-01', expected_new_projects: 0.0, expected_continuation_projects: 2.5, expected_new_sales: 0, expected_continuation_sales: 500}
        - {target_month: '2026-02-01', expected_new_projects: 0.0, expected_continuation_projects: 2.5, expected_new_sales: 0, expected_continuation_sales: 500}
        - {target_month: '2026-03-01', expected_new_projects: 0.0, expected_continuation_projects: 2.5, expected_new_sales: 0, expected_continuation_sales: 500}
        - {target_month: '2026-04-01', expected_new_projects: 0.0, expected_continuation_projects: 2.5, expected_new_sales: 0, expected_continuation_sales: 500}
        - {target_month: '2026-05-01', expected_new_projects: 0.0, expected_continuation_projects: 2.5, expected_new_sales: 0, expected_continuation_sales: 500}
        - {target_month: '2026-06-01', expected_new_projects: 0.0, expected_continuation_projects: 2.5, expected_new_sales: 0, expected_continuation_sales: 500}
        - {target_month: '2026-07-01', expected_new_projects: 0.0, expected_continuation_projects: 2.0, expected_new_sales: 0, expected_continuation_sales: 400}
        ...(省略)...
        - {target_month: '2028-06-01', expected_new_projects: 0.0, expected_continuation_projects: 1.02, expected_new_sales: 0, expected_continuation_sales: 204}        
実装して気づいた点として、unit testsをPassさせるためには浮動小数点の扱い（ROUND処理など）を厳密に定義する必要がありました。これは実装時には面倒に感じますが、裏を返せば「曖昧な計算仕様を許さない」ことになり、将来的な数値の信頼性担保に直結します。

 おわりに事業計画数値の管理および計算ロジックをSQL（dbt）に移管することで、Gitでのバージョン管理が可能になり、予実管理の再現性が高まります。また、「商品ごとにパラメータを変えたい」「途中で単価が変わる仕様を入れたい」といった追加要件に対しても、都度CTEの整合性を保ちながらリファクタリングしてくれるため、非常に効率的に実装できました。複雑な積み上げ計算に悩んでいる方は、この GENERATE_ARRAY パターンも試してみてください。
product_name	plan_month	continuation_index	cycle_start_month	expected_projects	cycle_duration_months	monthly_price
product_a	2025-09-01	0	2025-10-01	5.0	3	100
product_a	2025-09-01	1	2026-01-01	2.5	6	200
product_a	2025-09-01	2	2026-07-01	2.0	6	200
product_a	2025-09-01	3	2027-01-01	1.600	6	200
product_a	2025-09-01	4	2027-07-01	1.280	6	200
product_a	2025-09-01	5	2028-01-01	1.024	6	200
背景

ロジックの全体像

実装ステップと解説

1.計画値の準備

2.サイクルの展開

3.月次の展開と集計

dbt unit testsでの品質保証

おわりに

Discussion
