1.题目基本信息

1.1.题目描述

表：ProductPurchases

+-------------+------+

| Column Name | Type |

+-------------+------+

| user_id | int |

| product_id | int |

| quantity | int |

+-------------+------+

(user_id, product_id) 是这张表的唯一主键。

每一行代表用户以特定数量购买的产品。

表：ProductInfo

+-------------+---------+

| Column Name | Type |

+-------------+---------+

| product_id | int |

| category | varchar |

| price | decimal |

+-------------+---------+

product_id 是这张表的唯一主键。

每一行表示一个产品的类别和价格。

亚马逊希望根据 共同购买模式 实现 “购买此商品的用户还购买了...” 功能。编写一个解决方案以实现：

1. 识别 被同一客户一起频繁购买的 不同 产品对（其中 product1_id < product2_id）

2. 对于 每个产品对，确定有多少客户购买了这两种产品

如果 至少有 3 位不同的 客户同时购买了这两种产品，则认为该 产品对 适合推荐。

返回结果表以 customer_count 降序 排序，并且为了避免排序持平，以 product1_id 升序 排序，并以 product2_id 升序 排序。

1.2.题目地址

https://leetcode.cn/problems/find-product-recommendation-pairs/description/

2.解题方法

2.1.解题思路

自连接+分组聚合

2.2.解题步骤

第一步，将ProductInfo的相关信息根据product_id绑定到ProductPurchases表格中，记为表格T1

第二步，基于T1，进行自连接，保证user_id相等且product1_id<product2_id，得到表格T2

第三步，基于T2，使用user_id,product1_id,product2_id进行分组聚合，筛选出行数大于等于3的记录，并构建结果表

3.解题代码

sql代码

# Write your MySQL query statement below

WITH T1 AS (
    # 第一步，将ProductInfo的相关信息根据product_id绑定到ProductPurchases表格中，记为表格T1
    SELECT *
    FROM ProductPurchases LEFT JOIN ProductInfo USING(product_id)
), T2 AS (
    # 第二步，基于T1，进行自连接，保证user_id相等且product1_id<product2_id，得到表格T2
    SELECT 
        t11.user_id, 
        t11.product_id AS product1_id, 
        t12.product_id AS product2_id, 
        t11.category AS product1_category, 
        t12.category AS product2_category
    FROM T1 AS t11 JOIN T1 AS t12 ON t11.user_id = t12.user_id AND t11.product_id < t12.product_id
)

# 第三步，基于T2，使用user_id,product1_id,product2_id进行分组聚合，筛选出行数大于等于3的记录，并构建结果表
SELECT 
    product1_id, product2_id, product1_category, product2_category, 
    COUNT(user_id) AS customer_count
FROM T2
GROUP BY product1_id, product2_id, product1_category, product2_category
HAVING COUNT(user_id) >= 3
ORDER BY customer_count DESC, product1_id, product2_id

4.执行结果