90，SQL训练之，力扣，1596. 每位顾客最经常订购的商品

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表：Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
+---------------+---------+
customer_id 是该表具有唯一值的列
该表包含所有顾客的信息

表：Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| product_id    | int     |
+---------------+---------+
order_id 是该表具有唯一值的列
该表包含顾客 customer_id 的订单信息
没有顾客会在一天内订购相同的商品 多于一次

表：Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
| price         | int     |
+---------------+---------+
product_id 是该表具有唯一值的列
该表包含了所有商品的信息

写一个解决方案，找到每一个顾客最经常订购的商品。

结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。

返回结果 没有顺序要求。

查询结果格式如下例所示。

示例 1：

输入：
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     |
+-------------+------------+--------------+
解释：
Alice (customer 1) 三次订购鼠标, 一次订购键盘, 所以鼠标是 Alice 最经常订购的商品.
Bob (customer 2) 一次订购键盘, 一次订购鼠标, 一次订购显示器, 所以这些都是 Bob 最经常订购的商品.
Tom (customer 3) 只两次订购显示器, 所以显示器是 Tom 最经常订购的商品.
Jerry (customer 4) 只一次订购键盘, 所以键盘是 Jerry 最经常订购的商品.
John (customer 5) 没有订购过商品, 所以我们并没有把 John 包含在结果表中.

三，建表语句

Create table If Not Exists Customers (customer_id int, name varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, customer_id int, product_id int);
Create table If Not Exists Products (product_id int, product_name varchar(20), price int);
Truncate table Customers;
insert into Customers (customer_id, name) values ('1', 'Alice');
insert into Customers (customer_id, name) values ('2', 'Bob');
insert into Customers (customer_id, name) values ('3', 'Tom');
insert into Customers (customer_id, name) values ('4', 'Jerry');
insert into Customers (customer_id, name) values ('5', 'John');
Truncate table Orders;
insert into Orders (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3');
insert into Orders (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '3');;
insert into Orders (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3');
insert into Orders (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2');
Truncate table Products;
insert into Products (product_id, product_name, price) values ('1', 'keyboard', '120');
insert into Products (product_id, product_name, price) values ('2', 'mouse', '80');
insert into Products (product_id, product_name, price) values ('3', 'screen', '600');
insert into Products (product_id, product_name, price) values ('4', 'hard disk', '450');


select * from customers;
select * from Orders;
select * from Products;

四，分析

题解:

表1:客户表

字段:客户id,客户姓名

表2:订单表

字段:订单id,订单日期,客户id,产品id

表3:产品表

字段:产品id,产品名称,产品价格

求:结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。

 

 

第一步:三表合并; 内连接  去掉没有下单的人 以及没有被购买过的商品 以订单表为核心

    select
           c. name, o.order_id, o.order_date, o.customer_id, o.product_id,p.product_name,p.price
    from  customers c join orders o on c.customer_id=o.customer_id
                     join products p on o.product_id =p.product_id

 

第二步:以客户id,产品id,产品名称分组 统计购买的次数

with t1 as (
    select
           c. name, o.order_id, o.order_date, o.customer_id, o.product_id,p.product_name,p.price
    from  customers c join orders o on c.customer_id=o.customer_id
                     join products p on o.product_id =p.product_id
),t2 as (
    select
         customer_id,product_id,product_name,count(customer_id) cnt
        from t1 group by customer_id,product_id,product_name
),t3 as (
        select
         customer_id,product_id,product_name,cnt,
         rank()over(partition by customer_id order by cnt desc) rn
        from t2
)
select * from t2;

第三步,以客户id分组,以次数排序 分组求最大 分组求top1

第四步,去rank=1的行,然后映射对应的列

五，SQL解答

with t1 as (
    select
           c. name, o.order_id, o.order_date, o.customer_id, o.product_id,p.product_name,p.price
    from  customers c join orders o on c.customer_id=o.customer_id
                     join products p on o.product_id =p.product_id
),t2 as (
    select
         customer_id,product_id,product_name,count(customer_id) cnt
        from t1 group by customer_id,product_id,product_name
),t3 as (
        select
         customer_id,product_id,product_name,cnt,
         rank()over(partition by customer_id order by cnt desc) rn
        from t2
)
select customer_id,product_id,product_name from t3 where rn=1;

六，验证

七，知识点总结

• 特殊的分组求top1
• 需要先分组聚合一次 然后以聚合的列 排序求top1
• 分组在分组 可以用这个方法
• rank 排序 允许并列第一
• 经典的分组求top1