查找每位顾客最常订购的商品
问题描述
给定三张表：Customers、Orders 和 Products，要求查询每位至少下过一次单的顾客（`customer_id`）及其最常订购的商品（`product_id` 和 `product_name`）。如果某个顾客对多个商品的订购次数相同且为最高，则需要列出所有这些商品。表结构
- Customers 表： 包含客户信息。
- Orders 表： 包含订单记录，其中每个客户每天不会订购同一商品多次。
- Products 表： 包含商品详情。
示例数据
Customers 表：
+-------------+-------+ | customer_id | name | +-------------+-------+ | 1 | Alice | | 2 | Bob | | 3 | Tom | | 4 | Jerry | | 5 | John | +-------------+-------+
Orders 表：
+----------+------------+-------------+------------+ | order_id | order_date | customer_id | product_id | +----------+------------+-------------+------------+ | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 3 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------------+
Products 表：
+------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 | +------------+--------------+-------+
预期输出
+-------------+------------+--------------+ | customer_id | product_id | product_name | +-------------+------------+--------------+ | 1 | 2 | mouse | | 2 | 1 | keyboard | | 2 | 2 | mouse | | 2 | 3 | screen | | 3 | 3 | screen | | 4 | 1 | keyboard | +-------------+------------+--------------+
解决方案
以下是实现该需求的 SQL 查询代码：
WITH OrderSummary AS (
SELECT
o.customer_id,
p.product_id,
p.product_name,
COUNT(o.order_id) AS purchase_count
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY o.customer_id, p.product_id, p.product_name
),
RankedPurchases AS (
SELECT
customer_id,
product_id,
product_name,
purchase_count,
RANK() OVER (PARTITION BY customer_id ORDER BY purchase_count DESC) AS rank_num
FROM OrderSummary
)
SELECT
customer_id,
product_id,
product_name
FROM RankedPurchases
WHERE rank_num = 1;
步骤解析
- 通过连接 Orders 和 Products 表，统计每个客户的每种商品购买次数。
- 使用窗口函数 RANK 对每个客户按购买次数降序排列，生成排名。
- 筛选出排名为 1 的记录作为最终结果。
验证结果
运行上述 SQL 后，得到的结果与预期一致。
