如何查询Instacart数据库中同订单最常共购的商品对？

你的原SQL存在几个关键问题，导致无法得到正确的商品对共现次数：

• 未排除商品与自身的配对（比如p1.product_id = p2.product_id的无效组合）
• 没有避免重复商品对的统计（比如(741,742)和(742,741)被算作两个不同组合，但实际是同一对）
• 未处理订单内同一商品重复出现的情况（比如样例中order100里的两个741，会导致该订单内的商品对被重复计数）
• 计数逻辑错误：统计的是配对的行数，而非商品共同出现的订单数

正确的查询语句 #

先对每个订单的商品去重，避免重复条目干扰统计，再通过自连接获取唯一商品对并计算共现次数：

-- 生成去重后的订单-商品数据集
WITH unique_order_products AS (
    SELECT DISTINCT order_id, product_id
    FROM public.order_products
)
SELECT 
    p1.product_id AS product_a,
    p2.product_id AS product_b,
    COUNT(DISTINCT p1.order_id) AS co_occurrence_count
FROM unique_order_products p1
JOIN unique_order_products p2 
    ON p1.order_id = p2.order_id 
    AND p1.product_id < p2.product_id  -- 确保商品对唯一，避免重复统计
GROUP BY p1.product_id, p2.product_id
ORDER BY co_occurrence_count DESC
LIMIT 1;  -- 可选：仅返回出现次数最多的商品对

语句说明 #

1. unique_order_products 临时数据集：通过DISTINCT去除每个订单内重复的商品条目，确保每个订单-商品组合只统计一次。
2. 自连接条件：p1.product_id < p2.product_id既避免了商品与自身的无效配对，又保证每个商品对仅被统计一次（比如只保留(741,742)，不会出现反向的(742,741)）。
3. 计数逻辑：COUNT(DISTINCT p1.order_id)统计的是两个商品共同出现的订单数量，这才是真正的共购次数。

样例数据的预期结果 #

运行上述语句后，会得到符合你预期的结果：

如果你的业务场景中order_id + product_id本身就是唯一键（即同一订单不会重复出现同一商品），可以去掉CTE中的DISTINCT，直接使用原表查询。

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