如何用MySQL查询获取各客户的相似客户所购买的产品

表结构 #

CREATE TABLE orders (customer VARCHAR(16) NOT NULL, product_id INT NOT NULL);

定义 #

相似客户：至少购买两款相同产品的客户

需求 #

查询获取每个客户的相似客户所购买的产品，需排除该客户自身已购买的产品。

输入数据 #

INSERT INTO orders VALUES 
  ('A', 1), ('A', 2), ('A', 3),
  ('B', 1), ('B', 2), ('B', 4), 
  ('C', 1), ('C', 3), ('C', 4), ('C', 5);

理想输出 #

现有未完成代码 #

WITH common AS (
SELECT o1.customer AS cust_1, o2.customer AS cust_2, o1.product_id AS prod_id,
       COUNT(*) OVER (PARTITION BY o1.customer, o2.customer) AS same_purchased
FROM orders o1 JOIN orders o2
ON (o1.customer < o2.customer AND o1.product_id = o2.product_id))
SELECT cust_1, cust_2, prod_id
FROM common WHERE same_purchased >= 2

完整解决方案 #

以下是实现需求的完整SQL：

WITH similar_customers AS (
    -- 筛选出所有满足条件的相似客户对（单向，避免重复）
    SELECT o1.customer AS cust1, o2.customer AS cust2
    FROM orders o1
    JOIN orders o2 
      ON o1.customer < o2.customer 
      AND o1.product_id = o2.product_id
    GROUP BY o1.customer, o2.customer
    HAVING COUNT(DISTINCT o1.product_id) >= 2
),
bidirectional_similar AS (
    -- 转换为双向客户对，保证每个客户能查到所有相似客户
    SELECT cust1 AS customer, cust2 AS similar_cust FROM similar_customers
    UNION ALL
    SELECT cust2 AS customer, cust1 AS similar_cust FROM similar_customers
)
-- 获取相似客户的产品，并排除当前客户已购产品
SELECT DISTINCT bs.customer, o.product_id
FROM bidirectional_similar bs
JOIN orders o 
  ON bs.similar_cust = o.customer
LEFT JOIN orders self_orders 
  ON bs.customer = self_orders.customer 
  AND o.product_id = self_orders.product_id
WHERE self_orders.product_id IS NULL
ORDER BY bs.customer, o.product_id;

逻辑说明 #

1. similar_customers CTE：通过自连接订单表，找出所有共同购买≥2款产品的客户对，用cust1 < cust2确保每对客户只记录一次，避免重复计算。
2. bidirectional_similar CTE：将单向客户对转换为双向，比如(A,B)和(B,A)都保留，这样每个客户都能获取到自己的所有相似客户。
3. 最终查询：关联双向客户对与订单表，拿到相似客户的所有产品；再通过左连接排除当前客户已购买的产品，最后去重并排序得到结果。

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