SQL Server中基于股票交易规则计算PurchaseCost列的实现方法

实现思路 #

要实现逐笔滚动计算持仓成本，核心是需要按交易顺序逐行递推每只股票的累计持仓和当前成本，SQL Server中可以用递归CTE（公用表表达式）实现这类逐行依赖的计算逻辑，步骤如下：

• 按标的（Instrument）分组，对每只股票的交易按TransactionID升序排序，生成组内行号
• 递归锚点取每只股票的第一笔交易，计算初始持仓和初始成本
• 逐行递归处理后续交易，按买卖类型分别计算当前成本和持仓：
  • 买入：当前成本 = 上一笔成本 + 本次TransactionSum，持仓 = 上一笔持仓 + 本次买入股数
  • 卖出：当前成本 = 上一笔成本 * (1 - 本次卖出股数 / 上一笔持仓)，持仓 = 上一笔持仓 - 本次卖出股数
• 最后将递归计算得到的成本更新回原表的PurchaseCost列

完整实现代码 #

-- 1. 递归CTE计算每笔交易的成本和持仓
WITH RankedTransactions AS (
    -- 给每个标的的交易按交易ID排序生成行号
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Instrument ORDER BY TransactionID) AS rn
    FROM StockTransactions
),
RecursiveCost AS (
    -- 锚点：每只股票第一笔交易
    SELECT 
        TransactionID,
        Instrument,
        TransactionType,
        Units,
        TransactionSum,
        rn,
        CAST(TransactionSum AS FLOAT) AS CurrentCost,
        CAST(Units AS INT) AS CurrentHolding
    FROM RankedTransactions
    WHERE rn = 1

    UNION ALL

    -- 递归部分：逐行计算后续交易
    SELECT 
        rt.TransactionID,
        rt.Instrument,
        rt.TransactionType,
        rt.Units,
        rt.TransactionSum,
        rt.rn,
        -- 按交易类型计算当前成本
        CAST(
            CASE 
                WHEN rt.TransactionType = 'Buy' THEN rc.CurrentCost + rt.TransactionSum
                WHEN rt.TransactionType = 'Sell' THEN rc.CurrentCost * (1 - 1.0 * rt.Units / rc.CurrentHolding)
                ELSE rc.CurrentCost
            END AS FLOAT
        ) AS CurrentCost,
        -- 计算当前持仓
        CAST(
            CASE 
                WHEN rt.TransactionType = 'Buy' THEN rc.CurrentHolding + rt.Units
                WHEN rt.TransactionType = 'Sell' THEN rc.CurrentHolding - rt.Units
                ELSE rc.CurrentHolding
            END AS INT
        ) AS CurrentHolding
    FROM RecursiveCost rc
    JOIN RankedTransactions rt 
        ON rc.Instrument = rt.Instrument AND rc.rn + 1 = rt.rn
)
-- 2. 更新原表的PurchaseCost列
UPDATE st
SET st.PurchaseCost = rc.CurrentCost
FROM StockTransactions st
JOIN RecursiveCost rc ON st.TransactionID = rc.TransactionID

计算结果验证 #

执行以上更新后，查询SELECT TransactionID, Instrument, PurchaseCost FROM StockTransactions可以得到结果：

和需求描述的计算结果完全一致。

注意事项 #

• 如果实际业务中存在撤单、分红、拆股等场景，需要对应调整递归逻辑中的计算规则
• 浮点型计算可能存在精度误差，建议生产环境将成本相关字段类型改为DECIMAL(18,4)这类精确数值类型
• 交易量大的场景可以考虑用游标或者窗口函数累加（SQL Server 2022及以上支持WINDOW子句优化递归性能）

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