MySQL实现无订单月份沿用上期累计值的累计和查询

原有写法缺陷 #

原有逻辑仅对存在订单的月份做了聚合计算，缺失「无订单月份补行」的步骤，导致累计值计算只覆盖有订单的月份，无法满足空月金额记0、累计值顺延的要求。

核心实现逻辑 #

要实现连续月份的累计统计，分5步处理：

• 生成统计周期内的全部连续月份序列
• 提取所有需要统计的业务维度组合（即lead_id、no_pks、customer_name、point_name、funder_id的去重集合）
• 将维度集合和连续月份做笛卡尔积，搭出每个维度下所有月份的完整数据骨架
• 左关联原逻辑聚合出的月度订单金额，无匹配订单的月份金额填0
• 基于补全后的全量数据做窗口累计求和，空月因为金额为0，累计值会自动沿用上月结果

可直接运行的SQL代码（适配MySQL8.0+） #

with recursive month_seq as (
    -- 统计起始月取表中最早订单所在月，可按需修改为固定日期
    select date_format(min(order_date), '%Y-%m-01') as stat_month
    from x
    union all
    select date_add(stat_month, interval 1 month)
    from month_seq
    -- 统计截止月取表中最晚订单所在月，可按需修改为current_date等固定值
    where stat_month < (select date_format(max(order_date), '%Y-%m-01') from x)
),
dim_keys as (
    select distinct
        lead_id,
        no_pks,
        customer_name,
        point_name,
        funder_id
    from x
),
full_base as (
    select
        d.lead_id,
        d.no_pks,
        d.customer_name,
        d.point_name,
        d.funder_id,
        date_format(m.stat_month, '%Y-%m') as year_and_month_order
    from dim_keys d
    cross join month_seq m
),
monthly_agg as (
    select  
        lead_id,
        no_pks,
        customer_name,
        point_name,
        funder_id,
        DATE_FORMAT(order_date,'%Y-%m') as year_and_month_order,
        sum(total_amount) as outstanding
    from x
    group by
        lead_id,
        no_pks,
        customer_name,
        point_name,
        funder_id,
        year_and_month_order
)
select
    f.lead_id,
    f.no_pks,
    f.customer_name,
    f.point_name,
    f.funder_id,
    f.year_and_month_order,
    coalesce(m.outstanding, 0) as outstanding,
    sum(coalesce(m.outstanding, 0)) over(
        partition by f.lead_id, f.no_pks 
        order by f.year_and_month_order
    ) as cumulative_outstanding
from full_base f
left join monthly_agg m
on f.lead_id = m.lead_id
and f.no_pks = m.no_pks
and f.customer_name = m.customer_name
and f.point_name = m.point_name
and f.funder_id = m.funder_id
and f.year_and_month_order = m.year_and_month_order
order by f.lead_id, f.no_pks, f.year_and_month_order;

适配说明 #

• 如果使用不支持递归CTE的低版本数据库，可以提前构建一张通用日历维度表，存储所有需要统计的年月值，替换掉代码中month_seq递归部分即可
• 统计周期可以按需调整，比如固定统计近12个月的数据，只需要修改month_seq中的起止筛选条件
• 关联月度聚合数据时必须带上所有业务维度字段做匹配，避免出现维度错位导致的金额计算错误
• 空月金额填充为0后，窗口累计求和会自动保留上月累计值，不需要额外调用last_value等函数做补值处理

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