- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
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);
六,验证
七,知识点总结
- 以某列分组,求另外以内的行数,用count;
- 以某列分组,求另外的平均分,用vag
- 排序联系,以行数最大的排序,以列名称降序 在limit 取第一个
- 连接联系
- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用