Oracle SQL：如何补全无销售日期的累计求和结果？

你的原查询确实能计算有销售记录日期的累计和，但因为只基于存在交易的记录，所以缺失了那些没有销售的日期。要补全这些日期并沿用最近的累计值，我们可以通过构建完整的维度组合 + 窗口函数填充缺失值来实现，具体步骤如下：

1. 核心思路 #

• 先生成覆盖SH.SALES表中所有日期的完整日期序列
• 获取所有唯一的CHANNEL_ID和PROD_ID组合
• 将日期序列与渠道产品组合做笛卡尔积，得到所有需要的维度记录
• 左连接原销售数据计算每日销售额，再用窗口函数计算累计并填充缺失值

2. 完整SQL代码 #

WITH date_range AS (
    -- 生成从最早销售日期到最晚销售日期的每日序列
    SELECT MIN(TIME_ID) AS dt FROM SH.SALES
    UNION ALL
    SELECT dt + INTERVAL '1' DAY FROM date_range
    WHERE dt + INTERVAL '1' DAY <= (SELECT MAX(TIME_ID) FROM SH.SALES)
),
channel_product AS (
    -- 获取所有存在销售的渠道-产品唯一组合
    SELECT DISTINCT CHANNEL_ID, PROD_ID FROM SH.SALES
),
all_dimensions AS (
    -- 构建所有日期-渠道-产品的完整维度
    SELECT cr.dt, cp.CHANNEL_ID, cp.PROD_ID
    FROM date_range cr
    CROSS JOIN channel_product cp
),
daily_sales AS (
    -- 计算每个维度的每日销售额（无销售时为0）
    SELECT 
        ad.dt AS TIME_ID,
        ad.CHANNEL_ID,
        ad.PROD_ID,
        COALESCE(SUM(s.AMOUNT_SOLD), 0) AS daily_amount
    FROM all_dimensions ad
    LEFT JOIN SH.SALES s 
        ON ad.dt = s.TIME_ID 
        AND ad.CHANNEL_ID = s.CHANNEL_ID 
        AND ad.PROD_ID = s.PROD_ID
    GROUP BY ad.dt, ad.CHANNEL_ID, ad.PROD_ID
)
SELECT 
    CHANNEL_ID,
    PROD_ID,
    TIME_ID,
    -- 用LAST_VALUE取最近的非空累计值，补全无销售日期的累计
    LAST_VALUE(SUM(daily_amount) OVER (PARTITION BY CHANNEL_ID, PROD_ID ORDER BY TIME_ID) IGNORE NULLS) 
        OVER (PARTITION BY CHANNEL_ID, PROD_ID ORDER BY TIME_ID) AS "Cumulative Sum"
FROM daily_sales
ORDER BY CHANNEL_ID ASC, PROD_ID ASC, TIME_ID ASC;

3. 关键部分解释 #

• date_range：用递归CTE生成连续的日期序列，覆盖销售数据的整个时间范围，确保没有日期遗漏
• all_dimensions：通过笛卡尔积让每个渠道-产品组合都匹配所有日期，构建出完整的分析维度
• LAST_VALUE(...) IGNORE NULLS：无销售日期的累计和本质等于前一天的累计值，这个函数会自动忽略空值，取最近的有效累计值填充，完美解决缺失问题

另外提个小细节：原查询中的DISTINCT其实是多余的，因为SUM(AMOUNT_SOLD) OVER (...)已经按TIME_ID排序，每个日期的累计值是唯一的，去掉DISTINCT不会影响结果，还能提升一点查询效率。

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