Hive SQL进阶练习：用影评数据分析覆盖七大高频考点

做HIVE综合练习的时候，不少人习惯拿订单表或者网站日志练手，可纯订单表的结构太单一，练完聚合和关联就觉得没什么可深挖的了。我最近把影评案例完整跑了一遍，从原始文本数据的清洗、建表、装载，到多表关联、窗口排名、留存分析全部走通，体验下来发现，这种“用户-内容-反馈”三层结构的数据集比订单数据更适合用来做SQL练习：表与表之间的关系直观，业务含义清楚，几乎能覆盖Hive SQL的高频考点，练习完的成就感也明显更高。这篇文章就把这套影评案例的完整练习思路、建表方案、练习题目和踩坑记录整理出来，给正在刷Hive练习题的同学一份可以直接照着做的参考。

1. 影评数据的业务结构：为什么这个题材拿来做Hive练习很耐打

1.1 三层事实关系天然贴近真实业务

影评案例通常由三部分数据组成：用户信息、电影信息和用户的评分评论行为。其中用户与电影之间是多对多关系，一个用户可以给多个电影打分，一部电影可以收到多个用户的评价，这个关系在业务上非常常见，和电商里的“用户-商品-订单”、内容平台里的“用户-内容-阅读记录”本质上是同一类模型。用影评数据练习Hive SQL，本质上就是在练习处理这类真实业务模型。

三层结构的好处在于，它能同时支撑多种类型的SQL练习。单表聚合、多表关联、日期维度分析、排名开窗、去重、空值处理、数据倾斜模拟，都能在同一个数据集里展开，不像订单表那样只有销售金额和数量两个维度可挖。

我记得第一次拿到影评数据的时候，原始文件大概有几百万条评分记录、几万个用户、几千部电影。这个量级放在本地MySQL里可能已经很卡，但在Hive里刚好合适：既能跑出大数据场景下的执行计划，又不会因为数据量过大导致练习成本太高。对于练习Hive的人来说，这个规模是很理想的“练习场”。

1.2 影评案例覆盖的知识点全景

我习惯把Hive SQL的练习维度分成七个层级，影评数据在这七个层级上都有对应的练习场景：

这七个层级并不是孤立的，真实场景里经常要叠加使用。比如“统计每个用户最近一个月评过分的电影里，评分最高的那一部是什么”，就是把时间过滤、子查询、窗口排名组合在一起的综合题。这种组合能力，恰恰是影评数据集最能练出来的东西。

1.3 和订单数据练手相比的差异点

用订单数据练Hive，一个普遍的问题是“用户-商品”的关系虽然存在，但订单往往有多个状态字段（待支付、已支付、已退款），练关联时容易把业务状态搞混淆，反而干扰SQL本身的练习重点。影评数据则简单得多：评分只代表一次评价行为，评分值本身是数值，评论内容是文本，几乎没有状态流转的复杂性。

另一个差异是时间分布。订单的发生时间相对集中（比如大促期间数据激增），而影评数据的时间分布更均匀，能自然地观察长期趋势，这对练习时间函数的处理非常友好。我后来带新人做练习时，也优先推荐影评类数据而不是订单数据，原因就是这个题材“看起来简单，但练下去有很多层”。

2. 建表这一步做对了，后面才能少返工

2.1 三张核心表的字段设计与字段类型选择

影评案例的表结构通常包括用户表、电影表和评分表。命名上我习惯加前缀t_，表示这是练习用的业务表，避免和系统表混淆。三张表的字段设计如下：

CREATE TABLE t_user ( uid BIGINT COMMENT '用户ID', user_name STRING COMMENT '用户昵称', gender STRING COMMENT '性别', register_date STRING COMMENT '注册日期' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

CREATE TABLE t_movie ( mid BIGINT COMMENT '电影ID', movie_name STRING COMMENT '电影名称', genre STRING COMMENT '电影类型', release_year STRING COMMENT '上映年份', country STRING COMMENT '制片国家/地区', director STRING COMMENT '导演' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

CREATE TABLE t_rating ( rid BIGINT COMMENT '评分记录ID', uid BIGINT COMMENT '用户ID', mid BIGINT COMMENT '电影ID', rating DOUBLE COMMENT '评分值', comment_time STRING COMMENT '评分时间', comment_content STRING COMMENT '评论内容' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

字段类型选择上，我特别强调一个点：时间字段先用STRING，不要直接定义成TIMESTAMP或DATE。原因有两点。第一，原始文本数据里的时间格式经常不统一，有的是2022-01-01 12:30:00，有的是2022/1/1，直接按TIMESTAMP装载很容易报错。第二，Hive对STRING类型的处理最宽容，后面需要算日期差、按月聚合时，再用from_unixtime、to_date、date_format等函数去转换，可控性更强。把转换动作放在查询阶段，而不是装载阶段，是我踩过不少坑之后总结出来的习惯。

rating字段定义为DOUBLE而不是INT，是因为原始评分数据中会出现4.5、3.5这样的半分制评分，如果用INT定义，装载时Hive会做隐式转换，造成精度损失。

2.2 分隔符、空值和文本数据清洗

建表时最不起眼但对后期查询影响最大的，是分隔符的处理。原始影评数据如果是CSV格式，字段之间的逗号分隔很正常，但评论内容本身可能包含逗号甚至换行符。如果直接按逗号分隔装载，评论字段会被截断，导致后续统计评论长度、关键词过滤时数据全是乱的。

我处理这类问题的步骤是这样的：先用预处理脚本把原始CSV清洗成以\001（ASCII码中的SOH字符）作为分隔符的文件，再建表时指定FIELDS TERMINATED BY '\001'。这种清洗脚本在真实数仓项目中也是通用做法，因为\001在正常文本中几乎不会出现，能有效规避字段内容包含分隔符的问题。

Hive装载时另一个容易忽视的地方是空值。CSV文件里的空字段可能表现为两个连续逗号（比如1,,男），装载后Hive会把它识别成空字符串''，而不是NULL。不做处理的话，后续WHERE register_date IS NULL根本查不出这些记录，统计结果就会出现偏差。业界常用的口诀是：NULL在Hive里是\N，文件里的空值要么在清洗阶段统一写成\N，要么查询时用IF(field = '', NULL, field)转换。这个细节在练习阶段就可能直接影响结果正确性。

2.3 是否需要分区表：练习场景下的取舍

真实生产环境里，上千万行的大表通常会按日期做分区，查询时通过分区裁剪大幅降低扫描量。影评案例的数据量级一般只有几百万行，分区带来的收益不明显，反而会增加装载和查询的复杂度。所以练习阶段我倾向于不建分区表，所有数据放一个目录里，先把SQL逻辑练扎实。

如果之后想体验一下分区表的效果，可以在评分表中增加partition_date STRING分区字段，并修改装载数据的语句：

LOAD DATA INPATH '/exercise/movie_rating/dt=2024-06-01' INTO TABLE t_rating PARTITION (partition_date='2024-06-01');

这样做的好处是能对比有分区和没分区时EXPLAIN执行计划的差异，直观理解分区裁剪的意义。但日常练习不建议一上来就加分区，否则每次查询都要带上分区过滤条件，反而把练习重点从SQL逻辑带偏到表设计上去了。

3. 我设计的七大练习场景：从单表聚合到窗口排名

3.1 第一层：评分分布与基础聚合

基础层练习的重点是让SQL语法和函数手感和起来。我设计的第一个题目是统计评分分布：查询每个评分值（1分到5分）分别有多少条记录，并计算平均评分。

SELECT rating, COUNT(*) AS rating_cnt, ROUND(AVG(rating) OVER(), 2) AS avg_rating FROM t_rating GROUP BY rating;

这个题目看起来简单，实际上有个值得展开的地方：AVG(rating) OVER()是窗口聚合，和GROUP BY混在一起用，初学者容易混淆它们的执行顺序。跑通了这条语句，就会理解GROUP BY先对评分值分组，窗口函数在分组后的结果集上计算全局平均值。这个理解对后面叠加多层窗口函数很有帮助。

第二个练习是找出评分人数最多的前20部电影，要求输出电影名称、评分人数和平均分。这个题目练习了JOIN和聚合的组合，也是后面排名题的地基。

3.2 第二层：好电影识别与多表关联

多表关联是Hive练习的重点。入门题是：关联t_rating和t_movie，查每部电影的平均评分和评分人数，保留平均分不低于4分且评分人数不少于50的电影。

SELECT m.movie_name, m.genre, COUNT(r.rid) AS rating_cnt, ROUND(AVG(r.rating), 2) AS avg_rating FROM t_movie m LEFT JOIN t_rating r ON m.mid = r.mid GROUP BY m.movie_name, m.genre HAVING AVG(r.rating) >= 4 AND COUNT(r.rid) >= 50 ORDER BY avg_rating DESC;

注意这里用了LEFT JOIN而不是INNER JOIN，因为有些新上线的电影可能还没有收到任何评分，如果用内连接，这些电影会直接消失，统计出来的“电影总数”会少于实际。练习时最常犯的错误，就是不分场景一律用INNER JOIN，导致结果数据集不完整。

关联主题还能延伸到“用户-电影偏好”分析：查询同一个电影类型下，男性用户和女性用户的平均评分差异。这就需要在关联的基础上加CASE WHEN分组，逻辑上更绕一层，但很贴近真实业务里的对比分析需求。

3.3 第三层：月份维度的时间函数组合

时间维度的练习，我用的是“按月份统计评分数量和平均分”这道题。

SELECT DATE_FORMAT(comment_time, 'yyyy-MM') AS month, COUNT(*) AS rating_cnt, ROUND(AVG(rating), 2) AS avg_rating FROM t_rating GROUP BY DATE_FORMAT(comment_time, 'yyyy-MM') ORDER BY month;

大多数练习教程止步于按月聚合，但实际业务里日历表和自然月的对齐问题往往更折磨人。我在这组练习里加了一个进阶题：统计“每部电影上映后第一个月内的评分数量”，这需要拼接上映年份和月份，构造时间区间，再和评分时间做比较。

这个题目看似只是WHERE条件加一段字符串处理，但考验的是把两个不同格式的时间字段对齐的能力，非常接近真实数仓开发中经常遇到的“埋点时间对齐业务时间”的场景。

3.4 第四层：RANK类窗口函数与TOP-N

窗口函数是Hive面试的高频考点，影评数据用来练排名非常合适。我设计的题目是：找出每个电影类型下评分人数最多的前3部电影。

SELECT genre, movie_name, rating_cnt, rk FROM ( SELECT m.genre, m.movie_name, COUNT(r.rid) AS rating_cnt, ROW_NUMBER() OVER (PARTITION BY m.genre ORDER BY COUNT(r.rid) DESC) AS rk FROM t_movie m JOIN t_rating r ON m.mid = r.mid GROUP BY m.genre, m.movie_name ) t WHERE rk <= 3;

这里有个容易被忽略的细节：窗口函数的ORDER BY用的是COUNT(r.rid)这个聚合结果，而不是某个具体字段。这种“聚合后再开窗”的用法，很多初学者第一次见到会反应不过来，但它在真实业务中非常通用。PARTITION BY m.genre表示按类型分组排名，ROW_NUMBER()会保证同组内排名不重复。

如果要区分RANK、DENSE_RANK和ROW_NUMBER的差异，影评数据也是很好的载体。比如统计评分值分布时，用RANK和DENSE_RANK对出现次数相同的评分值进行排名，结果会有明显区别，对比着练习印象会很深。

3.5 第五层：连续行为与LAG/LEAD窗口

连续行为分析是影评案例里最出彩的部分，因为它能映射到真实业务里的“连续登录天数”问题。题目是：找出连续3天及以上都有评分行为的用户。

SELECT uid, group_flag, COUNT(*) AS continuous_days FROM ( SELECT uid, comment_date, DATE_SUB(comment_date, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY comment_date)) AS group_flag FROM ( SELECT uid, TO_DATE(comment_time) AS comment_date FROM t_rating GROUP BY uid, TO_DATE(comment_time) ) t1 ) t2 GROUP BY uid, group_flag HAVING COUNT(*) >= 3;

这套解法是连续行为分析里的经典写法，核心思路是“日期减去行号”的分组技巧：如果一个用户连续N天有评分行为，那么这些日期分别减去对应的行号后，得到的日期是同一个值，从而形成一个组。GROUP BY这个组标记后，COUNT(*)就是连续天数。

这个技巧在真实业务里经常用在“连续签到N天”“连续活跃N天”等场景，属于面试和实战双高频。用影评数据来练，比凭空造日期数据更有代入感，因为评分时间字段本身就是真实存在的业务时间。

3.6 第六层：留存率分析

留存率练习的题目是：统计某个月有评分行为的用户中，多少人次月在次月继续产生评分行为。这个指标在内容平台里非常关键。

SELECT base_month, retained_cnt / base_cnt AS retained_rate FROM ( SELECT base_month, COUNT(DISTINCT base_uid) AS base_cnt, COUNT(DISTINCT retain_uid) AS retained_cnt FROM ( SELECT a.uid AS base_uid, b.uid AS retain_uid, DATE_FORMAT(a.comment_time, 'yyyy-MM') AS base_month, DATE_FORMAT(b.comment_time, 'yyyy-MM') AS retain_month FROM ( SELECT uid, comment_time FROM t_rating WHERE DATE_FORMAT(comment_time, 'yyyy-MM') = '2023-01' ) a LEFT JOIN ( SELECT uid, comment_time FROM t_rating WHERE DATE_FORMAT(comment_time, 'yyyy-MM') = '2023-02' ) b ON a.uid = b.uid ) t1 GROUP BY base_month ) t2;

这个练习的关键在于LEFT JOIN和COUNT(DISTINCT)的组合：用1月的评分用户作为基数，LEFT JOIN到2月的评分记录，能关联上的就是留存用户，COUNT(DISTINCT)避免同一用户在同一个月内多次评分的重复计数。跑完这条语句之后，对“留存率为什么要用DISTINCT去重”会有感性认识。

3.7 第七层：综合场景与子查询嵌套

最后一层是综合题，我出的是“统计每个用户评分过的电影中，评分值高于该用户平均评分2倍以上的电影数量”。这道题需要先算出每个用户的平均评分，再回到评分明细里比较每个单条评分的值。

SELECT uid, COUNT(CASE WHEN rating > 2 * avg_rating THEN 1 END) AS high_rating_cnt FROM ( SELECT uid, rating, AVG(rating) OVER (PARTITION BY uid) AS avg_rating FROM t_rating ) t GROUP BY uid;

这道题把所有核心知识点都串起来了：子查询、窗口函数、CASE WHEN、GROUP BY。我每次带练到这里，都会特意强调一个思维转变——不要试图在一条SQL里一步到位，先把要计算的中间结果物化为子查询，再在外层做过滤和聚合，逻辑会清晰很多。这种“先算窗口、再过滤”的思路，正是Hive SQL复杂查询的核心套路。

4. 练习过程中踩过的坑：时间格式、空值关联和数据倾斜

4.1 时间字段格式不一致引发的统计偏差

第一次练习时，我发现一个月的评分记录数量比预期少了将近二三十个百分点，排查了很久才发现，原始数据文件里时间格式不是统一的。一部分是2023-01-15 10:20:00，另一部分是2023/1/15 10:20:00甚至20230115，TO_DATE函数对前两种格式返回值正常，但对20230115这种纯数字格式会返回NULL，导致这些记录被WHERE TO_DATE(comment_time) IS NOT NULL过滤掉了。

解决方案是在清洗阶段统一时间格式，把原始时间字符串全部转成yyyy-MM-dd HH:mm:ss，再装进表里。如果数据已经装载，也可以在查询时做兼容转换：

SELECT uid, TO_DATE( CASE WHEN comment_time LIKE '%/%' THEN FROM_UNIXTIME(UNIX_TIMESTAMP(comment_time, 'yyyy/MM/dd HH:mm:ss'), 'yyyy-MM-dd HH:mm:ss') ELSE comment_time END ) AS comment_date FROM t_rating;

在练习阶段遇到这种问题，我会强烈建议返回数据清洗环节解决，而不要在每条SQL里都写一段转换逻辑。后者虽然能跑通，但既影响性能，也让SQL可读性变差。

4.2 数据倾斜在影评数据中的典型表现

影评数据里数据倾斜的问题非常典型。头部电影可能收到几百万条评论，长尾电影可能只有几条甚至没有评论。实际操作中，用一个GROUP BY movie_id统计评分量时，某些热门电影对应的Reduce Task处理时间会明显比其他Task长，整个作业的耗时被“拖后腿”。

遇到倾斜时，我常用的三种处理思路是：

1. 如果倾斜字段的取值基数不大，可以用GROUP BY mid, (CAST(rand() * N AS INT))先将数据随机分散到N个桶，再在外层对中间结果聚合；
2. 对参与关联的热点key，先在小表里用WHERE过滤掉，或者用MapJoin强制走Map端关联；
3. 练习场景下最简单直接的办法，是单独把热点key拎出来和普通key分开聚合，再用UNION ALL合并结果。

以影评数据为例，先将热门电影ID单独筛出来做一次聚合，再用UNION ALL把非热门数据的聚合结果拼接在一起，既能保证结果正确，也能明显缩短作业执行时间。

4.3 空值参与关联时的“假缺失”问题

影评数据中，部分评分记录里的uid字段可能是空值，这在实际生产里很常见（比如用户注销后ID被置空）。如果直接用INNER JOIN关联用户表和评分表，空值会被当作匹配不上的key直接丢弃；如果在GROUP BY时把空值和有效值混在一起，还会导致聚合结果分出一个“空值组”。

练习中最容易忽略的是空值参与COUNT(DISTINCT)时的行为差异。COUNT(DISTINCT uid)不会把NULL计入结果，但如果查询里先WHERE uid IS NOT NULL再做COUNT(DISTINCT)，结果虽然一致，执行计划却可能更优，因为过滤提前了。这个小细节在数据量变大以后，对作业性能的影响会非常明显。

另外一个容易被坑的地方是LEFT JOIN之后，右表字段为空相当于“无匹配”的信号，但不能简单当作业务上的“不存在”来理解。我习惯在练习总结里提醒自己：空值永远要区分“本身是空”和“以外键缺失导致关联出来的空”这两种情况。

5. 同一套练习里，顺手验证的Hive执行优化点

5.1 MapJoin和小表关联大表的顺序问题

在实际跑影评练习时，t_movie只有几千行，属于典型的小表。当SQL里出现t_movie JOIN t_rating时，Hive会自动把t_movie加载到Map端的内存里做MapJoin，避免走Reduce端Shuffle。这就是为什么练习时把“小表写在左边、大表写在右边”并不是绝对的，Hive的优化器会根据hive.auto.convert.join等参数自动判断，而不是机械地按书写顺序执行。

为了验证这个机制，我特意跑过带和不带/*+ MAPJOIN(m) */提示的两条等价SQL，对比了EXPLAIN输出。结果显示，加提示后执行计划里出现了Map Join Operator，Reduce阶段的Shuffle被整体跳过，作业耗时明显缩短。这种对比练习能帮助理解Hive优化器的工作原理，也能避免以后在真实项目里盲目调整SQL书写顺序。

5.2 分区裁剪和谓词下推的作用

不建分区的表做练习时，即使查询里只过滤某个月的数据，也要扫描整个数据目录。我后来复制了一份数据到分区表中，在EXPLAIN里面对比了过滤前后扫描的Partition数量，清晰看到分区裁剪能把扫描量从全表降到一个分区，效果非常直观。

谓词下推是另一个优化点。当JOIN条件里写了WHERE过滤时，Hive会把下推到表扫描阶段提前过滤，减少参与关联的记录数。影评数据的练习题里，“先过滤时间再关联”和“先关联再过滤时间”在逻辑上结果可能一样，但执行计划差异很大。我在带练时会让学员把两种写法各跑一遍，再用EXPLAIN看Filter算子的位置，很多人的执行计划理解就是在这时候建立起来的。

5.3 窗口函数使用时的资源控制

窗口函数在所有练习场景里最消耗资源，因为全量数据在每个分组内都要做排序。影评数据几百万行时还好，但如果自己扩展了数据量，ROW_NUMBER() OVER (PARTITION BY genre ORDER BY COUNT(...) DESC)这种写法可能产生大量的中间排序数据。

我的经验是：能先聚合缩小的数据，绝对不带着明细去开窗；能用子查询提前过滤的数据，绝对不在窗口函数外层再过滤。比如“每个类型下评分前3”这道题，先GROUP BY聚合出每部电影的评分量，再对每个类型开窗排名，而不是先对几百万条明细开窗排名再聚合，两者的性能差距在数据量放大后会非常明显。

另外，Hive窗口函数默认的窗口范围是分组的全部行，当需要计算累计值时，可以显式指定ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，避免默认全窗口带来的不必要的计算开销。这个参数很多人不知道，但在数据量大的时候，显式声明窗口边界能让执行计划走更高效的路径。

6. 这套影评练习做下来的心得与下一步扩展

整套影评案例我前后跑了三轮，每一轮都能发现新的可挖点。第一轮专注把七层练习跑通，第二轮开始在意每类SQL的执行计划和资源消耗，第三轮则是把所有练习题的作答逻辑统一收敛到最简版本。影评数据作为一个练习载体，最大的优点是可以反复使用，而且每次换一个分析角度，都能构建出全新的题目。

我个人在使用中发现，最有价值的扩展方向是给评分表增加评论文本字段的复杂分析，比如利用Hive内置的SUBSTRING_INDEX、REGEXP_EXTRACT等函数统计高频词、探查评分明显偏低的评论共性问题。这套案例再往下走，还可以把影评表升级为分区表、压到ORC格式，加上SNAPPY压缩，用真正的生产库表标准重跑一遍全部练习，建立一套“从业务问题到物理存储再到SQL逻辑”的完整链路。

给想要照着练的同学一个建议：不要满足于把题目“跑出结果”，每个练习至少追问自己三个问题——这条SQL的执行计划是什么样、Shuffle发生在哪一步、数据量放大十倍之后这条SQL还能不能跑。影评案例的真正价值不是答案本身，而是练出这种对SQL执行过程的敏感性。把这三问养成习惯，毕业之后面对真实数仓开发里的复杂报表，才不会被一句“数据查不出来”卡住。