此方法思路非常的简单,但代码很复杂。但容易听懂
一:题目
表: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
平均评分最高 的电影名称。如果出现平局,返回字典序较小的电影名称。
字典序 ,即按字母在字典中出现顺序对字符串排序,字典序较小则意味着排序靠前。
返回结果格式如下例所示。
二:创建数据库
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')
三:分析
(一)、理解表中数据的含义
1.Movies表
表中第一个字段是movie_id就是电影的id用来标识电影,可以用来关联和查询等。
表中第二个字段是title就是电影的名字,后面可以用来比较字段序。
2.Users表
表中第一个字段是user_id就是用户的id用来标识用户,可以用来关联和查询等。
表中第二个字段是name就是用户的名字,后面可以用来区别和比较字段序。
3.MovieRating表
表中第一个字段是:movie_id用来关联Movies表得到其中的电影信息。
表中第二个字段是:user_id用来关联user表中的用户信息。
表中第三个字段是:rating是用户对电影的评分。
表中第四个字段是:created_at是用户评价电影的信息。
理解完这些字段对做这道题有很大的帮助。
(二)、分析问题
1.在这个题目中出现最多的就是字典序,首先了解什么是字典序
字典序就是比较字母的顺序,从a-->z 26个字母的顺序比较。如果要比较的两个字典第一个字母相同则比较第二个。在这道题里面,有两种方法可以得到最小的字典序
1.利用min()函数
2.利用order by 列名 limit 1 的方法
都可以得到最小的字典序。
2.把问题拆分。
首先我们对问题进行解析完之后,会发现查询评论电影数量最多的用户名和查询2020年2月份平均评分最高的电影名称这两个问题相互不关联,并且我们很难将这两个问题查询的内容一起输出出来。这时候我们可以想到把这两个问题分开查询,然后最后用union函数把他们合并一下。
四:解决问题
通过上面的分析后。我们把这两个问题拆分开解决。
第一问:
查询评论电影数量最多的用户名。如果平数,返回字典序较小的用户名
我们可以通过对
MovieRating表进行对user_id进行分组然后对movie_id进行计数,可以得到每个用户评论了几个人
select user_id,count(movie_id)cn from movierating group by user_id
然后把上面这个数据作为临时表,然后输出评论最多是评论了几个人(上面很显然评论最多的是3人)然后我们通过代码进行实现。
with t1 as (select user_id, count(movie_id) cn from movierating group by user_id)
select max(cn)
FROM t1
得到最大的评论数之后我们再通过子查询那些人评论了这个数字。
select user_id from movierating
group by user_id
having count(movie_id) = (with t1 as (select user_id,
count(movie_id) cn
from movierating
group by user_id)
select max(cn)
FROM t1)
我们得到了评论最多的人,发现他们是有两个人,这时候我们需要判断字段序谁大谁小。然后我们现在有了id,这时候想要知道这些id分别是谁,就非常清楚。得出来之后就可以判断大小。
通过子查询的方法可以得到那些人评论的最多。
然后利用排序或者min函数都可以得到最小的字典序。
select min(name)
from users1
where user_id in (select user_id
from movierating
group by user_id
having count(movie_id) = (with t1 as (select user_id,
count(movie_id) cn
from movierating
group by user_id)
select max(cn)
FROM t1));
接着改下列名,方便之后与第二问进行关联
第二问:
查找在February 2020评分最高的电影名称,如果出现出局,返回字典序较小的电影名称。
首先,对题目解析之后我们可以得出,首先我们看到的评论时间是年月日形式的数据,我们先把评论日期进行格式化只输出年月利用date_format函数,然后筛选出February 2020时候的数据。最终我们通过以下代码可以得到年月的数据
with t2 as (select movie_id,
user_id,
rating,
date_format(created_at, '%Y-%M') format
from movierating)
select *
FROM t2
where format = '2020-February'
然后对每个电影进行分组,求得平均的评分,注意平均数要进行保留小数点后几位数字。否则虽然不会报错,但返回不出来值。
with t2 as (select movie_id,
user_id,
rating,
date_format(created_at, '%Y-%M') format
from movierating)
select t2.movie_id,round(avg(rating), 2) pingjun
FROM t2
where format = '2020-February'
group by movie_id
然后我们找到最大的平均数
with t2 as (select movie_id,
user_id,
rating,
date_format(created_at, '%Y-%M') format
from movierating),
t3 as (select round(avg(rating), 2) pingjun
FROM t2
where format = '2020-February'
group by movie_id)
select max(pingjun)
FROM t3
然后找到平均数等于这个最大的电影名称就行,在进行排序。
select min(title)results from movies where movie_id in(
with t2 as (select movie_id,
user_id,
rating,
date_format(created_at, '%Y-%M') format
from movierating)
select movie_id
FROM t2
where format = '2020-February'
group by movie_id
having avg(rating) = (with t2 as (select movie_id,
user_id,
rating,
date_format(created_at, '%Y-%M') format
from movierating),
t3 as (select round(avg(rating), 2) pingjun
FROM t2
where format = '2020-February'
group by movie_id)
select max(pingjun)
FROM t3))
然后对两个结果进行合并。union all函数(因为如果user返回的结果和movie返回的结果相同使用union函数则返回不出结果)
五、最终代码
select min(name) results
from users1
where user_id in (select user_id
from movierating
group by user_id
having count(movie_id) = (with t1 as (select user_id,
count(movie_id) cn
from movierating
group by user_id)
select max(cn)
FROM t1))
union all
select min(title) results
from movies
where movie_id in (with t2 as (select movie_id,
user_id,
rating,
date_format(created_at, '%Y-%M') format
from movierating)
select movie_id
FROM t2
where format = '2020-February'
group by movie_id
having avg(rating) = (with t2 as (select movie_id,
user_id,
rating,
date_format(created_at, '%Y-%M') format
from movierating),
t3 as (select round(avg(rating), 2) pingjun
FROM t2
where format = '2020-February'
group by movie_id)
select max(pingjun)
FROM t3))
力扣返回的结果
六、结语:
最终代码,非常的长。但是思路是正确的,如果大家有什么别的想法或者更好的方法,可以进行评论。希望对大家有帮助。