SQL中SUM() OVER(PARTITION BY)计算燃尽值异常，求原因

问题背景 #

使用SQL和Tableau制作燃尽图时，编写的查询计算出的burn_down列结果不符合预期。预期值为8、7，但实际得到-54、-55。

当前表结构 #

原查询语句 #

select  lc.calendardate,
        lc.campaign_name,
        lc.loaded_count,
        dcc.calendardate,
        dcc.campaign_name,
        dcc.completed_call_count,
        sum(cast(lc.loaded_count as int) - cast(dcc.completed_call_count as int)) over(partition by lc.campaign_name order by lc.calendardate asc) as burn_down
from adjusted_loaded_count as lc
left join adjusted_daily_calls_completed as dcc on
    lc.calendardate = dcc.calendardate and
    lc.campaign_name = dcc.campaign_name
where lc.campaign_name is not null

问题原因 #

1. 窗口函数逻辑偏差：原查询中，窗口函数计算的是每日加载数 - 每日完成数的累加值。但当某日没有完成数据时（如2023-09-05），dcc.completed_call_count为NULL，导致当日差值为62 - NULL = NULL，窗口SUM会忽略该NULL值，后续仅累加0-54=-54和0-1=-1，最终得到-54、-55。
2. 燃尽图逻辑错误：燃尽图的核心逻辑应该是初始总加载数 - 截止当日的累计完成数，而非每日加载与完成数差值的累加。

修正方案 #

调整查询逻辑，先计算活动的总加载数，再计算截止每日的累计完成数，最后用总加载数减去累计完成数得到燃尽值：

WITH campaign_total_load AS (
    SELECT 
        campaign_name,
        SUM(CAST(loaded_count AS INT)) AS total_loaded
    FROM adjusted_loaded_count
    WHERE campaign_name IS NOT NULL
    GROUP BY campaign_name
),
daily_completed_cumulative AS (
    SELECT
        dcc.calendardate,
        dcc.campaign_name,
        SUM(CAST(dcc.completed_call_count AS INT)) OVER (
            PARTITION BY dcc.campaign_name 
            ORDER BY dcc.calendardate ASC
        ) AS cumulative_completed
    FROM adjusted_daily_calls_completed AS dcc
    WHERE dcc.campaign_name IS NOT NULL
)
SELECT
    lc.calendardate,
    lc.campaign_name,
    lc.loaded_count,
    dcc.completed_call_count,
    ctl.total_loaded - COALESCE(dcc.cumulative_completed, 0) AS burn_down
FROM adjusted_loaded_count AS lc
LEFT JOIN campaign_total_load AS ctl 
    ON lc.campaign_name = ctl.campaign_name
LEFT JOIN daily_completed_cumulative AS dcc 
    ON lc.calendardate = dcc.calendardate 
    AND lc.campaign_name = dcc.campaign_name
WHERE lc.campaign_name IS NOT NULL
ORDER BY lc.campaign_name, lc.calendardate;

修正后结果 #

内容的提问来源于stack exchange，提问作者j.jerrod.taylor