当前位置：首页 > news >正文

织梦电影网站模板下载苏州工业园区

news 2025/11/6 20:41:14

织梦电影网站模板下载,苏州工业园区,pc 网站开发设计,在线企业建站服务题目准备数据分析数据总结题目查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。查找在 February 2020 平均评分最高的电影名称。如果出现平局，返回字典序较小的电影名称。准备数据 ## 创建库 create database db; u…

题目

准备数据

分析数据

总结

题目

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

准备数据

## 创建库
create database db;
use sb;## 创建movies表
Create table If Not Exists Movies (movie_id int, title varchar(30));## 创建users表
Create table If Not Exists Users (user_id int, name varchar(30));## 创建MovieRating表
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表

users表

movierating表

分析数据

结果分两个结果，且不相关，因此最后使用union all进行连接。
1.Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 但是 Daniel 字典序比较小。2.Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。
字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。

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

with t1 as (select user_id,count(rating) rating from movierating group by user_id
) select min(a.name) resultsfrom users ajoin t1 b on b.user_id = a.user_id
where b.rating = (select max(rating) rating from t1);

第二步：查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

with t2 as (select movie_id,avg(rating) ratingfrom movieratingwhere created_at between '2020-02-01' and '2020-02-29'group by movie_id
)select min(a.title) resultsfrom movies a join t2 b on b.movie_id = a.movie_id
where b.rating = (select max(rating) rating from t2);

第三步：将两个结果进行连接。

with t1 as (select user_id,count(rating) rating from movierating group by user_id
) ,t2 as (select movie_id,avg(rating) ratingfrom movieratingwhere created_at between '2020-02-01' and '2020-02-29'group by movie_id
)
select min(a.name) resultsfrom users ajoin t1 b on b.user_id = a.user_id
where b.rating = (select max(rating) rating from t1)
union all
select min(a.title) resultsfrom movies a join t2 b on b.movie_id = a.movie_id
where b.rating = (select max(rating) rating from t2);