关于后端:LeetCode-mysql-刷题二电影评分判断日期的五种方法-审核中

题目

题目链接：电影评分

请你编写一个解决方案：

查找评论电影数量最多的用户名。如果呈现平局，返回字典序较小的用户名。

查找在 February 2020 均匀评分最高 的电影名称。如果呈现平局，返回字典序较小的电影名称。

字典序 ，即按字母在字典中呈现程序对字符串排序，字典序较小则意味着排序靠前。

返回后果格局如下例所示。

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');

输出：Movies 表：+-------------+--------------+| movie_id    |  title       |+-------------+--------------+| 1           | Avengers     || 2           | Frozen 2     || 3           | Joker        |+-------------+--------------+movie_id 是这个表的主键(具备惟一值的列)。title 是电影的名字。Users 表：+-------------+--------------+| user_id     |  name        |+-------------+--------------+| 1           | Daniel       || 2           | Monica       || 3           | Maria        || 4           | James        |+-------------+--------------+user_id 是表的主键(具备惟一值的列)。MovieRating 表：+-------------+--------------+--------------+-------------+| movie_id    | user_id      | rating       | created_at  |+-------------+--------------+--------------+-------------+| 1           | 1            | 3            | 2020-01-12  || 1           | 2            | 4            | 2020-02-11  || 1           | 3            | 2            | 2020-02-12  || 1           | 4            | 1            | 2020-01-01  || 2           | 1            | 5            | 2020-02-17  || 2           | 2            | 2            | 2020-02-01  || 2           | 3            | 2            | 2020-03-01  || 3           | 1            | 3            | 2020-02-22  || 3           | 2            | 4            | 2020-02-25  |+-------------+--------------+--------------+-------------+(movie_id, user_id) 是这个表的主键(具备惟一值的列的组合)。这个表蕴含用户在其评论中对电影的评分 rating 。created_at 是用户的点评日期输入：Result 表：+--------------+| results      |+--------------+| Daniel       || Frozen 2     |+--------------+解释：Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 然而 Daniel 字典序比拟小。Frozen 2 和 Joker 在 2 月的评分都是 3.5，然而 Frozen 2 的字典序比拟小。

解析

本题考查的知识点是应用 union all 将两张表后果合并起来

MySQL 中判断判断日期的五种办法：

• DATE_FORMAT(created_at, '%Y-%m') = '2020-02'
• YEAR(created_at) = 2020 AND MONTH(created_at) = 2
• created_at BETWEEN '2020-02-01' AND '2020-02-29'
• created_at LIKE '2020-02%'
• DATE_FORMAT(created_at, '%Y-%m') = DATE_FORMAT('2020-02-01', '%Y-%m')

办法一

1. 先将 MovieRating 表和 Users 表进行连贯，失去每个用户的评分次数

   • 通过 user_id 关联
   • 依照 user.name 分组，就能够计算每个用户评分次数了
   • 依照评分次数降序，name 升序排序
   • 取第一条数据
   • 作为长期表 tmp1

2. 再将 MovieRating 表和 Movies 表进行连贯，失去每部电影的均匀评分

   • 通过 movie_id 关联，并且筛选出 2020-02 月份的数据
   • 依照 title 分组，就能够计算每部电影的均匀评分了
   • 依照均匀评分降序，title 升序排序
   • 取第一条数据
   • 作为长期表 tmp2
3. 第一步查问进去的 name 和第二步查问进去的 title 命令为 results
4. 将两个后果合并起来，应用 union all 即可

SELECT  tmp1.resultsFROM (  SELECT    name results, count( MovieRating.user_id ) number  FROM MovieRating,Users  WHERE MovieRating.user_id = Users.user_id  GROUP BY Users.name  ORDER BY number DESC, NAME ASC LIMIT 1) tmp1UNION ALLSELECT  tmp2.resultsFROM (  SELECT    MovieRating.movie_id, title results, avg( rating ) max_rating  FROM MovieRating, Movies  WHERE    MovieRating.movie_id = Movies.movie_id AND created_at LIKE "2020-02%"  GROUP BY title  ORDER BY max_rating DESC, title LIMIT 1) tmp2

办法二

应用窗口函数对后果进行排序，而后取第一条数据

1. 先将 MovieRating 表和 Users 表进行连贯，失去每个用户的评分次数

   • 通过 user_id 关联
   • 依照 user.name 分组，就能够计算评分的次数了
   • 依照评分次数降序，name 升序排序
   • 应用 rank() 函数对后果进行排序，失去每个用户的排名
   • 产出 name 和 ranking 两个数据

2. 再将 MovieRating 表和 Movies 表进行连贯，失去每部电影的均匀评分

   • 通过 movie_id 关联，并且筛选出 2020-02 月份的数据
   • 依照 title 分组，就能够计算每部电影的均匀评分了
   • 依照均匀评分降序，title 升序排序
   • 应用 rank() 函数对后果进行排序，失去每部电影的排名
   • 产出 name 和 ranking 两个数据
3. 将两次查问的后果用 union all 合并起来，作为长期表 tmp
4. 从长期表 tmp 中取出排名为 1 的数据

SELECT  resultsFROM (  SELECT    name results,    rank() over ( ORDER BY count( 1 ) DESC, name ) ranking  FROM    MovieRating, Users  WHERE MovieRating.user_id = Users.user_id  GROUP BY name  UNION ALL  SELECT    title results,    rank() over ( ORDER BY avg( rating ) DESC, title ) ranking  FROM    MovieRating, Movies  WHERE MovieRating.movie_id = Movies.movie_id AND created_at LIKE "2020-02%"  GROUP BY Moviestitle) tmpWHERE  tmp.ranking = 1

往期 MySQL 题目

1. MySQL 题目
2. LeetCode mysql 刷题一：计算非凡奖金 | 买下所有产品的客户