三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

手把手教你用Hive SQL搞定电影评分数据分析（附完整代码与避坑指南）

发布时间：2026/10/26 8:13:46 三亩地 编程学习日记
手把手教你用Hive SQL搞定电影评分数据分析（附完整代码与避坑指南）

从零到一：用Hive SQL解锁电影评分数据的商业洞察力

当电影《泰坦尼克号》在1997年横扫全球票房时，很少有人意识到那些散落在各处的观众评分里藏着怎样的金矿。二十多年后的今天，数据分析师们正用SQL这把钥匙，打开电影评分数据的宝库。本文将带您亲历一场从原始数据到商业洞察的完整旅程，使用Hive SQL处理经典的MovieLens数据集，揭示那些藏在百万评分背后的秘密。

1. 环境准备与数据理解

在开始我们的数据分析之旅前，需要确保Hive环境配置正确。推荐使用CDH或HDP发行版，它们已经集成了Hive服务。对于本地测试，可以下载Apache Hive独立安装包：

# 下载Hive 3.1.2 wget https://downloads.apache.org/hive/hive-3.1.2/apache-hive-3.1.2-bin.tar.gz # 解压并配置环境变量 tar -xzvf apache-hive-3.1.2-bin.tar.gz export HIVE_HOME=/path/to/hive export PATH=$PATH:$HIVE_HOME/bin

MovieLens数据集通常包含三个核心表：

  • t_movies：电影基本信息（ID、名称、类型）
  • t_ratings：用户评分记录（用户ID、电影ID、评分、时间戳）
  • t_user：用户属性（性别、年龄、职业等）

创建这些表的Hive DDL如下：

CREATE EXTERNAL TABLE t_movies ( movieid INT, moviename STRING, movietype STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/data/movies'; CREATE EXTERNAL TABLE t_ratings ( userid INT, movieid INT, rate DECIMAL(2,1), `timestamp` BIGINT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/data/ratings'; CREATE EXTERNAL TABLE t_user ( userid INT, sex STRING, age INT, occupation INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LOCATION '/data/users';

注意：实际文件路径需要根据HDFS配置调整，DECIMAL(2,1)确保评分精度为1位小数

2. 基础分析：电影热度探针

2.1 单部电影评分统计

分析特定电影的受欢迎程度是基础中的基础。以下查询计算《Bad Boys (1995)》的评分次数：

SELECT m.movieid, m.moviename, COUNT(r.movieid) AS rating_count, ROUND(AVG(r.rate), 2) AS avg_rating 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;

这个查询揭示了几个关键业务指标：

  • rating_count：反映电影曝光量和观众参与度
  • avg_rating：衡量电影质量的核心KPI

2.2 年度电影热度趋势

通过提取电影名称中的年份信息，我们可以分析不同年份的电影热度：

SELECT REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1) AS year, COUNT(*) AS rating_count, COUNT(DISTINCT m.movieid) AS movie_count, ROUND(COUNT(*)/COUNT(DISTINCT m.movieid), 1) AS avg_rating_per_movie FROM t_movies m JOIN t_ratings r ON m.movieid = r.movieid WHERE moviename RLIKE '\\(\\d{4}\\)$' GROUP BY REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1) ORDER BY year;

这里使用了更健壮的REGEXP_EXTRACT函数替代SUBSTRING，避免对电影名称格式的强依赖。该查询输出三个维度：

  1. 每年总评分次数
  2. 每年被评分的电影数量
  3. 每部电影平均获得的评分数量

3. 用户画像分析

3.1 性别维度的观影差异

不同性别用户的观影行为往往呈现显著差异。以下查询统计1995年电影在不同性别用户中的评分分布：

SELECT u.sex, COUNT(*) AS rating_count, ROUND(AVG(r.rate), 2) AS avg_rating, PERCENTILE_APPROX(r.rate, 0.5) AS median_rating FROM t_user u JOIN t_ratings r ON u.userid = r.userid JOIN t_movies m ON m.movieid = r.movieid WHERE REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1) = '1995' GROUP BY u.sex;

我们引入了PERCENTILE_APPROX函数计算评分中位数，避免极端值对平均评分的影响。实际分析中，中位数往往比平均数更能反映典型用户的评分倾向。

3.2 年龄分层的评分模式

用户年龄是另一个关键维度。以下查询展示不同年龄段用户的评分特征：

SELECT CASE WHEN age < 18 THEN 'Under 18' WHEN age BETWEEN 18 AND 24 THEN '18-24' WHEN age BETWEEN 25 AND 34 THEN '25-34' WHEN age >= 35 THEN '35+' END AS age_group, COUNT(*) AS rating_count, ROUND(AVG(rate), 2) AS avg_rating, STDDEV(rate) AS rating_stddev FROM t_user u JOIN t_ratings r ON u.userid = r.userid GROUP BY CASE WHEN age < 18 THEN 'Under 18' WHEN age BETWEEN 18 AND 24 THEN '18-24' WHEN age BETWEEN 25 AND 34 THEN '25-34' WHEN age >= 35 THEN '35+' END;

提示：STDDEV函数计算评分标准差，反映用户评分的离散程度。标准差越大，说明该群体评分分歧越大

4. 高级分析技术

4.1 电影类型偏好分析

电影类型分析需要特殊处理，因为一部电影可能属于多个类型（如"动作|冒险"）。Hive的LATERAL VIEW EXPLODE结合split函数可以优雅解决这个问题：

SELECT movie_type, COUNT(DISTINCT r.userid) AS user_count, ROUND(AVG(rate), 4) AS avg_rating -- 保留4位小数避免精度问题 FROM t_ratings r JOIN t_movies m ON r.movieid = m.movieid LATERAL VIEW EXPLODE(SPLIT(m.movietype, '\\|')) t AS movie_type WHERE REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1) = '1995' GROUP BY movie_type ORDER BY avg_rating DESC;

关于原文提到的精度问题（ROUND(AVG(rate) + 0.02, 2)），正确的解决方案应该是：

  1. 检查原始数据精度（确保rate字段是DECIMAL类型）
  2. 增加计算精度（先计算高精度平均值再四舍五入）
  3. 使用更精确的聚合函数：
-- 精确计算方案 SELECT movie_type, ROUND(SUM(rate)/COUNT(rate), 2) AS accurate_avg -- 避免AVG函数可能的精度问题 FROM ...

4.2 基于窗口函数的深度分析

窗口函数可以实现更复杂的分析逻辑。以下查询找出每年评分最高的电影：

WITH yearly_ratings AS ( SELECT REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1) AS year, m.movieid, m.moviename, COUNT(*) AS rating_count, ROUND(AVG(rate), 2) AS avg_rating, ROW_NUMBER() OVER (PARTITION BY REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1) ORDER BY AVG(rate) DESC, COUNT(*) DESC) AS rank FROM t_movies m JOIN t_ratings r ON m.movieid = r.movieid WHERE REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1) BETWEEN '1990' AND '2000' GROUP BY REGEXP_EXTRACT(m.moviename, '\\((\\d{4})\\)$', 1), m.movieid, m.moviename ) SELECT year, moviename, avg_rating, rating_count FROM yearly_ratings WHERE rank = 1 ORDER BY year;

这个查询展示了几个高级技巧：

  • 使用CTE（WITH子句）提高可读性
  • ROW_NUMBER()窗口函数实现分组排名
  • 多字段排序确保结果合理性（先按平均分，再按评分数量）

5. 性能优化实战

5.1 分区表设计

对于大规模数据集，合理的分区设计能显著提升查询性能。以下是优化后的表定义：

CREATE EXTERNAL TABLE t_ratings_optimized ( userid INT, movieid INT, rate DECIMAL(2,1) ) PARTITIONED BY (year INT, month INT) STORED AS ORC; -- 动态加载分区 SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict; INSERT INTO TABLE t_ratings_optimized PARTITION(year, month) SELECT userid, movieid, rate, YEAR(FROM_UNIXTIME(`timestamp`)) AS year, MONTH(FROM_UNIXTIME(`timestamp`)) AS month FROM t_ratings;

ORC格式+时间分区的组合，可以使查询速度提升5-10倍。对于时间范围查询：

-- 查询1995年第一季度的数据 SELECT COUNT(*) FROM t_ratings_optimized WHERE year = 1995 AND month BETWEEN 1 AND 3;

5.2 执行计划调优

理解Hive查询的执行计划是优化的关键。使用EXPLAIN命令分析查询：

EXPLAIN SELECT m.moviename, AVG(r.rate) FROM t_movies m JOIN t_ratings r ON m.movieid = r.movieid GROUP BY m.moviename;

重点关注：

  • JOIN策略：是否使用了MapJoin
  • 数据倾斜：是否有Reducer处理的数据量远大于其他
  • 分区裁剪：是否跳过了不必要分区的扫描

对于大表关联，可以强制使用MapJoin：

SET hive.auto.convert.join=true; SET hive.auto.convert.join.noconditionaltask=true; SET hive.auto.convert.join.noconditionaltask.size=100000000;

6. 数据质量保障

6.1 异常值检测

评分数据中常见的异常包括：

  • 同一用户对同一电影多次评分
  • 超出合理范围的评分值（如0分或6分）
  • 短时间内大量评分（刷分行为）

检测异常评分的查询示例：

-- 检测重复评分 SELECT userid, movieid, COUNT(*) AS cnt FROM t_ratings GROUP BY userid, movieid HAVING COUNT(*) > 1; -- 检测异常评分值 SELECT rate, COUNT(*) AS cnt FROM t_ratings GROUP BY rate ORDER BY cnt DESC;

6.2 数据一致性检查

确保电影引用完整性：

-- 查找评分记录中引用不存在的电影ID SELECT DISTINCT r.movieid FROM t_ratings r LEFT JOIN t_movies m ON r.movieid = m.movieid WHERE m.movieid IS NULL; -- 检查电影名称格式一致性 SELECT moviename FROM t_movies WHERE moviename NOT RLIEW '\\(\\d{4}\\)$' LIMIT 10;

在实际项目中，我会为这类数据质量检查创建专门的监控作业，定期运行并邮件告警异常情况。

编程学习 学习日记 实战经验
← 返回列表