从电影评分数据实战Hive：手把手教你搞定5个经典SQL分析场景（附完整代码）-平芜编程栈

电影评分数据实战：用Hive SQL解锁5个经典分析场景

当你第一次拿到一个真实的电影评分数据集时，可能会感到既兴奋又迷茫。兴奋的是终于可以动手分析真实数据了，迷茫的是不知道从何入手。本文将以MovieLens风格的数据集为例，带你用Hive SQL完成5个典型的数据分析任务，每个任务都包含业务背景、实现思路和完整代码解析。

1. 环境准备与数据理解

在开始分析之前，我们需要先搭建好Hive环境并理解数据结构。假设你已经安装好了Hadoop和Hive，这里我们使用一个模拟MovieLens数据集，包含三张表：

t_movies：电影信息表，包含movieid、moviename和movietype字段
t_ratings：评分记录表，包含userid、movieid和rate字段
t_user：用户信息表，包含userid和sex字段

创建数据库和表的Hive SQL如下：

CREATE DATABASE IF NOT EXISTS movie_analysis; USE movie_analysis; CREATE TABLE t_movies ( movieid INT, moviename STRING, movietype STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; CREATE TABLE t_ratings ( userid INT, movieid INT, rate FLOAT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; CREATE TABLE t_user ( userid INT, sex STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

提示：实际项目中，数据加载通常使用LOAD DATA INPATH命令，但本地测试时可以用LOAD DATA LOCAL INPATH从本地文件系统加载数据。

2. 基础分析：单部电影评分统计

第一个任务是统计特定电影的评分次数。这看似简单，但包含了几个关键点：

如何准确匹配电影名称（考虑名称相似性）
表连接的正确方式
聚合函数的使用

SELECT m.movieid, m.moviename, COUNT(r.movieid) AS rating_count FROM t_movies m JOIN t_ratings r ON m.movieid = r.movieid WHERE m.moviename LIKE '%Bad Boys (1995)%' GROUP BY m.movieid, m.moviename;

关键点解析：

LIKE操作符用于模糊匹配电影名称
JOIN确保只统计有评分的电影
COUNT聚合函数计算评分次数
GROUP BY必须包含所有非聚合字段

3. 时间维度分析：年度电影评分趋势

第二个任务更有趣：按年份统计电影评分次数。这里需要从电影名称中提取年份信息：

SELECT SUBSTRING(moviename, LENGTH(moviename)-4, 4) as year, COUNT(*) as rating_count FROM t_movies m JOIN t_ratings r ON m.movieid = r.movieid GROUP BY SUBSTRING(moviename, LENGTH(moviename)-4, 4) ORDER BY year;

技术要点：

SUBSTRING函数提取电影名称中的年份
假设电影名称格式为"片名 (年份)"
结果按年份排序便于观察趋势

注意：实际数据中可能存在格式不一致的情况，这时需要更复杂的字符串处理或数据清洗。

4. 用户画像分析：性别维度的评分行为

第三个任务从用户角度出发，分析不同性别用户在特定年份的评分行为：

SELECT u.sex, COUNT(*) as rating_count FROM t_user u JOIN t_ratings r ON u.userid = r.userid JOIN t_movies m ON m.movieid = r.movieid WHERE SUBSTRING(m.moviename, LENGTH(m.moviename)-4, 4) = '1995' GROUP BY u.sex;

业务价值：

了解不同性别用户的观影偏好
为精准营销提供数据支持
发现潜在的用户行为差异

5. 高级分析：好片年份的最差电影

第四个任务更复杂，需要找出好片最多的年份中评分最低的8部电影。这需要用到临时表和多重查询：

-- 创建临时表存储每部电影的年份和平均评分 CREATE TEMPORARY TABLE temp_movie_avg_rating AS SELECT SUBSTRING(m.moviename, LENGTH(m.moviename)-4, 4) as year, AVG(r.rate) as avg_rate, m.moviename FROM t_movies m JOIN t_ratings r ON m.movieid = r.movieid GROUP BY SUBSTRING(m.moviename, LENGTH(m.moviename)-4, 4), m.moviename; -- 找出好片最多的年份 CREATE TEMPORARY TABLE temp_good_movie_year AS SELECT year, COUNT(*) as count FROM temp_movie_avg_rating WHERE avg_rate > 4.0 GROUP BY year ORDER BY count DESC LIMIT 1; -- 查询该年份评分最低的8部电影 SELECT a.year, a.avg_rate, a.moviename FROM temp_movie_avg_rating a JOIN temp_good_movie_year g ON a.year = g.year ORDER BY a.avg_rate ASC LIMIT 8;

实现思路：

先计算每部电影的平均评分
找出好片（评分>4.0）最多的年份
在该年份中找出评分最低的8部电影

6. 类型偏好分析：男性用户最喜欢的电影类型

最后一个任务分析1995年男性用户最喜欢的电影类型，涉及复杂类型处理和精度调整：

-- 创建临时表存储1995年男性用户的评分 CREATE TEMPORARY TABLE IF NOT EXISTS temp_movies AS SELECT r.userid, m.movietype, r.rate FROM t_ratings r JOIN t_movies m ON r.movieid = m.movieid JOIN t_user u ON r.userid = u.userid WHERE u.sex = 'M' AND SUBSTRING(m.moviename, LENGTH(m.moviename) - 4, 4) = '1995'; -- 计算每种类型的平均评分 SELECT exploded_table.movie_type, ROUND(AVG(rate), 2) AS avg_rating FROM temp_movies LATERAL VIEW EXPLODE(split(movietype, '[|]')) exploded_table AS movie_type GROUP BY exploded_table.movie_type ORDER BY avg_rating DESC LIMIT 1; -- 清理临时表 DROP TABLE IF EXISTS temp_movies;

技术亮点：