SQL中SUM OVER ORDER BY计算running total不返回负值问题排查

计算net_qty滚动累计值时始终无法返回负值，目标是计算未来可用于履行销售订单的可用数量累计值，但当前查询中Net_Qty的计算结果仅返回正数，实际业务中已存在应返回负余额的订单。使用数据库版本为SQL Server 15.0.2080.9。

• 存在无意义关联：查询关联了Job_Operation表但未读取该表任何字段，该表与Job表为一对多关系，会导致同一个作业生成多条重复记录，直接放大窗口函数SUM的计算基数。
• 执行顺序逻辑错误：DISTINCT去重操作在窗口函数计算完成后才会执行，也就是说当前累计值是基于重复的脏数据计算的，后续去重无法修正已经算错的累计结果。
• 累计排序维度错误：可用量累计需要按业务发生时间顺序逐单扣减，当前写法按Job编号排序累加，未使用业务实际的时间字段Sched_Start排序，你提到的2022-10-05对应Est_Qty为40的订单未被纳入对应时间点的累计计算，自然无法得到-35的预期结果。

先通过分组聚合消除多表关联产生的重复行，再按计划开工时间排序做滚动累计计算：

WITH Agg_Job_Material AS (
    SELECT 
        Job.Job,
        SO_Detail.Sales_Order,
        Material_Req.Material,
        Material_Location.On_Hand_Qty AS ON_Hand,
        Material_Req.Est_Qty,
        Job.Order_Quantity,
        Material_Req.Lead_Days,
        Job.Sched_Start,
        Job.Status
    FROM Material 
    INNER JOIN Material_Req ON Material.Material = Material_Req.Material
    INNER JOIN Job ON Job.Job = Material_Req.Job
    INNER JOIN Material_Location ON Material_Location.Material = Material.Material
    INNER JOIN SO_Detail ON SO_Detail.job = job.Job
    WHERE 
        Material_Req.Act_Qty <> Material_Req.Est_Qty
        AND Material.Material = '101291'
        AND job.Status = 'Pending' 
        AND job.sched_start >= GETDATE()
    GROUP BY
        Job.Job,
        SO_Detail.Sales_Order,
        Material_Req.Material,
        Material_Location.On_Hand_Qty,
        Material_Req.Est_Qty,
        Job.Order_Quantity,
        Material_Req.Lead_Days,
        Job.Sched_Start,
        Job.Status
)
SELECT 
    *,
    ON_Hand - SUM(Order_Quantity) OVER (ORDER BY Sched_Start ASC, Job ASC) AS Net_Qty
FROM Agg_Job_Material
ORDER BY Sched_Start ASC

• 移除了无意义的Job_Operation表关联，从源头减少重复数据产生
• 提前通过分组聚合拿到每个作业对应的唯一业务数据，避免重复行干扰窗口函数计算
• 窗口函数排序字段改为业务实际需要的Sched_Start，按时间顺序逐单扣减库存，累计到2022-10-05的40量订单时会正确返回-35的计算结果

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