33，SQL训练之，力扣，1341，电影评分

最新推荐文章于 2024-08-21 10:11:50 发布

凡梦_leo

最新推荐文章于 2024-08-21 10:11:50 发布

阅读量587

点赞数 13

分类专栏： SQL实战训练文章标签： leetcode hadoop 数据库服务器 linux sql database

本文链接：https://blog.csdn.net/qq_55006020/article/details/140834690

版权

SQL实战训练专栏收录该内容

69 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新

练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表：Movies

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| title         | varchar |
+---------------+---------+
movie_id 是这个表的主键(具有唯一值的列)。
title 是电影的名字。

表：Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
+---------------+---------+
user_id 是表的主键(具有唯一值的列)。

表：MovieRating

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| user_id       | int     |
| rating        | int     |
| created_at    | date    |
+---------------+---------+
(movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。
这个表包含用户在其评论中对电影的评分 rating 。
created_at 是用户的点评日期。

请你编写一个解决方案：

查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

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

返回结果格式如下例所示。

示例 1：

输入：
Movies 表：
+-------------+--------------+
| movie_id    |  title       |
+-------------+--------------+
| 1           | Avengers     |
| 2           | Frozen 2     |
| 3           | Joker        |
+-------------+--------------+
Users 表：
+-------------+--------------+
| user_id     |  name        |
+-------------+--------------+
| 1           | Daniel       |
| 2           | Monica       |
| 3           | Maria        |
| 4           | James        |
+-------------+--------------+
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  | 
+-------------+--------------+--------------+-------------+
输出：
Result 表：
+--------------+
| results      |
+--------------+
| Daniel       |
| Frozen 2     |
+--------------+
解释：
Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 但是 Daniel 字典序比较小。
Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。

三，建表语句

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

四，分析

题解：
第一个表：电源id，电源名称
第二个表：用户id，用户名称
第三个表：电源id，用户id，电源评分，创建时间
要求：
查找评论电源数量最多的用户   --以用户分组 以count（评论数） 排序
如果出现平局 返回字典序较小的用户名  -- 排序  cnt 降序，用户名默认

查找在20202月 平均评分最高的电源名称  如果出现平均 返回字典序较小的电源名称  
              ----以电影分组 求该电影的平均分   
-- 平均分排序 desc 然后电影名称。

实际上是 2个表拼接在一起的

第一个表

要求：查找评论电影数量最多的用户，如果出现平局返回字典序较小的用户

以用户分组，count 评分数量

然后以数量升序，用户降序排序 limit 取第一个；

要用户名，那么就拼接上用户表拿到用户名的字段

-

第二个表

要求：查找在20202月平均评分最高的电源名称如果出现平均返回字典序较小的电源名称

以电影id分组，avg 评分

以avg评分升序，以用户id降序在limit 取第一个

要电影名称那么就拼接上电影表拿到电影名

--

最后拼接上三个表。

五，SQL解答

(with t1 as (
   select
       movie_id, m1.user_id, rating, created_at, name,
       row_number() over (partition by m1.user_id) ro
       from movierating m1,users u1 where m1.user_id=u1.user_id
),t2 as (
    select user_id,name,count(ro) as cnt  from t1 group by user_id,name
)
select name as results from t2 order by cnt desc ,name limit 1 )
union all
(with t1 as (
 select m2.title,avg(rating) as avg_r
       from movierating m1,movies m2
       where m1.movie_id=m2.movie_id
         and date_format(created_at,'%Y-%m')='2020-02'
       group by m2.title
       order by avg_r desc,m2.title limit 1
)
select title from t1);