一、力扣链接
二、题目描述
表：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。
返回结果 没有顺序要求。
三、目标拆解
四、建表语句
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')五、过程分析
1、分组、求排名
2、排名为1即为最常订购
六、代码实现
with t1 as(
select customer_id,
product_id,
rank() over(partition by customer_id order by count(product_id) desc) rn
from orders -- 这里排名使用了分组后的count()函数进行排序
group by customer_id, product_id
)
select customer_id,
product_id,
(select product_name from Products p where p.product_id = t1.product_id) product_name
from t1 where rn = 1;七、结果验证
八、小结
1、CTE表达式 + rank() + count() + 子查询
2、注意group by 分组后select 后只能是分组字段和聚合函数