如何在Microsoft SQL Server中按条件计算NetMF累计求和

现有股票数据 #

NetMF计算规则 #

• 当datetime为09:15:00时，NetMF直接取MF的值；
• 从09:16:00开始，遵循以下逻辑：
  • 当前AVG_Price > 前一分钟AVG_Price：当前NetMF = 前一分钟NetMF + 当前MF；
  • 当前AVG_Price < 前一分钟AVG_Price：当前NetMF = 前一分钟NetMF - 当前MF；
  • 当前AVG_Price = 前一分钟AVG_Price 且 前一分钟NetMF < 0：当前NetMF = 前一分钟NetMF - 当前MF；
  • 当前AVG_Price = 前一分钟AVG_Price 且 前一分钟NetMF >= 0：当前NetMF = 前一分钟NetMF + 当前MF；
• 规则需支持多股票代码、多时间维度的持续计算。

现有SQL问题 #

我用以下SQL代码尝试更新NetMF，但仅能返回前1-2行的正确值，后续行无法计算：

WITH v_table_name AS
    (
        SELECT stock_code,[datetime], [AVG_Price],[MF], CASE WHEN cast(datetime as time) = '09:15:00.000' and close_price >= open_price THEN [MF] 
                       WHEN cast(datetime as time) = '09:15:00.000' and close_price < open_price THEN -1*[MF] 
                       WHEN Avg_Price > LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) + [MF]
                       WHEN Avg_Price < LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) - [MF]
                       WHEN Avg_Price = LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) and (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) < 0 THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) - [MF]
                       WHEN Avg_Price = LAG(Avg_Price,1,-1) over (Partition by stock_code order by datetime asc) and (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) > 0 THEN (LAG([Net MF],1,-1) over (Partition by stock_code order by datetime asc)) + [MF]
                       END as [Net MF] from Equity
    ) 
    UPDATE [Equity] set Equity.[Net MF] = v_table_name.[Net MF]
    FROM v_table_name
    WHERE Equity.stock_code = v_table_name.stock_code
    and Equity.datetime = v_table_name.datetime

问题分析与解决方案 #

问题根源 #

原代码使用LAG([Net MF])时，调用的是原表中未更新的旧值，而非CTE中计算出的前一行新值。因为CTE是基于原表数据生成的，LAG无法引用CTE内同列的计算结果，导致后续行无法基于前一行的正确NetMF值递推，只能计算前1-2行。

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

递归CTE可以逐行递推计算NetMF，每一行都基于前一行的计算结果，完美适配这种依赖历史值的场景。以下是适配多股票、多时间维度的代码：

WITH RecursiveNetMF AS (
    -- 锚点成员：取09:15:00的初始数据，NetMF直接等于MF
    SELECT 
        stock_code,
        [datetime],
        AVG_Price,
        MF,
        CAST(MF AS BIGINT) AS [Net MF]
    FROM Equity
    WHERE CAST([datetime] AS TIME) = '09:15:00.000'

    UNION ALL

    -- 递归成员：逐行计算后续时间的NetMF
    SELECT 
        e.stock_code,
        e.[datetime],
        e.AVG_Price,
        e.MF,
        CASE
            -- 价格上涨：累加MF
            WHEN e.AVG_Price > r.AVG_Price THEN r.[Net MF] + e.MF
            -- 价格下跌：减去MF
            WHEN e.AVG_Price < r.AVG_Price THEN r.[Net MF] - e.MF
            -- 价格持平，根据前一行NetMF符号判断
            ELSE 
                CASE 
                    WHEN r.[Net MF] < 0 THEN r.[Net MF] - e.MF
                    ELSE r.[Net MF] + e.MF
                END
        END AS [Net MF]
    FROM Equity e
    INNER JOIN RecursiveNetMF r 
        ON e.stock_code = r.stock_code
        -- 关联前一分钟的数据
        AND DATEADD(MINUTE, 1, r.[datetime]) = e.[datetime]
)
-- 更新原表的NetMF字段
UPDATE e
SET e.[Net MF] = r.[Net MF]
FROM Equity e
INNER JOIN RecursiveNetMF r 
    ON e.stock_code = r.stock_code
    AND e.[datetime] = r.[datetime];

代码说明 #

1. 锚点成员：筛选出每个股票的09:15:00数据，初始化NetMF为MF的值；
2. 递归成员：通过DATEADD关联当前行与前一分钟的行，根据价格对比规则和前一行NetMF的值，计算当前行的NetMF；
3. 更新操作：将递归CTE计算出的正确NetMF值更新回原表。

注意事项 #

• 确保股票的时间序列是连续的每分钟数据，若存在缺漏，可先补全数据或调整关联条件（如使用ROW_NUMBER()按时间排序后关联行号）；
• 若MF或NetMF数值较大，建议使用BIGINT类型避免溢出；
• 原代码中提到的close_price和open_price未在问题描述中出现，若初始规则需要基于这些字段调整，可修改锚点成员的逻辑。

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