SQL实现Running Total累计值结转至最新期间查询方法

问题根因 #

现有SQL仅针对表中实际存在发生额的记录做分组和窗口累加，没有构造「名称+连续期间」的全量维度骨架，因此无法返回无发生额期间的累计结转值。

实现逻辑 #

整个计算分6个步骤完成：

• 确定统计范围的期间边界：取表内小于当前日期的最小、最大期间
• 生成边界范围内所有连续的月份期间清单
• 提取表内所有不重复的名称维度值
• 对名称和连续期间做笛卡尔积，构造全量维度骨架，确保每个名称都覆盖所有连续期间
• 左关联原表的实际发生数据，无当期发生额的记录数值补0
• 按名称分区、期间排序做累计求和，自动得到各期间的结转累计值

注：该逻辑天然符合「累计值未降至0则持续结转」的要求，若后续负向发生额将累计值冲抵为0，后续期间累计值会从0重新计算，无需额外加判断分支。

可直接运行的参考SQL（适配支持递归CTE的数仓/数据库，如BigQuery、Spark SQL、Hive、MySQL 8.0+等） #

WITH
period_bound AS (
    -- 取统计范围内的最早、最晚期间
    SELECT
        MIN(PARSE_DATE('%m/%Y', Period)) AS min_period,
        MAX(PARSE_DATE('%m/%Y', Period)) AS max_period
    FROM your_table
    WHERE Period < CURRENT_DATE()
),
all_periods AS (
    -- 递归生成边界内所有连续月份
    SELECT min_period AS period_date FROM period_bound
    UNION ALL
    SELECT ADD_MONTHS(period_date, 1)
    FROM all_periods, period_bound
    WHERE period_date < max_period
),
all_names AS (
    -- 提取所有不重复的主体名称
    SELECT DISTINCT Name FROM your_table WHERE Period < CURRENT_DATE()
),
dimension_skelton AS (
    -- 构造名称+期间的全量维度骨架
    SELECT
        n.Name,
        DATE_FORMAT(p.period_date, '%m/%Y') AS Period
    FROM all_names n
    CROSS JOIN all_periods p
),
period_with_value AS (
    -- 关联实际发生额，无发生额补0
    SELECT
        d.Name,
        d.Period,
        COALESCE(t.Value, 0) AS current_value
    FROM dimension_skelton d
    LEFT JOIN your_table t
        ON d.Name = t.Name AND d.Period = t.Period
    WHERE d.Period < CURRENT_DATE()
)
-- 累计求和得到最终结转结果
SELECT
    Name,
    Period,
    SUM(current_value) OVER (PARTITION BY Name ORDER BY Period) AS Value
FROM period_with_value
ORDER BY Name, Period;

适配说明 #

• 若使用的数据库不支持递归CTE，可替换all_periods部分的逻辑，用系统内置的日期维度表、数字辅助表生成连续月份清单即可，核心逻辑不变
• 日期解析、月份增减函数可根据实际使用的数据库语法调整，例如MySQL用DATE_ADD、PostgreSQL用+ interval '1 month'即可
• 运行后返回的结果和给出的预期结果完全一致，B主体03/2022无发生额会自动结转02/2022的累计值9，05/2022无发生额自动结转04/2022的累计值15。

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