题目
题目链接:电影评分
请你编写一个解决方案:
查找评论电影数量最多的用户名。如果出现平局,返回字典序较小的用户名。
查找在 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')
方法一
MovieRating
表和 Users
表进行连接,得到每个用户的评分次数
- 通过
user_id
关联 - 按照
user.name
分组,就可以计算每个用户评分次数了 - 按照评分次数降序,
name
升序排序 - 取第一条数据
- 作为临时表
tmp1
MovieRating
表和 Movies
表进行连接,得到每部电影的平均评分
- 通过
movie_id
关联,并且筛选出2020-02
月份的数据 - 按照
title
分组,就可以计算每部电影的平均评分了 - 按照平均评分降序,
title
升序排序 - 取第一条数据
- 作为临时表
tmp2
name
和第二步查询出来的 title
命令为 results
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
方法二
使用窗口函数对结果进行排序,然后取第一条数据
MovieRating
表和 Users
表进行连接,得到每个用户的评分次数
- 通过
user_id
关联 - 按照
user.name
分组,就可以计算评分的次数了 - 按照评分次数降序,
name
升序排序 - 使用
rank()
函数对结果进行排序,得到每个用户的排名 - 产出
name
和ranking
两个数据
MovieRating
表和 Movies
表进行连接,得到每部电影的平均评分
- 通过
movie_id
关联,并且筛选出2020-02
月份的数据 - 按照
title
分组,就可以计算每部电影的平均评分了 - 按照平均评分降序,
title
升序排序 - 使用
rank()
函数对结果进行排序,得到每部电影的排名 - 产出
name
和ranking
两个数据
union all
合并起来,作为临时表 tmp
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