如何在SQL中实现买卖交易的FIFO匹配

现有一张跟踪股票交易记录的SQL表，包含以下字段：

• date：交易日期
• id：交易唯一标识符
• ticker：股票代码
• txn_type：交易类型（'Buy' 或 'Sell'）
• qty：股份数量
• price_per_share：每股价格

示例表数据 #

预期输出 #

需要按**FIFO（先进先出）**规则，将每笔卖出的股份匹配到对应的买入记录，输出结果如下：

解决方案SQL #

以下是适用于支持窗口函数（如PostgreSQL、MySQL 8.0+、SQL Server等）的SQL查询：

WITH buy_transactions AS (
    SELECT
        date,
        id AS buy_id,
        ticker,
        qty,
        price_per_share,
        -- 计算每笔买入的累计可用数量（FIFO顺序）
        SUM(qty) OVER (PARTITION BY ticker ORDER BY date, id) AS cumulative_buy_qty,
        SUM(qty) OVER (PARTITION BY ticker ORDER BY date, id) - qty AS prev_cumulative_buy_qty
    FROM transactions
    WHERE txn_type = 'Buy'
),
sell_transactions AS (
    SELECT
        date,
        id AS sell_id,
        ticker,
        qty AS sell_qty,
        -- 计算每笔卖出的累计需要匹配数量
        SUM(qty) OVER (PARTITION BY ticker ORDER BY date, id) AS cumulative_sell_qty,
        SUM(qty) OVER (PARTITION BY ticker ORDER BY date, id) - qty AS prev_cumulative_sell_qty
    FROM transactions
    WHERE txn_type = 'Sell'
)
SELECT
    s.date,
    s.sell_id AS id,
    s.ticker,
    'Sell' AS txn_type,
    -- 计算当前卖出匹配到该买入的实际数量
    LEAST(
        s.cumulative_sell_qty, b.cumulative_buy_qty
    ) - GREATEST(
        s.prev_cumulative_sell_qty, b.prev_cumulative_buy_qty
    ) AS sell_qty,
    b.buy_id,
    b.price_per_share
FROM sell_transactions s
JOIN buy_transactions b 
    ON s.ticker = b.ticker
    -- 匹配条件：卖出的累计区间与买入的累计区间有重叠
    AND s.prev_cumulative_sell_qty < b.cumulative_buy_qty
    AND s.cumulative_sell_qty > b.prev_cumulative_buy_qty
WHERE 
    -- 过滤掉匹配数量为0的无效行
    LEAST(s.cumulative_sell_qty, b.cumulative_buy_qty) - GREATEST(s.prev_cumulative_sell_qty, b.prev_cumulative_buy_qty) > 0
ORDER BY s.date, s.sell_id, b.buy_id;

逻辑说明 #

1. buy_transactions CTE：

   • 筛选所有买入交易，按股票代码分组、交易日期+ID排序，计算每笔买入的累计持仓量，保证FIFO顺序
   • prev_cumulative_buy_qty 表示当前买入之前的累计持仓量，用于标记该笔买入的数量区间

2. sell_transactions CTE：

   • 筛选所有卖出交易，按相同规则计算每笔卖出的累计卖出量
   • prev_cumulative_sell_qty 表示当前卖出之前的累计卖出量，标记该笔卖出的数量区间

3. JOIN与匹配计算：

   • 按股票代码关联卖出和买入记录，筛选出数量区间有重叠的有效匹配
   • 通过LEAST和GREATEST计算重叠部分的数量，即该笔卖出匹配到对应买入的股份数
   • 过滤掉匹配数量为0的无效行，最终按交易顺序排序输出

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