1596. 每位顾客最经常订购的商品 - 力扣（LeetCode）

1. 1596. 每位顾客最经常订购的商品 - 力扣（LeetCode）

2. 目标

   1. 输入

      表：Products
      			product_id	product_name	price
      1	keyboard	120
      2	mouse	80
      3	screen	600
      4	hard disk	450

      表：Orders
      				order_id	order_date	customer_id	product_id
      1	2020/7/31	1	1
      2	2020/7/30	2	2
      3	2020/8/29	3	3
      4	2020/7/29	4	1
      5	2020/6/10	1	2
      6	2020/8/1	2	1
      7	2020/8/1	3	3
      8	2020/8/3	1	2
      9	2020/8/7	2	3
      10	2020/7/15	1	2

      表：Customers
      		customer_id	name
      1	Alice
      2	Bob
      3	Tom
      4	Jerry
      5	John

   2. 输出

      输出:
      			customer_id	product_id	product_name
      2	1	keyboard
      4	1	keyboard
      1	2	mouse
      2	2	mouse
      2	3	screen
      3	3	screen

3. 分析

   写一个解决方案，找到每一个顾客最经常订购的商品。 结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。
   																		表：Products				表：Orders					表：Customers			输出:
   product_id	product_name	price		order_id	order_date	customer_id	product_id		customer_id	name		customer_id	product_id	product_name
   1	keyboard	120		1	2020/7/31	1	1		1	Alice		2	1	keyboard
   2	mouse	80		2	2020/7/30	2	2		2	Bob		4	1	keyboard
   3	screen	600		3	2020/8/29	3	3		3	Tom		1	2	mouse
   4	hard disk	450		4	2020/7/29	4	1		4	Jerry		2	2	mouse
   				5	2020/6/10	1	2		5	John		2	3	screen
   				6	2020/8/1	2	1					3	3	screen
   				7	2020/8/1	3	3
   				8	2020/8/3	1	2
   				9	2020/8/7	2	3
   				10	2020/7/15	1	2
   分组计算每个顾客订购每个商品的次数		customer_id	product_id	cnt	每个顾客订购每个商品的次数排序		customer_id	product_id	cnt	rn	查询用户订购最多次数的商品id和name		customer_id	product_id	product_name
   		1	1	1			1	2	3	1			2	1	keyboard
   		2	2	1			1	1	1	2			4	1	keyboard
   		3	3	2			2	2	1	1			1	2	mouse
   		4	1	1			2	1	1	1			2	2	mouse
   		1	2	3			2	3	1	1			2	3	screen
   		2	1	1			3	3	2	1			3	3	screen
   		2	3	1			4	1	1	1

4. 实现

   DROP TABLE IF EXISTS Customers;
   DROP TABLE IF EXISTS Orders;
   DROP TABLE IF EXISTS Products;
   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 Products;
   SELECT *
   FROM Orders;
   SELECT *
   FROM Customers;
   # 分组计算每个顾客订购每个商品的次数
   SELECT customer_id,product_id,count(*)cnt
   FROM Orders
   GROUP BY customer_id, product_id;
   # 每个顾客订购每个商品的次数排序
   WITH t1 AS ( SELECT customer_id,product_id,count(*)cnt
   FROM Orders
   GROUP BY customer_id, product_id )
   SELECT customer_id, product_id,cnt,rank() OVER (PARTITION BY customer_id ORDER BY cnt DESC )rn
   FROM t1;
   # 查询用户订购最多次数的商品id和name
   WITH t1 AS ( SELECT customer_id,product_id,count(*)cnt
   FROM Orders
   GROUP BY customer_id, product_id ),
   t2 AS ( SELECT customer_id, product_id,cnt,rank() OVER (PARTITION BY customer_id ORDER BY cnt DESC )rn
   FROM t1 )
   SELECT customer_id,p.product_id,product_name
   FROM t2,products p
   WHERE rn=1 AND t2.product_id=p.product_id;

5. 小结

        rank排序窗口,子查询