如何在SQL中计算带递归调整与盈余封顶的净短缺累计值

需要计算生产周期内的净短缺值，规则如下：

• 生产周期内有固定日配额
• 每日实际产量与配额对比，产生当日短缺或盈余
• 短缺可累计，盈余仅能抵消累计短缺，且最多将累计短缺抵消至0
• 无累计短缺时，盈余无法留存

之前尝试用SUM() OVER(ORDER BY Date)失败，因为该函数无法处理“盈余仅抵消短缺、无短缺时盈余不累计”的依赖逻辑，目前用C#循环处理，希望用纯SQL查询实现（非存储过程循环）。

解决方案：递归CTE实现递推计算 #

由于每日净短缺值依赖前一日的计算结果，递归CTE（Common Table Expression）是最适合的纯SQL实现方式，它可以逐行处理这种递推逻辑。

假设你的生产数据表名为Production，结构如下：

• Date：日期（唯一且可排序）
• DailyQuota：日配额
• Yield：当日实际产量

基础实现（净短缺为非负值，代表累计未被抵消的短缺） #

WITH RecursiveNetShort AS (
    -- 初始化：取周期第一天的数据，计算初始净短缺
    SELECT
        Date,
        DailyQuota,
        Yield,
        MAX(0, DailyQuota - Yield) AS NetShort
    FROM Production
    WHERE Date = (SELECT MIN(Date) FROM Production)

    UNION ALL

    -- 递归计算后续每日的净短缺
    SELECT
        p.Date,
        p.DailyQuota,
        p.Yield,
        -- 核心逻辑：前一日净短缺 + 当日短缺/盈余，结果不小于0
        MAX(0, rns.NetShort + (p.DailyQuota - p.Yield)) AS NetShort
    FROM Production p
    INNER JOIN RecursiveNetShort rns
        ON p.Date = (SELECT MIN(Date) FROM Production WHERE Date > rns.Date)
)
-- 查询每日数据及周期最终净短缺
SELECT
    Date,
    DailyQuota,
    Yield,
    NetShort,
    LAST_VALUE(NetShort) OVER(ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS PeriodFinalNetShort
FROM RecursiveNetShort
ORDER BY Date;

适配负净短缺的实现（代表累计盈余抵消后的净缺口） #

如果你的NetShort定义为负数值代表短缺、0代表无缺口（如你提到的最终结果为-3），只需调整递归部分的计算逻辑：

WITH RecursiveNetShort AS (
    SELECT
        Date,
        DailyQuota,
        Yield,
        MIN(0, Yield - DailyQuota) AS NetShort
    FROM Production
    WHERE Date = (SELECT MIN(Date) FROM Production)

    UNION ALL

    SELECT
        p.Date,
        p.DailyQuota,
        p.Yield,
        -- 核心逻辑：前一日净短缺 + 当日盈余/短缺，结果不大于0
        MIN(0, rns.NetShort + (p.Yield - p.DailyQuota)) AS NetShort
    FROM Production p
    INNER JOIN RecursiveNetShort rns
        ON p.Date = (SELECT MIN(Date) FROM Production WHERE Date > rns.Date)
)
SELECT
    Date,
    DailyQuota,
    Yield,
    NetShort,
    LAST_VALUE(NetShort) OVER(ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS PeriodFinalNetShort
FROM RecursiveNetShort
ORDER BY Date;

代码说明 #

1. 初始化部分：获取生产周期的第一天数据，计算初始净短缺值，匹配规则中“无累计短缺时盈余不留存”的要求。
2. 递归计算部分：逐行处理后续日期，基于前一日的净短缺值，结合当日产量与配额的差值，按照规则计算当日净短缺——既实现了短缺累计，又保证盈余仅能抵消现有短缺，无短缺时盈余不会留存。
3. 最终查询：返回每日详细数据，并通过LAST_VALUE窗口函数直接获取整个周期的最终净短缺值。

注意事项 #

• 确保Date字段唯一且可排序，若存在同一天多批次数据，需增加排序字段（如批次号）保证递归顺序正确。
• 主流数据库（SQL Server、PostgreSQL、MySQL 8.0+）均支持递归CTE，语法细节可根据使用的数据库做细微调整。

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