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 是这张表的唯一主键。
每一行表示一件商品的类别和价格。
亚马逊想要了解不同产品类别的购物模式。编写一个解决方案：
-
查找所有 类别对（其中 category1 < category2）
-
对于 每个类别对，确定 同时 购买了两类别产品的 不同用户 数量
如果至少有 3 个不同的客户购买了两个类别的产品，则类别对被视为 可报告的。
返回可报告类别对的结果表以 customer_count 降序 排序，并且为了防止排序持平，以 category1 字典序 升序 排序，然后以 category2 升序 排序。
1.2.题目地址
https://leetcode.cn/problems/find-category-recommendation-pairs/description/
2.解题方法
2.1.解题思路
自连接+分组聚合+排序
2.2.解题步骤
第一步，将ProductInfo连接到ProductPurchases表格上，得到T1
第二步，T1表格进行自连接，并确保user_id相等和category1
第三步，T2的基础上进行排序，然后返回
3.解题代码
sql代码
# Write your MySQL query statement below
WITH T1 AS (
# 第一步，将ProductInfo连接到ProductPurchases表格上，得到T1
SELECT * FROM ProductPurchases LEFT JOIN ProductInfo USING(product_id)
), T2 AS (
# 第二步，T1表格进行自连接，并确保user_id相等和category1<category2，同时以<category1>-<category2>为键，聚合统计不重复的用户数，记为user_count字段，并过滤出user_count>=3的记录，得到表格T2
SELECT
p1.category AS category1,
p2.category AS category2,
COUNT(DISTINCT p1.user_id) AS user_count
FROM T1 AS p1 JOIN T1 AS p2 ON p1.category < p2.category AND p1.user_id = p2.user_id
GROUP BY p1.category, p2.category
HAVING COUNT(DISTINCT p1.user_id) >= 3
)
# 第三步，T2的基础上进行排序，然后返回
SELECT
category1, category2,
user_count AS customer_count
FROM T2
ORDER BY customer_count DESC, category1, category24.执行结果
