如何使用匹配重叠块（JOIN替代UNION）在SQL中实现FIFO买卖匹配

完全可以跳过统一交易表的准备环节，利用累计量区间重叠的逻辑直接关联买卖记录实现FIFO匹配，这种方法能避免UNION操作带来的性能损耗，尤其适合已有独立买卖表的场景。

核心思路 #

FIFO的本质是「按时间顺序，先买入的份额优先被先卖出的订单消耗」。我们可以给每笔买卖记录计算累计量区间：

• 对买入记录，按时间排序计算累计买入量，每笔买入对应一个[买入累计起始量, 买入累计结束量)的区间
• 对卖出记录，同理计算累计卖出量的区间
• 两个区间重叠的部分，就是该笔买入和卖出在FIFO规则下匹配的份额

具体实现示例 #

假设我们有两张独立表：

• buys：存储买入记录，字段为buy_id, amount, trade_time
• sells：存储卖出记录，字段为sell_id, amount, trade_time

步骤1：计算买卖记录的累计量区间

用窗口函数分别计算两张表的累计量区间：

WITH buy_intervals AS (
    SELECT
        buy_id,
        amount AS buy_amount,
        trade_time AS buy_time,
        -- 累计起始量：当前记录之前的所有买入总量
        SUM(amount) OVER (ORDER BY trade_time, buy_id) - amount AS buy_start,
        -- 累计结束量：当前记录及之前的所有买入总量
        SUM(amount) OVER (ORDER BY trade_time, buy_id) AS buy_end
    FROM buys
),
sell_intervals AS (
    SELECT
        sell_id,
        amount AS sell_amount,
        trade_time AS sell_time,
        SUM(amount) OVER (ORDER BY trade_time, sell_id) - amount AS sell_start,
        SUM(amount) OVER (ORDER BY trade_time, sell_id) AS sell_end
    FROM sells
)

注：排序时加入buy_id/sell_id是为了处理同一时间多笔交易的顺序确定性，避免窗口函数排序歧义。

步骤2：关联重叠区间并计算匹配量

通过区间重叠条件关联两张表，计算每对买卖记录的实际匹配份额：

SELECT
    bi.buy_id,
    si.sell_id,
    -- 重叠区间的长度即为匹配量
    LEAST(bi.buy_end, si.sell_end) - GREATEST(bi.buy_start, si.sell_start) AS matched_amount,
    bi.buy_time,
    si.sell_time
FROM buy_intervals bi
JOIN sell_intervals si
    -- 确保买入时间不晚于卖出时间（符合FIFO时间顺序）
    ON bi.buy_time <= si.sell_time
    -- 区间重叠判断：买入起始 < 卖出结束，且卖出起始 < 买入结束
    AND bi.buy_start < si.sell_end
    AND si.sell_start < bi.buy_end
ORDER BY bi.buy_time, si.sell_time;

性能优势 #

• 无需UNION合并两张表，避免了合并后的数据排序、索引重建等额外开销
• 窗口函数的计算是线性复杂度（O(n)），只要buys和sells表在trade_time字段上有索引，窗口函数的执行效率极高
• 关联操作基于数值区间和时间条件，相比UNION后的全量排序匹配，过滤更精准

注意事项 #

• 如果存在大量同一时间的交易，必须补充唯一排序键（如交易ID）来保证FIFO顺序的一致性
• 若需要计算剩余未匹配的买入/卖出量，可以基于匹配结果反向统计原记录的剩余金额
• 该方法适用于买卖记录分开存储的场景，无需预先合并为统一交易表

内容的提问来源于stack exchange，提问作者Serkan Ekşioğlu