1341. 电影评分 - 力扣（LeetCode）

1. 1341. 电影评分 - 力扣（LeetCode）

2. 目标

   1. 输入

      输入:评分表
      				movie_id	user_id	rating	created_at
      1	1	3	2020/1/12
      1	2	4	2020/2/11
      1	3	2	2020/2/12
      1	4	1	2020/1/1
      2	1	5	2020/2/17
      2	2	2	2020/2/1
      2	3	2	2020/3/1
      3	1	3	2020/2/22
      3	2	4	2020/2/25

      输入:用户表
      		user_id	name
      1	Daniel
      2	Monica
      3	Maria
      4	James

      输入:电影表
      		movie_id	title
      1	Avengers
      2	Frozen 2
      3	Joker

   2. 输出

      result

      Daniel

      Frozen 2

3. 分析

   请你编写一个解决方案： 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。 查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。 字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。
   																									输入:评分表						输入:用户表				输入:电影表			输出:result表
   movie_id	user_id	rating	created_at			user_id	name			movie_id	title		result
   1	1	3	2020/1/12			1	Daniel			1	Avengers		Daniel
   1	2	4	2020/2/11			2	Monica			2	Frozen 2		Frozen 2
   1	3	2	2020/2/12			3	Maria			3	Joker
   1	4	1	2020/1/1			4	James
   2	1	5	2020/2/17
   2	2	2	2020/2/1
   2	3	2	2020/3/1
   3	1	3	2020/2/22
   3	2	4	2020/2/25
   按用户名分组计数评论次数并按照字典序排列		name	cnt	num	按电影名分组计算再February 202平均评分并按照字典序排序		title	avg	rn	分别查询num和rn为1的用户名和电影名使用全连接UNION ALL连接结果		result
   		Daniel	3	1			Frozen 2	3.5	1			Daniel
   		Monica	3	2			Joker	3.5	2			Frozen 2
   		Maria	2	3			Avengers	2.5	3
   		James	1	4

4. 实现

   DROP TABLE IF EXISTS Movies;
   DROP TABLE IF EXISTS Users;
   DROP TABLE IF EXISTS MovieRating;
   Create table If Not Exists Movies (movie_id int, title varchar(30));
   Create table If Not Exists Users (user_id int, name varchar(30));
   Create table If Not Exists MovieRating (movie_id int, user_id int, rating int, created_at date);
   Truncate table Movies;
   insert into Movies (movie_id, title) values ('1', 'Avengers');
   insert into Movies (movie_id, title) values ('2', 'Frozen 2');
   insert into Movies (movie_id, title) values ('3', 'Joker');
   Truncate table Users;
   insert into Users (user_id, name) values ('1', 'Daniel');
   insert into Users (user_id, name) values ('2', 'Monica');
   insert into Users (user_id, name) values ('3', 'Maria');
   insert into Users (user_id, name) values ('4', 'James');
   Truncate table MovieRating;
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '1', '3', '2020-01-12');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '2', '4', '2020-02-11');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '3', '2', '2020-02-12');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '4', '1', '2020-01-01');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '1', '5', '2020-02-17');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '2', '2', '2020-02-01');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '3', '2', '2020-03-01');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('3', '1', '3', '2020-02-22');
   insert into MovieRating (movie_id, user_id, rating, created_at) values ('3', '2', '4', '2020-02-25');
   SELECT * FROM Movies;
   SELECT * FROM Users;
   SELECT * FROM MovieRating;
   SELECT name results
   FROM (SELECT Users.name,row_number()  OVER (ORDER BY count(*)DESC,name )num
   FROM movierating,users
   WHERE Users.user_id=MovieRating.user_id
   GROUP BY name)n
   WHERE num=1
   UNION ALL 
   SELECT title results
   FROM (SELECT title,row_number() OVER (ORDER BY avg(rating)DESC,title )rn
   FROM movierating,movies
   where Movies.movie_id=MovieRating.movie_id AND year(created_at)=2020 AND month(created_at)=2
   GROUP BY title )t
   WHERE rn=1;

5. 小结

        使用row_number排序,union all全连接结果