如何在SQL中实现Excel式跨行对比计算Rpr_order_consumption列

要实现你需要的递归计算逻辑，不能直接用普通的ROWS UNBOUNDED PRECEDING——因为该列的计算依赖同组内前一行的计算结果，属于逐行递归依赖，可以用递归CTE来实现，具体步骤如下：

1. 先给数据添加行号（用于递归顺序） #

首先在基础CTE里，按Parts分组，给每组内的行按顺序编号，确保递归时能逐行处理：

WITH cte_Main AS (
    SELECT 
        Theater, 
        Parts, 
        Open_Orders, 
        Non_Restricted_Empty_Bins,
        -- 按Parts分组，给每组内的行编号，保证顺序
        ROW_NUMBER() OVER (PARTITION BY Parts ORDER BY Theater) AS rn
    FROM Apps.Fill_Rate
),
-- 2. 递归CTE计算目标列
cte_Recursive AS (
    -- 递归起点：每组的第一行，直接用Open_Orders减去Non_Restricted_Empty_Bins
    SELECT 
        Theater, 
        Parts, 
        Open_Orders, 
        Non_Restricted_Empty_Bins,
        rn,
        CAST(Open_Orders - Non_Restricted_Empty_Bins AS DECIMAL(18,2)) AS Rpr_order_consumption
    FROM cte_Main
    WHERE rn = 1

    UNION ALL

    -- 递归部分：非第一行，用上一行的计算结果减去当前行的Non_Restricted_Empty_Bins
    SELECT 
        m.Theater, 
        m.Parts, 
        m.Open_Orders, 
        m.Non_Restricted_Empty_Bins,
        m.rn,
        CAST(r.Rpr_order_consumption - m.Non_Restricted_Empty_Bins AS DECIMAL(18,2)) AS Rpr_order_consumption
    FROM cte_Main m
    JOIN cte_Recursive r ON m.Parts = r.Parts AND m.rn = r.rn + 1
)
-- 3. 输出最终结果
SELECT 
    Theater, 
    Parts, 
    Open_Orders, 
    Non_Restricted_Empty_Bins,
    Rpr_order_consumption
FROM cte_Recursive
ORDER BY Parts, rn;

关键说明 #

• 递归CTE分为两部分：锚点成员（每组第一行，直接计算初始值）和递归成员（关联上一行的结果，计算当前行的值）
• 用ROW_NUMBER()给每组内的行编号，确保递归时能按顺序关联前一行
• 如果你的Open_Orders或Non_Restricted_Empty_Bins是整数，CAST可以根据实际需求调整精度，或者直接用整数类型

替代方案（部分数据库支持） #

如果你的数据库支持LAG()窗口函数结合SUM()的条件累加（比如PostgreSQL、SQL Server 2012+），也可以用以下方式，逻辑等价于逐行递归：

WITH cte_Main AS (
    SELECT 
        Theater, 
        Parts, 
        Open_Orders, 
        Non_Restricted_Empty_Bins,
        -- 获取每组的第一个Open_Orders值
        FIRST_VALUE(Open_Orders) OVER (PARTITION BY Parts ORDER BY Theater) AS first_open_order,
        -- 计算每组内从第一行到当前行的Non_Restricted_Empty_Bins累计和
        SUM(Non_Restricted_Empty_Bins) OVER (PARTITION BY Parts ORDER BY Theater ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_empty_bins
    FROM Apps.Fill_Rate
)
SELECT 
    Theater, 
    Parts, 
    Open_Orders, 
    Non_Restricted_Empty_Bins,
    first_open_order - cum_empty_bins AS Rpr_order_consumption
FROM cte_Main
ORDER BY Parts, Theater;

内容的提问来源于stack exchange，提问作者Devaraj Mani Maran