如何在SQL中实现基于同列的递归滚动平均价格计算

现有如下数据表：

第一行是初始库存，后续行是改变库存的交易记录，需按ID升序，根据以下规则计算滚动平均价格：

1. 若 Transaction = NULL（初始行），则 Average = 90;
2. 若 Transaction = 'Sale'，则 Average = 前一行计算得到的平均值;
3. 若 Transaction = 'Purchase'，则 Average = ((Inventory - Amount) * 前一行平均值 + Amount * Price) / Inventory

预期结果表：

计算过程：

• ID 1：初始值90
• ID 2：复制前一行平均值90
• ID 3：((12-2)90 + 2102)/12 = 92
• ID 4：复制前一行平均值92

用户尝试两种方法均失败：

1. 直接用窗口函数lag()计算新列：

Select *, 
       case when [transaction] is null then Average
            when [transaction]  = 'Sale' then lag(Average) over (order by ID)
            when [transaction] = 'Purchase' 
                 then (((Inventory - Amount) * lag(Average) over (order by ID))
                      + (Amount * Price)) / Inventory
        end as Average_f
from table

结果不符合预期，后续行出现NULL值，因为窗口函数lag()只能引用原始表中的值，无法获取计算过程中更新后的平均值。

2. 使用UPDATE语句：

update table
    set average = case when [transaction] is null then Average
             when [transaction] = 'Purchase' 
                 then (((Inventory - Amount) * (select lag(Average) over (order by ID)
                                                from table t 
                                                where t.ID = table.ID))
                      + (Amount * Price)) / Inventory
             when [transaction]  = 'Sale' then (select lag(Average) over (order by ID)
                                                from table t 
                                                where t.ID = table.ID)
             end

同样失败，因为UPDATE语句默认是基于原始数据批量更新，无法逐行依赖前一行的更新结果。

请问在SQL中如何实现这种逐行依赖前一行计算结果的滚动平均？

这种依赖前一行计算结果的场景，适合使用**递归CTE（公共表表达式）**来实现，递归CTE可以逐行遍历数据，并引用上一行的计算结果。

实现代码 #

WITH RecursiveAvg AS (
    -- 锚点成员：获取初始行
    SELECT 
        ID,
        Transaction,
        Amount,
        Inventory,
        Price,
        CAST(90 AS DECIMAL(10,2)) AS Average
    FROM YourTableName
    WHERE ID = 1

    UNION ALL

    -- 递归成员：逐行计算后续行的Average
    SELECT 
        t.ID,
        t.Transaction,
        t.Amount,
        t.Inventory,
        t.Price,
        CASE
            WHEN t.Transaction = 'Sale' THEN ra.Average
            WHEN t.Transaction = 'Purchase' THEN 
                CAST( ((t.Inventory - t.Amount) * ra.Average + t.Amount * t.Price) / t.Inventory AS DECIMAL(10,2) )
        END AS Average
    FROM YourTableName t
    INNER JOIN RecursiveAvg ra ON t.ID = ra.ID + 1
)
SELECT * FROM RecursiveAvg ORDER BY ID;

代码说明 #

1. 锚点成员：首先获取ID=1的初始行，直接设置Average为90，作为递归的起点。
2. 递归成员：通过INNER JOIN关联上一行的结果（ra.ID + 1 = t.ID），根据当前行的交易类型计算Average：
   • 若为Sale，直接继承上一行的Average；
   • 若为Purchase，按照给定公式计算新的Average，并通过CAST确保数值精度。
3. 最后查询递归CTE的结果，按ID排序即可得到预期的滚动平均价格。

注意事项 #

• 将YourTableName替换为你的实际表名；
• 根据实际数据精度需求调整DECIMAL(10,2)的参数；
• 若ID不连续，可先通过ROW_NUMBER() OVER(ORDER BY ID)生成连续行号，再基于行号进行递归。

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