LeetCode mysql 刷题二:电影评分——判断日期的五种方法

2023年 9月 21日 79.3k 0

题目

题目链接:电影评分

请你编写一个解决方案:

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

查找在 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() 函数对结果进行排序,得到每个用户的排名
    • 产出 nameranking 两个数据
  • 再将 MovieRating 表和 Movies 表进行连接,得到每部电影的平均评分
    • 通过 movie_id 关联,并且筛选出 2020-02 月份的数据
    • 按照 title 分组,就可以计算每部电影的平均评分了
    • 按照平均评分降序,title 升序排序
    • 使用 rank() 函数对结果进行排序,得到每部电影的排名
    • 产出 nameranking 两个数据
  • 将两次查询的结果用 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
    

    往期 MySQL 题目

  • MySQL 题目
  • LeetCode mysql 刷题一:计算特殊奖金 | 买下所有产品的客户
  • 相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论