BigQuery如何生成含期初与期末余额的交易报表？

核心思路 #

先生成合同有效期内的所有财季区间，按合同+财季汇总交易数据，再用窗口函数按合同分组做累计计算——利用LAG()直接获取上一季度期末余额作为当期期初，彻底避免循环依赖问题。

数据表结构说明 #

对齐你提供的示例，两张表结构如下：

• 合同表contracts：name(合同名称), start_date(合同起始日), opening_balance(初始余额)
• 交易表transactions：contract_name(关联合同), transaction_date(交易日期), transaction_type(交易类型：预付款/付款/特许权使用费), amount(金额)

完整BigQuery SQL代码 #

WITH -- 1. 生成合同覆盖的所有财季区间
contract_fy_quarters AS (
    SELECT
        c.name AS contract_name,
        -- 生成财季标识（示例财年从4月开始，可按需调整）
        FORMAT_DATE('FY%y-%yQ%q', DATE_TRUNC(DATE_ADD(c.start_date, INTERVAL 9 MONTH), YEAR)) AS fy_quarter,
        c.opening_balance AS initial_opening
    FROM contracts c
    -- 生成合同有效期内的所有财季（这里默认覆盖20个季度，可调整）
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 20)) AS q_offset
    WHERE DATE_ADD(DATE_TRUNC(DATE_ADD(c.start_date, INTERVAL 9 MONTH), YEAR), INTERVAL q_offset * 3 MONTH) <= CURRENT_DATE()
),
-- 2. 按合同+财季汇总交易数据
transaction_quarterly_summary AS (
    SELECT
        t.contract_name,
        FORMAT_DATE('FY%y-%yQ%q', DATE_TRUNC(DATE_ADD(t.transaction_date, INTERVAL 9 MONTH), YEAR)) AS fy_quarter,
        SUM(IF(t.transaction_type = '预付款', t.amount, 0)) AS total_prepayment,
        SUM(IF(t.transaction_type = '付款', t.amount, 0)) AS total_payment,
        SUM(IF(t.transaction_type = '特许权使用费', t.amount, 0)) AS total_royalty
    FROM transactions t
    GROUP BY contract_name, fy_quarter
),
-- 3. 关联计算季度期初、期末余额
final_report AS (
    SELECT
        cfq.contract_name,
        cfq.fy_quarter,
        -- 期初余额：首季用合同初始余额，后续取上季度期末
        COALESCE(LAG(fq.end_balance) OVER (PARTITION BY cfq.contract_name ORDER BY cfq.fy_quarter), cfq.initial_opening) AS quarter_opening_balance,
        COALESCE(tqs.total_prepayment, 0) AS total_prepayment,
        COALESCE(tqs.total_payment, 0) AS total_payment,
        COALESCE(tqs.total_royalty, 0) AS total_royalty,
        -- 期末余额 = 期初 + 预付款 - 付款 - 特许权使用费
        COALESCE(LAG(fq.end_balance) OVER (PARTITION BY cfq.contract_name ORDER BY cfq.fy_quarter), cfq.initial_opening) 
        + COALESCE(tqs.total_prepayment, 0) 
        - COALESCE(tqs.total_payment, 0) 
        - COALESCE(tqs.total_royalty, 0) AS quarter_end_balance
    FROM contract_fy_quarters cfq
    LEFT JOIN transaction_quarterly_summary tqs 
        ON cfq.contract_name = tqs.contract_name AND cfq.fy_quarter = tqs.fy_quarter
    -- 提前计算各季度期末，简化窗口函数逻辑
    LEFT JOIN (
        SELECT
            cfq_inner.contract_name,
            cfq_inner.fy_quarter,
            cfq_inner.initial_opening 
            + COALESCE(tqs_inner.total_prepayment, 0) 
            - COALESCE(tqs_inner.total_payment, 0) 
            - COALESCE(tqs_inner.total_royalty, 0) AS end_balance
        FROM contract_fy_quarters cfq_inner
        LEFT JOIN transaction_quarterly_summary tqs_inner 
            ON cfq_inner.contract_name = tqs_inner.contract_name AND cfq_inner.fy_quarter = tqs_inner.fy_quarter
    ) fq ON cfq.contract_name = fq.contract_name AND cfq.fy_quarter = fq.fy_quarter
    ORDER BY cfq.contract_name, cfq.fy_quarter
)
SELECT * FROM final_report;

关键问题解决说明 #

• 消除循环依赖：用LAG()窗口函数直接获取上一季度期末余额，无需递归CTE
• 正确取季度期初：按合同分组排序后，确保每个季度只取上一期期末，不会错误汇总季度内数据
• 支持同季多交易：交易汇总阶段按合同+财季分组，自动合并同季度同日期的多笔交易
• 兼容无交易季度：通过LEFT JOIN和COALESCE处理空值，保证报表连续

财年调整提示 #

如果财年起始月不是4月，修改DATE_ADD(..., INTERVAL 9 MONTH)中的月份偏移：

• 财年从1月开始：改为DATE_ADD(..., INTERVAL 0 MONTH)
• 财年从7月开始：改为DATE_ADD(..., INTERVAL 6 MONTH)

内容的提问来源于stack exchange，提问作者Jopgood