- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
经典案例 分组求top1
目录
一,原题力扣链接
二,题干
表:
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 是该表主键. 该表包含所有商品的信息.写一个解决方案, 找到每件商品的最新订单(可能有多个).
返回的结果以
product_name
升序排列, 如果有排序相同, 再以product_id
升序排列. 如果还有排序相同, 再以order_id
升序排列.查询结果格式如下例所示。
示例 1:
输入:Customers表:
+-------------+-----------+ | customer_id | name | +-------------+-----------+ | 1 | Winston | | 2 | Jonathan | | 3 | Annabelle | | 4 | Marwan | | 5 | Khaled | +-------------+-----------+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 | 1 | | 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 | +------------+--------------+-------+ 输出: +--------------+------------+----------+------------+ | product_name | product_id | order_id | order_date | +--------------+------------+----------+------------+ | keyboard | 1 | 6 | 2020-08-01 | | keyboard | 1 | 7 | 2020-08-01 | | mouse | 2 | 8 | 2020-08-03 | | screen | 3 | 3 | 2020-08-29 | +--------------+------------+----------+------------+ 解释: keyboard 的最新订单在2020-08-01, 在这天有两次下单. mouse 的最新订单在2020-08-03, 在这天只有一次下单. screen 的最新订单在2020-08-29, 在这天只有一次下单. hard disk 没有被下单, 我们不把它包含在结果表中.
三,建表语句
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', 'Winston');
insert into Customers (customer_id, name) values ('2', 'Jonathan');
insert into Customers (customer_id, name) values ('3', 'Annabelle');
insert into Customers (customer_id, name) values ('4', 'Marwan');
insert into Customers (customer_id, name) values ('5', 'Khaled');
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', '1');
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
表二 订单表
字段:订单id 订购日期,客户id,产品id
表上 产品表
字段:产品id,产品姓名,价格
求:每件商品的最新将订单 考虑并列 并且按照要求排序
第一步: 内连接 订单表和产品表
select
order_id,order_date,o.product_id ,p.product_name,p.price,
rank() over (partition by o.product_id order by order_date desc ) rn
from Orders o join products p on o.product_id=p.product_id
第二步,经典案例 分组求top1
第三步,取并列第一的 行 然后选取指定的列 最后按照要求排序即可
五,SQL解答
with t1 as (
select
order_id,order_date,o.product_id ,p.product_name,p.price,
rank() over (partition by o.product_id order by order_date desc ) rn
from Orders o join products p on o.product_id=p.product_id
)
select
product_name,
product_id,
order_id,
order_date
from t1 where rn=1 order by product_name,product_id,order_id ;
六,验证
七,知识点总结
- 开窗函数的运用
- 经典案例 分组求top1 要求取1的值 考虑并列 用rank
- 排序的运用
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用