Hive 的 SQL 练习选什么数据集性价比最高我练下来的感受是影评数据几乎是最合适的选择。它自带用户、电影、评分、时间四个核心维度天然适合练多表关联、聚合统计、窗口函数、日期处理这些高频操作。影评案例既是 Hive SQL 练习的经典入门题也是很多数仓开发岗面试题的素材来源。这篇文章我会拆解一套完整的影评案例练习从建表、数据准备、基础清洗到窗口函数和复杂业务场景每一步都给出可直接运行的 Hive SQL并把我踩过的坑一并写出来。适合正在学 Hive 的初学者也适合准备转岗数仓开发的同学照着自测。1. 项目背景与需求拆解1.1 为什么选影评数据练手我见过很多人练 SQL 喜欢用订单表、用户表但那些数据总缺一点“业务感”。影评数据的妙处在于它天生就是一个简单的星型模型中间是评分事实表两边是用户维度和电影维度。你既要会查明细又得会按维度聚合还得能把“用户连续评分”这种问题翻译成 SQL。从技术点看影评案例几乎覆盖了日常数仓开发的高频操作基础查询过滤、排序、去重、空值处理聚合统计分组、均值、计数、双 GROUP BY多表关联事实表和维度表 JOIN窗口函数ROW_NUMBER、RANK、DENSE_RANK、LAG行转列split explode lateral view日期处理TO_DATE、DATE_FORMAT、DATE_SUB、DATEDIFF业务分析榜单、留存、连续行为、分层打标更重要的是影评数据结果容易验证。比如统计一部电影的平均评分你手算一下就能知道 SQL 对不对。这种“即时反馈”对建立信心特别有帮助。我用这套案例带过不少新人基本上从建表到连续评分那道题练完Hive SQL 的常见语法就能打通一大半。1.2 业务场景与需求清单我模拟的业务背景是某在线影视平台积累了一批用户对电影的评分记录现在需要做一轮 UV 层面的评分行为分析。整个练习拆成 7 个需求按难度递进编号需求描述核心考察点1统计每部电影的评分人数、平均分、最高分、最低分GROUP BY 聚合函数2找出评分人数 Top 10 的电影子查询 ORDER BY LIMIT3统计每个用户的评分次数、平均分、活跃天数COUNT DISTINCT TO_DATE4给每个用户按评分次数分层打标CASE WHEN5对每部电影按评分排名列出评分最高的用户ROW_NUMBER / RANK6计算每个用户相邻两次评分的时间间隔LAG DATEDIFF7判断每个用户是否存在连续 3 天评分行为DATE_SUB ROW_NUMBER这些需求看起来简单真写起来会踩不少语法坑。比如 Hive 里窗口函数和 GROUP BY 的执行顺序、LEFT JOIN 后 COUNT 字段的口径差异、日期字符串的格式统一每一个都是新手容易翻车的地方。1.3 数据表结构设计为了把多表关联练透我建议建三张表而不是一张宽表。三张表分别是用户表users电影表movies评分明细表ratings字段设计如下users user_id 用户ID user_name 用户昵称 gender 性别 city 所在城市 register_date 注册日期 movies movie_id 电影ID movie_name 电影名称 genre 电影类型多个类型用逗号分隔 release_year 上映年份 duration_min 时长分钟 ratings user_id 用户ID movie_id 电影ID rating 评分采用5分制支持0.5分 rating_time 评分时间格式 yyyy-MM-dd HH:mm:ss电影表里故意把genre设计成逗号分隔的多值字段是为了练 lateral view 行转列。这个设计在生产环境里偶尔会遇到影评数据用来练拆分比订单表自然得多。2. 建表与数据准备2.1 内部表还是外部表很多人在练习时不太在意 Hive 表的类型直接CREATE TABLE默认建的就是内部表。内部表和外部表的区别在于删除表时内部表会连同 HDFS 上的数据一起删除外部表只删除元数据数据文件还在。我建议在练习环境里也用外部表。原因很简单如果有天你写DROP TABLE时手滑内部表数据就没了重新造数很麻烦。外部表的数据还在 HDFS 上重新执行一次建表语句就能找回元数据。生产环境就更不用说了数据资产通常都在数仓外部目录必须用外部表管理。不过如果只是练语法内部表也不是不能用。关键是知道两者的边界。下面的建表脚本我用外部表并且把数据统一放在一个目录下。2.2 建表语句实例先建一个练习库然后建三张表。为了保证在普通 Hive 环境能直接执行这里用最基础的写法CREATE DATABASE IF NOT EXISTS movie_rating; USE movie_rating; CREATE EXTERNAL TABLE IF NOT EXISTS users ( user_id BIGINT COMMENT 用户ID, user_name STRING COMMENT 用户昵称, gender STRING COMMENT 性别, city STRING COMMENT 城市, register_date STRING COMMENT 注册日期yyyy-MM-dd ) COMMENT 用户信息表 ROW FORMAT DELIMITED FIELDS TERMINATED BY , STORED AS TEXTFILE LOCATION /warehouse/movie_rating/users; CREATE EXTERNAL TABLE IF NOT EXISTS movies ( movie_id BIGINT COMMENT 电影ID, movie_name STRING COMMENT 电影名称, genre STRING COMMENT 电影类型逗号分隔, release_year INT COMMENT 上映年份, duration_min INT COMMENT 时长分钟 ) COMMENT 电影信息表 ROW FORMAT DELIMITED FIELDS TERMINATED BY , STORED AS TEXTFILE LOCATION /warehouse/movie_rating/movies; CREATE EXTERNAL TABLE IF NOT EXISTS ratings ( user_id BIGINT COMMENT 用户ID, movie_id BIGINT COMMENT 电影ID, rating DECIMAL(2,1) COMMENT 评分5分制支持0.5, rating_time STRING COMMENT 评分时间yyyy-MM-dd HH:mm:ss ) COMMENT 评分明细表 ROW FORMAT DELIMITED FIELDS TERMINATED BY , STORED AS TEXTFILE LOCATION /warehouse/movie_rating/ratings;字段类型有两点值得注意。第一rating用DECIMAL(2,1)而不是FLOAT因为浮点计算容易出 4.9000001 这种结果后面 AVG 和 ROUND 会很难看。第二日期时间我用STRING存因为 Hive 对复杂时间格式的 TO_DATE 解析能力很强字符串反而灵活。只要保证格式统一后续用TO_DATE(rating_time)就能取到日期部分。2.3 准备模拟数据并导入练习数据不需要太大但要有“脏”数据。我当初造了 500 条评分记录覆盖 20 部电影、30 个用户故意在 ratings 里塞了一些重复记录和异常评分就是为了后面能练去重和清洗。数据文件用 CSV 格式逗号分隔注意不要有表头。分别放到本地目录后通过 Hive 的 load 命令导入hive -e LOAD DATA LOCAL INPATH /tmp/data/users.csv INTO TABLE movie_rating.users; hive -e LOAD DATA LOCAL INPATH /tmp/data/movies.csv INTO TABLE movie_rating.movies; hive -e LOAD DATA LOCAL INPATH /tmp/data/ratings.csv INTO TABLE movie_rating.ratings;导入完成后先做三件事看总行数、看重复、看空值。SELECT COUNT(*) FROM movie_rating.ratings; SELECT user_id, movie_id, COUNT(*) AS cnt FROM movie_rating.ratings GROUP BY user_id, movie_id HAVING COUNT(*) 1; SELECT COUNT(*) AS null_rating_cnt FROM movie_rating.ratings WHERE rating IS NULL OR rating NOT BETWEEN 0 AND 5;这一步极其重要。很多人在练习时直接进入统计等结果对不上才怀疑数据有问题。实际上造数时故意留一些重复和脏值能让后面的去重练习有真实对象而不是为了写函数而写函数。2.4 数据质量体检的细节查看总行数时我更喜欢用COUNT(*)而不是COUNT(1)两者结果一致但COUNT(*)更直观。如果需要看明细建议先SELECT * ... LIMIT 10确认字段顺序对不对因为 CSV 导入是按位置匹配的字段顺序错了会整列污染。另一个容易忽略的是字段空值。COUNT(rating)不会统计 NULL而COUNT(*)会计数所有行。这个差异在 LEFT JOIN 场景里特别容易踩坑我后面会专门讲。数据体检阶段只要确认异常范围不用急着清洗清洗动作放到正式练习里做。3. 基础 SQL 练习从清洗到统计3.1 排重与空值处理在实际业务里用户对同一部电影多次评分是有可能的比如先打了低分看完全片后又改高分。业务上一般取最新一次评分。Hive 里最通用的排重方式是ROW_NUMBERSELECT user_id, movie_id, rating, rating_time FROM ( SELECT user_id, movie_id, rating, rating_time, ROW_NUMBER() OVER (PARTITION BY user_id, movie_id ORDER BY rating_time DESC) AS rn FROM movie_rating.ratings ) t WHERE rn 1;这里的关键点是PARTITION BY user_id, movie_id它定义了一部电影一个用户的唯一组合ORDER BY rating_time DESC保证最新时间排在前面rn1 就是最新评分。如果你想保留最早评分改ASC即可。空值处理要特别注意业务口径。很多人习惯用NVL(rating, 0)把空值补成 0这其实很危险。因为某个用户确实没给评分补 0 等于人为拉低平均分。更合理的做法是分析平均分时过滤掉空值或者在填充前先看一眼空值比例。如果是测试数据直接过滤掉最干净SELECT * FROM movie_rating.ratings WHERE rating IS NOT NULL AND rating_time IS NOT NULL;3.2 电影维度统计先做最简单的聚合需求统计每部电影的评分人数、平均分、最高分、最低分。SELECT m.movie_id, m.movie_name, COUNT(r.rating) AS rating_cnt, ROUND(AVG(r.rating), 2) AS avg_rating, MAX(r.rating) AS max_rating, MIN(r.rating) AS min_rating FROM movie_rating.movies m LEFT JOIN movie_rating.ratings r ON m.movie_id r.movie_id GROUP BY m.movie_id, m.movie_name HAVING COUNT(r.rating) 0 ORDER BY avg_rating DESC;我特意用了LEFT JOIN而不是INNER JOIN目的是保留所有电影包括那些还没有人评分的电影。注意COUNT(r.rating)不会统计右表为 NULL 的行所以没评分的电影 rating_cnt 是 0恰好符合业务语义。如果你写成COUNT(*)没评分的电影会因为左表字段补 NULL 被算成 1 行结果就错了。找出评分人数 Top 10 电影可以直接在外面套一层子查询SELECT movie_id, movie_name, rating_cnt, avg_rating FROM ( SELECT m.movie_id, m.movie_name, COUNT(r.rating) AS rating_cnt, ROUND(AVG(r.rating), 2) AS avg_rating FROM movie_rating.movies m LEFT JOIN movie_rating.ratings r ON m.movie_id r.movie_id GROUP BY m.movie_id, m.movie_name ) t ORDER BY rating_cnt DESC LIMIT 10;注意 Hive 对非聚合列的 GROUP BY 要求很严格SELECT里的非聚合字段必须全部出现在 GROUP BY 中。所以不能只GROUP BY m.movie_id然后查m.movie_name。3.3 用户维度统计用户维度重点看活跃度和评分偏好。一条 SQL 能统计出每个用户的评分次数、平均分、活跃天数和首次/末次评分时间SELECT user_id, COUNT(*) AS rating_cnt, ROUND(AVG(rating), 2) AS avg_rating, COUNT(DISTINCT TO_DATE(rating_time)) AS active_days, MIN(rating_time) AS first_rating_time, MAX(rating_time) AS last_rating_time FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY user_id ORDER BY rating_cnt DESC;TO_DATE(rating_time)会把2023-05-01 12:30:00转成2023-05-01然后COUNT(DISTINCT ...)就能得到活跃天数。这里我用的是清洗后的数据所以不需要再写NVL之类的东西。再做一层用户分层按评分次数分成高频、中频、低频SELECT t.user_id, t.rating_cnt, CASE WHEN t.rating_cnt 20 THEN 高频用户 WHEN t.rating_cnt 10 THEN 中频用户 ELSE 低频用户 END AS user_level FROM ( SELECT user_id, COUNT(*) AS rating_cnt FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY user_id ) t;这个 CASE WHEN 语法很简单但要注意边界值。我习惯把20和10分开这样不会出现重叠。边界条件写反了会导致分类不正确比如WHEN rating_cnt 20会把恰好 20 条的用户漏出去。4. 进阶 SQL 练习窗口函数与多表关联4.1 三种排序函数实操对比窗口函数是 Hive SQL 进阶的分水岭。影评案例里最常见的需求是“每部电影评分最高的用户是谁”。先看三个函数放一起的效果SELECT movie_id, user_id, rating, ROW_NUMBER() OVER (PARTITION BY movie_id ORDER BY rating DESC) AS rn, RANK() OVER (PARTITION BY movie_id ORDER BY rating DESC) AS rk, DENSE_RANK() OVER (PARTITION BY movie_id ORDER BY rating DESC) AS dr FROM movie_rating.ratings WHERE rating IS NOT NULL;三者区别如下ROW_NUMBER()按顺序排不管分数是否相同名次一定不重复。RANK()分数相同时名次相同但下一个名次会跳跃。比如两个并列第一下一个就是第三。DENSE_RANK()分数相同时名次相同且下一个名次不跳跃。比如两个并列第一下一个还是第二。实际业务中如果只是取固定的业务行比如“每部电影评分最高的用户”通常用 ROW_NUMBER。如果榜单强调并列关系比如“评分最高的三名用户”用 DENSE_RANK 更合理。很多面试官喜欢问这个区别所以建议把三条 SQL 都跑一遍用 LIMIT 看结果记忆会非常牢。取每部电影评分最高的用户SELECT movie_id, user_id, rating FROM ( SELECT movie_id, user_id, rating, ROW_NUMBER() OVER (PARTITION BY movie_id ORDER BY rating DESC, rating_time DESC) AS rn FROM movie_rating.ratings WHERE rating IS NOT NULL ) t WHERE rn 1;这里我多加了rating_time DESC作为并列时的次级排序保证同一个用户如果多次评分也能得到确定结果。4.2 lag 看用户的评分节奏“计算每个用户相邻两次评分的时间间隔”这类问题用LAG非常方便。LAG(rating_time, 1)表示取当前行之前的一行也就是上一次评分时间SELECT user_id, rating_time, LAG(rating_time, 1) OVER (PARTITION BY user_id ORDER BY rating_time) AS prev_rating_time FROM movie_rating.ratings WHERE rating IS NOT NULL;然后在外层算时间差SELECT user_id, ROUND(AVG(day_diff), 1) AS avg_interval_days FROM ( SELECT user_id, DATEDIFF( TO_DATE(rating_time), TO_DATE(LAG(rating_time, 1) OVER (PARTITION BY user_id ORDER BY rating_time)) ) AS day_diff FROM movie_rating.ratings WHERE rating IS NOT NULL ) t WHERE day_diff IS NOT NULL GROUP BY user_id;这里有三个坑。第一每个用户的第一条评分记录没有前一行LAG 返回 NULLDATEDIFF 结果也是 NULL所以在聚合前要过滤day_diff IS NOT NULL。第二Hive 窗口函数不能直接嵌套在 DATEDIFF 的参数里实际上我上面的写法是可行的但如果你的 Hive 版本较老可以先在子查询里先算出prev_rating_time再在下一层算 DATEDIFF可读性更好。第三LAG的第三个参数可以指定默认值比如LAG(rating_time, 1, rating_time)但这里等于把第一条差值算成 0业务上没意义不如直接过滤。4.3 类型拆分与多表关联实战影评数据里一部电影通常有多个类型比如“动作,科幻”。要分析不同电影类型的评分偏好就得先把类型拆开。Hive 里的标准做法是splitexplodelateral view。我先把电影表拆成一行一个类型再关联评分表SELECT g.genre, COUNT(r.rating) AS rating_cnt, ROUND(AVG(r.rating), 2) AS avg_rating FROM ( SELECT movie_id, genre FROM movie_rating.movies LATERAL VIEW explode(split(genre, ,)) t AS genre ) g LEFT JOIN movie_rating.ratings r ON g.movie_id r.movie_id WHERE r.rating IS NOT NULL GROUP BY g.genre ORDER BY rating_cnt DESC;如果某个电影类型写成“动作, 科幻”中间带空格split(genre, ,)会把空格也带进来导致同一个类型被拆成两个。稳妥的办法是拆完后再用TRIM清理SELECT movie_id, TRIM(genre) AS genre FROM movie_rating.movies LATERAL VIEW explode(split(genre, ,)) t AS genre;这类行转列操作在日志解析、标签拆解场景非常常见。影评数据的好处是你能直观看到“动画”类型平均分是不是真的比恐怖片高从而验证 SQL 是否正确。5. 复杂业务场景实战5.1 计算连续评分天数连续行为判断是 SQL 经典题Hive 里最通用的解法是“日期减行号”。思路分三步第一步把每个用户每天的多条评分记录去重成一条得到用户活跃日期表SELECT user_id, TO_DATE(rating_time) AS rating_date FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY user_id, TO_DATE(rating_time);第二步按用户分组对日期排序同时用 DATE_SUB 把日期减去行号SELECT user_id, rating_date, DATE_SUB(rating_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY rating_date)) AS date_group FROM ( SELECT user_id, TO_DATE(rating_time) AS rating_date FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY user_id, TO_DATE(rating_time) ) t;为什么连续日期的 date_group 会相同因为连续日期每天递增 1行号也递增 1相减之后自然保持不变。比如 1 号和 2 号1 号减 1 等于 12 月 31 号2 号减 2 也等于 12 月 31 号。一旦断档差值就会变化。第三步按 user_id 和 date_group 分组统计连续天数并过滤出连续 3 天及以上的用户SELECT user_id, COUNT(*) AS consecutive_days FROM ( SELECT user_id, rating_date, DATE_SUB(rating_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY rating_date)) AS date_group FROM ( SELECT user_id, TO_DATE(rating_time) AS rating_date FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY user_id, TO_DATE(rating_time) ) t ) t2 GROUP BY user_id, date_group HAVING COUNT(*) 3 ORDER BY consecutive_days DESC;这道题的训练价值不在于记住解法而在于理解“日期减行号”背后的数学逻辑。做完一遍后很多连续登录、连续打卡的题目都能套用。5.2 月度评分活跃度与月度 Top3 电影先按月统计评分次数和参与用户数SELECT DATE_FORMAT(TO_DATE(rating_time), yyyy-MM) AS month, COUNT(*) AS rating_cnt, COUNT(DISTINCT user_id) AS user_cnt FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY DATE_FORMAT(TO_DATE(rating_time), yyyy-MM) ORDER BY month;注意 Hive 的DATE_FORMAT格式是yyyy-MM不要用YYYY-MM。虽然 Hive 某些版本能容忍但规范起见全用小写 yyyy。如果要做每个月的评分 Top 3 电影需要先用子查询按月聚合再用窗口函数排名SELECT month, movie_id, rating_cnt FROM ( SELECT month, movie_id, rating_cnt, ROW_NUMBER() OVER (PARTITION BY month ORDER BY rating_cnt DESC) AS rn FROM ( SELECT DATE_FORMAT(TO_DATE(rating_time), yyyy-MM) AS month, movie_id, COUNT(*) AS rating_cnt FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY DATE_FORMAT(TO_DATE(rating_time), yyyy-MM), movie_id ) t ) t2 WHERE rn 3 ORDER BY month, rn;这里能很明显看到窗口函数和 GROUP BY 的执行顺序窗口函数是在 GROUP BY 聚合之后计算的所以在窗口子查询里可以直接用 COUNT(*) 的结果但不能在窗口函数内引用一个还没有定义的别名rating_cnt需要重复写一遍聚合表达式。5.3 用户分层与类型偏好交叉分析最后做一个综合业务场景不同分层用户的电影类型偏好。这个场景要用到前面创建的用户分层结果以及类型拆分后的电影表。首先是把用户分层和类型拆分都封装成子查询SELECT lu.user_level, tg.genre, COUNT(*) AS rating_cnt FROM ( SELECT u.user_id, CASE WHEN t.rating_cnt 20 THEN 高频用户 WHEN t.rating_cnt 10 THEN 中频用户 ELSE 低频用户 END AS user_level FROM ( SELECT user_id, COUNT(*) AS rating_cnt FROM movie_rating.ratings WHERE rating IS NOT NULL GROUP BY user_id ) t JOIN movie_rating.users u ON t.user_id u.user_id ) lu JOIN ( SELECT r.user_id, g.genre FROM movie_rating.ratings r JOIN ( SELECT movie_id, TRIM(genre) AS genre FROM movie_rating.movies LATERAL VIEW explode(split(genre, ,)) t AS genre ) g ON r.movie_id g.movie_id WHERE r.rating IS NOT NULL ) tg ON lu.user_id tg.user_id GROUP BY lu.user_level, tg.genre ORDER BY lu.user_level, rating_cnt DESC;这个 SQL 比较长但拆开看并不复杂先得到“带层级的用户”再得到“用户对每个类型的评分记录”最后做 JOIN 和 GROUP BY。真正的复杂业务场景基本都是若干个子查询的堆叠关键是每一层都只做一件事。6. 常见问题与排查技巧6.1 空值、NULL 和 COUNT 的口径这是影评练习里出现频率最高的坑。第一COUNT(*)和COUNT(rating)在 LEFT JOIN 场景结果不同。统计“每部电影评分人数”时如果用COUNT(*)没评分电影也会被算成 1应该用COUNT(r.rating)或COUNT(1)过滤掉右表 NULL。第二ROUND(AVG(rating), 2)会自动忽略 NULL不需要手动WHERE rating IS NULL排除。第三筛选空值时不要写rating NULL要用IS NULL。这几个问题练完影评案例基本都能形成肌肉记忆。6.2 窗口函数常见报错窗口函数练习中常见两个问题。一是子查询里给窗口函数的结果加 WHERE 过滤比如WHERE rn 1Hive 不允许在同一层直接写必须额外嵌套一层。二是窗口函数里用了聚合结果别名比如ORDER BY COUNT(*) DESC这个可以写但如果你在窗口函数里引用已经定义好的列别名Hive 不一定支持稳妥做法是把聚合表达式重复写一遍。这两个点我在 5.2 的月度 Top3 里都实际用到了建议对照着看。6.3 数据倾斜与小文件影评数据里如果有一部现象级电影被大量评分按电影分组聚合时就会遇到数据倾斜。练习数据只有几百条看不出来但生产环境要具备这个意识。针对 GROUP BY 倾斜简单做法是加盐打散到多个并行任务再聚合一次针对 JOIN 倾斜如果小表能做内存就用 MAPJOIN。不过这些属于优化话题新手阶段不用硬啃先跑对结果更重要。小文件问题同样容易被忽略。频繁 INSERT 会产生大量小文件影响后续查询效率。如果练习环境允许可以在建表时设置TBLPROPERTIES或使用INSERT OVERWRITE合并小文件。最直接的习惯是练习结束后把中间临时表清掉不给 Hadoop 集群制造垃圾。6.4 SQL 自查方法我在带 A 同学做这套影评练习时最常提醒他的不是语法而是自查方式。第一写完一条复杂 SQL先加LIMIT 10看明细确认字段值符合预期再跑全量。第二遇到报错先用子查询把问题缩小到某一段不要整体查。第三善用EXPLAIN看执行计划能发现笛卡尔积、无效 JOIN 这类隐患。第四给所有表起别名尤其是多表 JOIN 和 lateral view 混用的时候没有别名的 SQL 几乎没法读。这些习惯比多背几个函数重要得多。练影评案例时我建议你把每道题都刻意写两遍第一遍用最直观的写法第二遍尝试用窗口函数或子查询优化。这样你才能真正理解不同写法的性能差异和可读性差异。整套影评案例做下来我个人最受用的不是学会某条具体函数而是把业务问题拆解成 SQL 步骤的方法。A 同学每次卡住时我都会让他先把“我要算什么”用一句话说清楚再翻译成“从哪张表、过滤什么、按什么分组、用什么窗口”。重复几次Hive SQL 就熟练了。最后再分享一个小技巧练习数据不要造得太干净故意留一些重复评分、缺失值和异常时间这样排重的坑、空值的坑、日期解析的坑你都会真实踩一遍比看十篇文档都有用。