Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

58 电影评分 #70

Open
astak16 opened this issue Sep 19, 2023 · 0 comments
Open

58 电影评分 #70

astak16 opened this issue Sep 19, 2023 · 0 comments
Labels

Comments

@astak16
Copy link
Owner

astak16 commented Sep 19, 2023

题目

题目链接:电影评分

请你编写一个解决方案:

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

查找在 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() 函数对结果进行排序,得到每个用户的排名
    • 产出 nameranking 两个数据
  2. 再将 MovieRating 表和 Movies 表进行连接,得到每部电影的平均评分
    • 通过 movie_id 关联,并且筛选出 2020-02 月份的数据
    • 按照 title 分组,就可以计算每部电影的平均评分了
    • 按照平均评分降序,title 升序排序
    • 使用 rank() 函数对结果进行排序,得到每部电影的排名
    • 产出 nameranking 两个数据
  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
@astak16 astak16 added the 中等 label Sep 19, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

1 participant