如何在SQL中创建依赖自身前序值的averagePrice计算列

问题背景 #

现有交易数据表，结构及数据如下：

需按照以下规则计算平均价格：

• 当volume > 0（买入交易）：averagePrice = (price * volume + prevNetVolume * prevAveragePrice)/(prevNetVolume + volume)
• 当volume <= 0（卖出交易）：averagePrice = prevAveragePrice

其中prevAveragePrice为上一行计算得到的平均价格，原代码使用WHILE循环+LAG函数时，无法获取前一行更新后的averagePrice值。

问题原因 #

LAG函数基于查询执行时的表数据快照工作，而非循环中实时更新后的行数据。在WHILE循环的UPDATE语句中，LAG读取的是初始赋值为0的averagePrice列，并非上一行刚更新的结果，导致计算逻辑失效。

解决方案 #

改用变量保存前一行的平均价格，逐行更新时直接引用变量值，更新后再将当前行的平均价格赋值给变量。修正后的代码如下：

IF OBJECT_ID('tempdb..#Transaction2') IS NOT NULL
DROP TABLE #Transaction2

SELECT
    *
    , 0 AS averagePrice
INTO #Transaction2
FROM Table1

DECLARE @count INT = (SELECT COUNT(*) FROM #Transaction2)
DECLARE @counter INT = 1
DECLARE @prevAvg DECIMAL(18,4) = 0 -- 存储前一行的平均价格

WHILE @counter <= @count
BEGIN
    DECLARE @currentPrice DECIMAL(18,4)
    DECLARE @currentVolume INT
    DECLARE @currentPrevNet INT
    DECLARE @newAvg DECIMAL(18,4)

    -- 获取当前行的交易数据
    SELECT 
        @currentPrice = price,
        @currentVolume = volume,
        @currentPrevNet = prevNetVolume
    FROM #Transaction2
    WHERE RowNumber = @counter

    -- 计算当前行的平均价格
    IF @currentVolume > 0
        SET @newAvg = (@currentPrice * @currentVolume + @currentPrevNet * @prevAvg) / (@currentPrevNet + @currentVolume)
    ELSE
        SET @newAvg = @prevAvg

    -- 更新当前行的averagePrice
    UPDATE #Transaction2
    SET averagePrice = @newAvg
    WHERE RowNumber = @counter

    -- 更新变量为当前行的平均价格，供下一行计算使用
    SET @prevAvg = @newAvg
    SET @counter += 1
END

SELECT * FROM #Transaction2

代码说明 #

1. 新增@prevAvg变量，专门存储上一行计算得到的平均价格，初始值设为0（对应第一行的前置平均价格）。
2. 循环中先读取当前行的交易数据，再根据规则计算新的平均价格，避免直接依赖表的实时数据。
3. 更新当前行后，将计算结果赋值给@prevAvg，确保下一行能获取到正确的前置值。

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