关于后端: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.results
FROM (
  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
) tmp1
UNION ALL
SELECT
  tmp2.results
FROM (
  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
  results
FROM (
  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
) tmp
WHERE
  tmp.ranking = 1

往期 MySQL 题目

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