如何使用GROUP BY实现SQL中bid_id与offer_id的全局唯一匹配

核心问题本质 #

你当前遇到的交叉匹配问题，本质是普通内连接仅能筛选出满足基础交易条件的bid-offer候选组合，但无法实现bid、offer维度的全局一对一唯一约束：

• 仅按bid_id分组：每个出价固定绑定1个报价，但多个出价会重复匹配同一个报价
• 同时按bid_id+offer_id分组：本质是把所有交叉组合全部保留，完全不具备去重效果
  这类双边唯一匹配的需求，靠简单的GROUP BY根本解决不了，需要通过双向排名的匹配逻辑完成筛选。

实现逻辑 #

这套逻辑完全匹配你给出的测试用例筛选规则，核心思路是：

1. 先跑通原有连接逻辑，拿到所有满足基础交易条件的候选bid-offer对
2. 对每个bid，按照业务撮合优先级（比如报价价格从低到高、报价ID从小到大）给它能匹配的所有offer排顺序，生成bid_rank字段
3. 对每个offer，按照业务撮合优先级（比如出价价格从高到低、出价ID从小到大）给它能匹配的所有bid排顺序，生成offer_rank字段
4. 只保留bid_rank = offer_rank的候选对，此时每个bid、每个offer在结果集里全局唯一，不会出现重复交叉匹配

拿你的测试用例算出来的排名结果：

bid_id	offer_id	bid_rank	offer_rank	最终筛选结果
8	11	1	1	保留
8	12	2	1	排除
9	11	1	2	排除
9	12	2	2	保留

和你期望的结果完全对齐。

调整后的完整可运行SQL #

/* Insert statement to insert matched bids/offers */ 
INSERT INTO Marketplace_Matched(
    consumer_id,        /* Bidder */
    producer_id,        /* Offer */
    bid_id,             /* bid id */
    offer_id,           /* offer id */ 
    agreed_ta_tokens,   /* Volume */
    agreed_tb_tokens,   /* Price */
    market_time         /* Market Time */
)
WITH candidate_matches AS (
    -- 第一步：生成所有满足基础交易条件的候选匹配对，修复原SQL缺失的逗号问题
    SELECT  
        bids.user_id AS bidder_user_id,
        offers.user_id AS dealer_user_id,
        bids.bid_id AS bid_id,
        offers.offer_id AS offer_id,
        bids.bid_ta_tokens AS bid_volume,
        bids.bid_tb_tokens AS bid_price,
        bids._bid_market_time AS bid_market_time,
        offers.rn AS row_offers
    FROM  
        Marketplace_Bids bids
    INNER JOIN (
        SELECT *, ROW_NUMBER() 
            OVER (
                PARTITION BY offer_ta_tokens 
                ORDER BY offer_tb_tokens ASC -- 同交易量下，卖方报价越低越优先
            ) rn 
        FROM Marketplace_Offers
    ) offers
    ON
        bids.bid_ta_tokens = offers.offer_ta_tokens AND
        bids.bid_tb_tokens >= offers.offer_tb_tokens AND
        bids.bid_market_time = offers.offer_market_time
),
ranked_matches AS (
    -- 第二步：生成双向匹配排名
    SELECT 
        *,
        -- 对每个bid，按优先级排序可匹配的offer：同bid下报价价格越低、offer_id越小，排名越靠前
        ROW_NUMBER() OVER (
            PARTITION BY bid_id 
            ORDER BY offer_tb_tokens ASC, offer_id ASC
        ) AS bid_rank,
        -- 对每个offer，按优先级排序可匹配的bid：同offer下出价价格越高、bid_id越小，排名越靠前
        ROW_NUMBER() OVER (
            PARTITION BY offer_id 
            ORDER BY bid_tb_tokens DESC, bid_id ASC
        ) AS offer_rank
    FROM candidate_matches
)
-- 第三步：筛选双向排名相等的唯一匹配对
SELECT
    bidder_user_id,
    dealer_user_id,
    bid_id,
    offer_id,
    bid_volume,
    bid_price,
    bid_market_time
FROM ranked_matches
WHERE bid_rank = offer_rank;

关键注意点 #

• 窗口函数里的排序规则可以根据实际撮合优先级调整：比如如果要优先匹配最早挂单的bid/offer，直接在ORDER BY子句里加上对应的挂单时间字段就行
• 整个流程不需要额外加GROUP BY，去重逻辑完全靠窗口函数的双向排名实现
• 你原来的SQL存在两处可执行的bug：一是bids._bid_market_time AS bid_market_time这行后面漏了逗号，直接接了offers.rn AS row_offers会触发语法报错；二是SELECT列表里多了row_offers字段，但Marketplace_Matched表的插入列里没有对应字段，会触发列数不匹配的插入错误，以上问题均已在调整后的代码中修复

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