从电影评分数据实战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;技术亮点LATERAL VIEW EXPLODE处理管道分隔的类型字段split函数将字符串拆分为数组平均评分计算和四舍五入处理在实际项目中处理电影评分数据时我发现最常遇到的挑战是数据质量问题。比如电影年份格式不一致、类型标签不规范等这些问题往往需要花费大量时间进行数据清洗。