SQL如何补全base_table缺失月份至当前月并计算累计销售额？

核心逻辑为先生成覆盖全周期的连续月份序列，与所有独立ID做笛卡尔积得到完整行骨架，左关联原始销售数据补空置后再计算累计销售额即可，完整实现代码如下（通用Spark SQL/Hive语法）：

WITH 
-- 获取所有独立业务ID
all_ids AS (
    SELECT DISTINCT id FROM base_table
),
-- 生成从最早交易月到当前月的连续月份序列，默认取每月第一天
date_series AS (
    SELECT 
        explode(sequence(
            (SELECT MIN(month) FROM base_table),
            date_trunc('month', current_date()),
            interval 1 month
        )) AS month
),
-- 拼接得到每个ID对应所有月份的完整行骨架
full_month_frame AS (
    SELECT a.month, b.id 
    FROM date_series a
    CROSS JOIN all_ids b
),
-- 左关联原始表，无销售数据的月份销售额补0
sales_fill AS (
    SELECT 
        f.month,
        f.id,
        COALESCE(b.sales, 0) AS sales
    FROM full_month_frame f
    LEFT JOIN base_table b 
        ON f.month = b.month AND f.id = b.id
)
-- 最终计算累计销售额
SELECT 
    month,
    id,
    sales,
    SUM(sales) OVER (
        PARTITION BY id 
        ORDER BY month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales
FROM sales_fill
ORDER BY id, month;

如果你使用的是MySQL 8.0及以上版本，将上述date_series部分替换为递归CTE写法即可：

date_series AS (
    SELECT MIN(month) AS month FROM base_table
    UNION ALL
    SELECT DATE_ADD(month, INTERVAL 1 MONTH) 
    FROM date_series 
    WHERE month < DATE_FORMAT(CURDATE(), '%Y-%m-01')
)

关键说明：

• CROSS JOIN保证每个ID都覆盖所有需要统计的月份，不会出现ID或月份遗漏
• COALESCE将无销售记录的月份销售额置为0，保证累计值可以正确延续上月结果
• 窗口累计计算逻辑和你原有写法一致，骨架补全后即可得到符合预期的累计销售额

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