如何在TSQL中使用窗口函数实现On Balance Volume(OBV)计算

你需要的迭代累计逻辑在TSQL中可以实现，以下是两种常用方案：

方案1：窗口函数累加实现（推荐，SQL Server 2012+支持，性能最优） #

核心思路是先给每行计算Volume的加减系数，再通过开窗累加得到最终的c值，无需递归，适合大数据量场景：

-- 测试数据，实际使用替换为你的业务表
WITH SourceData AS (
    SELECT a = 1, b = '2020-01', Volume = 10 UNION ALL
    SELECT a = 2, b = '2020-02', Volume = 20 UNION ALL
    SELECT a = 5, b = '2020-03', Volume = 40 UNION ALL
    SELECT a = 3, b = '2020-04', Volume = 30 UNION ALL
    SELECT a = 1, b = '2020-05', Volume = 10
),
-- 计算每行的加减系数
VolumeCoeff AS (
    SELECT *,
        CASE
            -- 首行系数为0，对应初始值0
            WHEN LAG(a,1) OVER(ORDER BY b ASC) IS NULL THEN 0
            -- 当期a大于上期，系数为+1
            WHEN LAG(a,1) OVER(ORDER BY b ASC) < a THEN 1
            -- 当期a小于上期，系数为-1
            WHEN LAG(a,1) OVER(ORDER BY b ASC) > a THEN -1
            -- 若a相等可自行调整规则，默认系数为0（不累加）
            ELSE 0
        END AS coeff
    FROM SourceData
)
SELECT a, b, Volume,
    -- 按时间顺序累加系数*Volume得到c值
    SUM(coeff * Volume) OVER(ORDER BY b ASC ROWS UNBOUNDED PRECEDING) AS c
FROM VolumeCoeff
ORDER BY b ASC

运行结果和你给出的预期完全一致。

方案2：递归CTE实现（兼容所有TSQL版本） #

如果你的SQL Server版本低于2012，可以用递归CTE模拟逐行迭代的逻辑，和你提到的其他语言用变量存储上一行值的逻辑一致：

-- 测试数据，实际使用替换为你的业务表
WITH SourceData AS (
    SELECT a = 1, b = '2020-01', Volume = 10 UNION ALL
    SELECT a = 2, b = '2020-02', Volume = 20 UNION ALL
    SELECT a = 5, b = '2020-03', Volume = 40 UNION ALL
    SELECT a = 3, b = '2020-04', Volume = 30 UNION ALL
    SELECT a = 1, b = '2020-05', Volume = 10
),
-- 按时间排序生成行号，方便定位上一行
OrderedData AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY b ASC) AS rn
    FROM SourceData
),
-- 递归计算c值
OBV_Calc AS (
    -- 锚点：首行c初始值为0
    SELECT a, b, Volume, rn, c = 0
    FROM OrderedData
    WHERE rn = 1
    UNION ALL
    -- 递归逐行计算
    SELECT curr.a, curr.b, curr.Volume, curr.rn,
        CASE
            WHEN prev.a < curr.a THEN prev.c + curr.Volume
            WHEN prev.a > curr.a THEN prev.c - curr.Volume
            ELSE prev.c
        END AS c
    FROM OBV_Calc prev
    INNER JOIN OrderedData curr ON curr.rn = prev.rn + 1
)
SELECT a, b, Volume, c
FROM OBV_Calc
ORDER BY rn
-- 数据量超过100行时需打开递归深度限制，0为不限制
-- OPTION (MAXRECURSION 0)

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