SQL使用INNER JOIN实现买卖撮合时单买入单仅匹配首个符合条件卖出单

问题根源 #

你当前的SQL仅完成了基础条件的INNER JOIN，没有对候选匹配集做双向去重排名，导致单个买入单会关联所有满足量、价约束的卖出单，出现一个bid对应多个offer的冗余结果，无法满足一对一撮合要求。

实现逻辑 #

完全匹配你提出的撮合规则：

• 先筛选所有满足基础撮合条件的组合：买入量与卖出量相等、买入价≥卖出价
• 对每笔买入单关联的候选卖出单，按卖出价从低到高排序，仅保留排名第1的匹配（即同一笔买入单优先匹配价格最低的卖出单）
• 对每笔卖出单关联的候选买入单，按买入价从高到低排序，仅保留排名第1的匹配（即同个卖出单被多笔买入单竞争时，出价最高的买入单获得资格）
• 最终筛选同时满足双向排名第1的组合，就是符合规则的一对一撮合结果

可直接运行的SQL #

WITH valid_matches AS (
    -- 筛选满足基础量、价条件的撮合候选
    SELECT
        b.bid_id,
        b.user_id AS bidder_user_id,
        b.bid_volume,
        b.bid_price,
        o.offer_id,
        o.user_id AS dealer_user_id,
        o.offer_volume,
        o.offer_price
    FROM bids b
    INNER JOIN offers o
        ON b.bid_volume = o.offer_volume
        AND b.bid_price >= o.offer_price
),
bid_ranked AS (
    -- 为每笔买入单选匹配成本最低（卖价最低）的卖出单
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY bid_id 
            ORDER BY offer_price ASC, offer_id ASC
        ) AS bid_optimal_rank
    FROM valid_matches
),
offer_ranked AS (
    -- 为每笔卖出单选出价最高的买入单
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY offer_id 
            ORDER BY bid_price DESC, bid_id DESC
        ) AS offer_optimal_rank
    FROM bid_ranked
)
-- 输出最终一对一匹配结果
SELECT
    bidder_user_id,
    dealer_user_id,
    bid_volume,
    offer_volume,
    bid_price,
    offer_price
FROM offer_ranked
WHERE bid_optimal_rank = 1 
  AND offer_optimal_rank = 1
ORDER BY bid_price DESC;

结果验证 #

用你提供的示例数据执行上述SQL，输出结果和你期望的完全一致：

• 用户1对应的买入单仅匹配卖价200的卖出单（来自用户8），不会重复匹配同量、卖价300的卖出单（来自用户10）
• 所有匹配项中每笔买入单、卖出单仅出现一次，完全满足一对一撮合要求

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