SQL Server查询无重复最常同时购买Top2商品对的实现方法

核心思路是通过统一商品ID的排序规则去重重复商品对：始终把较小的商品ID放在前位，较大的放在后位，避免(A,B)和(B,A)被识别为两组不同的商品对。

写法1（适用于表中已预存每对商品共同购买次数的场景） #

如果你的product表中times_bought_together已经是每对商品的聚合结果，仅存在正反双向的重复记录，可直接用以下写法：

SELECT TOP 2
    CASE WHEN Product_Id < bought_with_Product_Id THEN Product_Id ELSE bought_with_Product_Id END AS product1,
    CASE WHEN Product_Id < bought_with_Product_Id THEN bought_with_Product_Id ELSE Product_Id END AS product2,
    MAX(times_bought_together) AS times_bought_together
FROM product
WHERE Product_Id <> bought_with_Product_Id
GROUP BY 
    CASE WHEN Product_Id < bought_with_Product_Id THEN Product_Id ELSE bought_with_Product_Id END,
    CASE WHEN Product_Id < bought_with_Product_Id THEN bought_with_Product_Id ELSE Product_Id END
ORDER BY times_bought_together DESC

写法2（适用于需要从订单明细重新统计共同购买次数的场景） #

如果你的product表实际存储的是单订单的商品明细（而非预聚合的关联数据），可以先关联统计共同购买次数再去重：

WITH purchase_pairs AS (
    SELECT 
        a.product_id AS product1,
        b.product_id AS product2,
        COUNT(*) AS times_bought_together
    FROM product a
    JOIN product b 
        ON a.order_id = b.order_id 
        AND a.product_id < b.product_id -- 关联阶段直接避免生成重复对
    GROUP BY a.product_id, b.product_id
)
SELECT TOP 2 *
FROM purchase_pairs
ORDER BY times_bought_together DESC

原有写法的问题说明 #

• 第一种写法未添加ORDER BY times_bought_together DESC排序规则，TOP 2返回的是表存储顺序的前两条数据，并非购买次数最高的两组商品
• 第二种写法仅匹配和最高购买次数等值的记录，若最高次数仅对应1组商品，只能拿到1组结果，同时也没有处理双向重复的商品对问题

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