做Hive的SQL练习很多人会卡在一个尴尬点上网上能找到的题目不少但大多是零零散散存的要么只有题目没有答案要么答案跑起来报错要么结果和题目对不上。前阵子我按Hive的典型考点整理了一套50道的经典练习题每道都带完整SQL和真实查询结果整个过程下来觉得收获很大今天把这套资料的拆解思路、核心考点和常见坑都梳理一遍给正在刷题的人一点参考。这套题适合谁刚学Hive、准备数据仓库岗位面试、或者工作里写HiveQL总觉得没底的人都可以拿它当自查清单。所有题目我都是放在公司测试环境的Hive上实际跑过的严格按Hive的方言逻辑来写不是拿MySQL的答案硬搬。这一点很重要后面我会专门讲Hive和MySQL在练习时那些让人摔跤的差异。1. 内容整体设计与思路拆解1.1 为什么是50道而不是20道或100道我在整理这套题之前先翻了近两年面经里Hive相关的SQL题又对照了部门新人入职培训的练习清单发现高频考点其实就是那么几类基础查询与过滤、分组聚合、多表关联、子查询、窗口函数、行列转换、自定义函数和动态分区。50道题刚好能把每类考点铺开又不至于像100道那样大量重复。分类和题量我是这么分配的题型分类题数考察重点基础查询与过滤8道where、distinct、like、正则、case when分组聚合与having8道group by、聚合函数组合、having过滤多表关联8道join类型选择、关联键设计、笛卡尔积陷阱子查询与集合操作6道嵌套子查询、in/exists、union语义窗口函数专项10道row_number、rank、lag、lead、sum over行列转换与复杂类型6道collect_set、explode、concat_ws、lateral view综合应用与函数4道日期函数、udf思路、多步骤推导窗口函数单独占10道是同类题型里最不能省的。很多SQL写得慢的人问题就出在窗口函数不熟同一个分组求topN能写出一长串join窗口函数一行解决。1.2 Hive SQL和MySQL的差异是出题的核心参考这类练习题最常见的问题是拿MySQL的练习改个表名就说是Hive题。实际操作中两者至少有三处明显区别直接影响答案写法第一是执行引擎的差别。Hive默认把SQL翻译成分布式任务跑MySQL是单机执行。这意味着Hive里很多看似能跑的语法实际跑起来会触发全表扫描练习时必须建立数据量大的思维否则写出的答案在测试环境跑几分钟都出不来还以为是死锁。第二是数据类型和函数的差异。Hive没有datetime类型常用string或timestamp替代字符串函数、日期函数的名字和参数顺序跟MySQL也有出入比如日期加减在Hive里是date_add和date_sub在MySQL里是date_add(date, interval 1 day)这种写法。第三是复杂数据类型的支持。Hive里有array、map、struct配合lateral view explode可以做非常灵活的行列转换这是MySQL完全无法覆盖的功能。50道题里专门留了几道这类题目就是想把Hive特有的处理思路练透。1.3 题目数据怎么设计才能练到点子上题目设计数据时我不建议直接用网上现成那种员工表、部门表就完了。我这次额外设计了一个订单流水表和用户维度表订单表里故意加入了重复订单号、空值分区、同一用户多笔消费等数据噪声。为什么要这样因为真实数仓里接触的数据脏得很练习时多处理这些脏数据面试或工作时遇到类似问题才不会慌。造数据也讲究技巧。比如为了练窗口函数特地让同一用户的多个订单分布在相邻月份这样求每个用户最近三笔订单时结果才有区分度为了练行转列故意让一个用户购买多种商品类别这样group by之后才能看到聚合效果。模拟数据只有几百行但能模拟出千万级数据才需要用到的SQL技巧。2. 核心考点细节解析与实操要点2.1 分组聚合group by的边界感是基础也最容易错分组聚合这8道题看着简单实际刷完发现最容易错的是select字段和group by字段的对应关系。很多人上学时用MySQL写惯了select name, count(*) from table这种写法到Hive里直接照搬结果既不报错也不符合预期原因就是没有理解Hive的严格模式。Hive有一个参数叫hive.mapred.mode如果设成strict那查询必须带上分区字段过滤条件group by查询出来的字段也必须是分组字段或聚合函数。很多生产环境为了防全表扫描会把这个参数打开练习时必须养成每个select列都在group by里出现过的肌肉记忆。举一个典型的练习例子统计每个品类的订单数、下单用户数和总金额。最直接的做法就是group by之后count和sum并行但这道题如果再加上每人买过多少个不同品类的条件就得先子查询去重再聚合这就把分组和去重结合起来了。这类题目我的建议是每道题至少手写两遍。第一遍求快第二遍刻意改变聚合函数的组合方式比如把count(distinct xxx)和sum(case when)同时用上确保脑子里对每个维度上哪些指标能放进同一层聚合有清晰判断。2.2 多表关联Hive里join的坑比想象中多多表关联的8道题覆盖了inner join、left join、right join、full join以及map join。很多新手以为Hive的join和MySQL差不多其实至少有两点要特别留心。关联键的数据类型必须完全一致。MySQL里int和string经常隐式转换Hive里两表关联键如果一边是int一边是string结果大概率会丢数据而且不报错。练习时我故意设计了一道user_id一边是int一边是string的题就是让大家感受一下这种静默错误有多恶心。join的顺序和过滤时机直接决定结果正确性。尤其在多表关联时是先过滤再关联还是先关联再过滤跑出来的行数可能差很远。标准做法是提前把每个子表需要的数据过滤完再和其他表关联这样既减少shuffle量也避免结果被放大。还有一个高频考点是关联时出现一对多的数据膨胀。比如用户表和订单表关联后用户信息会随着订单条数重复出现很多人一下子没反应过来以为数据重复了其实是正常现象。练习时我专门设计了一道题让用户维度信息伴随订单明细输出考察点就是你能不能预期并接受这种行数放大。2.3 窗口函数为什么能占十道题窗口函数这10道题是整个50道题的核心。它解决的场景很明确在不减少原行数的情况下给每一行附上分组内的统计值。很多聚合函数做不到的每一行都保留原始明细同时显示聚合结果窗口函数可以轻松做到。最常用的几个函数的写法差异要记清楚row_number()是生成连续不重复编号rank()是并列时跳号dense_rank()并列时不跳号。一道经典题是取每个用户最近三笔订单两种写法都试试看一下结果差异就能彻底理解这三个函数的区别。lag和lead也非常常见。lag(字段, n, 默认值)是取当前行往前n行的值lead是往后n行。面试里高频场景是计算每个用户相邻两笔订单的时间间隔这个不靠lag很难写得优雅。练习时我特意把同一个用户的订单打散在多个分区中考察的就是跨分区取上一条记录的逻辑。最后是sum(xx) over(partition by xx order by xx)这种累计求和场景。它和group by本质区别在于order by的存在让窗口有了流动的感觉可以计算累计值、平均值、占比等。实战里做留存分析、付费转化漏斗都会用这套写法值得反复练。2.4 行列转换lateral view explode组合拳行列转换是Hive相对MySQL最大的亮点也是练习中最容易卡壳的点。它的核心是explode函数把array或map里的元素炸成多行然后用lateral view把炸出来的结果和原表的其他字段关联上。一道典型的题目是把用户购买的商品列表列转行变成一行一个商品的明细。正确答案的关键是写出lateral view explode(商品数组) t as 商品这段语法再配合需要保留的用户id。很多新手一上来就写explode忘了lateral view结果直接报错这就是基本功不扎实。行转列用的是collect_set或collect_list加group by把同一分组下的多个值聚合成一个数组再用concat_ws转成字符串。这一正一反两道题放在相邻位置建议连着做做完你会明显感觉到Hive在处理一对多数据时的表达能力远强于普通的关系型数据库。还有一道进阶题是map类型的数据展开比如一行里存了品类:金额的map需要拆出每个品类对应的金额。这种数据在实际日志中非常常见算法那边经常传过来这样的半结构化数据能写对lateral view explode(map)的人不多练会了会很有竞争优势。2.5 日期与复杂函数处理日期函数是数仓日常取数的硬需求。Hive里最常用的是date_format把字符串转成yyyy-MM-dd格式date_add和date_sub做加减datediff计算日期差还有last_day、next_day这种针对周和月边界的函数。题目里我设计了一个统计每周新增用户数的需求需要用date_format和next_day配合找到每个日期所在周的周一。这道题看起来不难实际好多人栽在next_day的用法上因为next_day在Hive里有周起始日参数理解了这个函数才能准确算出本周一而不是下周一。复杂类型方面array的size、map的keys、struct的字段访问也要练。有一道题是统计每个用户平均购买的商品种类数就需要先把array字段去重再算size再把size结果汇总求平均涉及嵌套函数的使用这个对新手来说是很好的拔高训练。2.6 UDF和自定义函数的思路题50道练习题里专属于Hive的还有一个重要方向是UDF即用Java写自定义函数。纯SQL练不到这一点但面试常问。我在综合题里留了一道情景题如果需求要计算某个不规则时间段内的去重用户数而内置函数不支持你会怎么处理。这道题的思路是拆分先看能不能用SQL组合实现不能的话需要注册临时函数还是永久函数。一个通用的建议是能SQL搞定就不要写UDFUDF的开发和维护成本都不低尤其在分布式环境下函数必须打成jar包分发到所有节点出问题排查很麻烦。但有些场景非得用UDF不可比如复杂的字符串解析、加密算法对接。练习时知道什么时候该UDF什么时候不该比会写UDF更重要这决定了你在真实需求里能不能快速给出合理方案。3. 实操过程与核心环节实现细节3.1 建表与模拟数据准备拿到50道题后第一步不是急着写SQL而是先把表建出来数据导进去。这次练习的核心表一共四张用户维度表、部门表、订单流水表、商品维度表。我选的存储格式是ORC因为练习场景里查询居多ORC压缩比高、列式存储适合只取部分字段的场景。如果完全照搬网上那些SNAPPY压缩的TEXTFILE练习跑起来速度差异不大但养成用列式存储的习惯对生产环境有好处。建表语句里关键要指定PARTITIONED BY因为Hive里分区表是常态。哪怕就几百行模拟数据也建一个按月份的日期分区这样可以练到动态分区插入的写法。题目里有一道按月份统计订单金额如果表没有分区这个字段过滤都无从谈起。插入数据时我故意往订单表里插了少量user_id为null的记录还有几条重复订单号。这样在练习过程中各种空值处理和去重的需求才有的放矢否则你写的SQL里那些null判断完全是摆设。3.2 典型题目的完整SQL与查询结果分析这里选三道有代表性的题目完整演示从读题到SQL落地再到结果校验的实战过程。第一道是2024年每个自然月下单用户数环比变化。核心是先按月做用户数统计再通过lag函数拿到上个月的用户数。完整SQL如下SELECT month, user_cnt, LAG(user_cnt, 1) OVER (ORDER BY month) AS prev_user_cnt, ROUND((user_cnt - LAG(user_cnt, 1) OVER (ORDER BY month)) / LAG(user_cnt, 1) OVER (ORDER BY month), 4) AS mom_ratio FROM ( SELECT DATE_FORMAT(order_date, yyyy-MM) AS month, COUNT(DISTINCT user_id) AS user_cnt FROM dwd_order_detail WHERE order_date 2024-01-01 AND order_date 2024-12-31 GROUP BY DATE_FORMAT(order_date, yyyy-MM) ) t ORDER BY month;这里关键点是先聚合成每月用户数再做lag计算。如果你试图直接对明细数据lag根本算不出环比因为每个月有好多行。这种先聚合再开窗的思路是窗口函数很重要的应用模式。第二道是每个用户最近一笔订单的商品名称和下单时间。这道题考察row_number的用法SELECT user_id, order_time, goods_name FROM ( SELECT user_id, order_time, goods_name, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM dwd_order_detail WHERE order_time IS NOT NULL ) t WHERE rn 1;注意我特意加了where过滤空值。这个细节很多人会忽略如果没有过滤空值的order_time排序时会跑到最前面就会被当成最近订单选中结果明显错误。第三道是把用户购买的商品合并成一行按逗号分隔。这里用到collect_set还是collect_list有讲究SELECT user_id, CONCAT_WS(,, COLLECT_LIST(goods_name)) AS goods_list FROM dwd_order_detail GROUP BY user_id;collect_list会保留所有记录包括重复的商品collect_set会先去重再收集。具体选哪个取决于业务口径题目里如果要求购买过的商品种类就该用collect_set如果要求所有购买记录则用collect_list。这个区别面试时也是一个很好的追问点。3.3 查询结果校验的通用套路写完SQL后如何确认结果是正确的我有一套固定的校验流程任何练习题都适用。先数行数。比如每个用户最近一笔订单最终行数理论上应该等于用户去重数如果不等要么是row_number写错了要么是空值过滤导致的。数行数是最快的报错信号。再抽查数据。我建议每次跑完都随机选一个用户或一个月份把他所有的明细打印出来人为核验SQL到底算得对不对。比如环比那道题我抽查1月份的用户数再对比2月份结果看公式套进去是否一致。最后做集合对比。如果有两个SQL都能实现同一个需求一定要对比结果是否一样。比如最近一笔可以用row_number1也可以用group by max这个老办法两个结果比对完全一致说明这次写对了。3.4 这套题在面试中的真实问法刷完题之后建议把自己代入面试官视角想一想这些题会怎么被问。最简单的问法是直接让你写写个SQL求每个部门薪资前三名。这就是row_number的套用练过就秒出。稍微升级的问法是结合业务场景订单表里有很多重复数据怎么取每个用户最新状态这是去重row_number结合。再难一点的是把窗口函数和join嵌套在一起统计每个品类下销量最高的三个商品占总销量的比例。这种问题需要先算出每个商品销量再开窗求rank再做聚合涉及的知识面比较综合。我刷的时候会把每道题在面经里的变体形式都记下来对着练习答案反向标注。这样做的好处是考试时换个外壳你也能识别出内核不会换个表名就懵。4. 常见问题与排查技巧实录4.1 为什么答案对不上别人的结果练习时最让人抓狂的状况就是同样的题自己跑出来的结果跟参考答案不一样。遇到过这个问题的可以先从三个方向逐一排查。第一是数据不同。同一个题目零件表里的数据可能差一条两条最终统计结果自然不一样。所以练习题如果附带了建表和插入数据的脚本一定要先跑脚本再对答案否则单纯对比数字没有意义。第二是空值策略不同。count(*)和count(1)会统计null行count(指定列)不会统计null行group by时null会单独成为一组join时null关联键基本匹配不上。这些差异都会让结果看起来不对但其实SQL逻辑是没错的。第三是维度口径不同。比如下过单的用户数到底是下单次数大于0的用户还是下单金额大于0的用户有时题目没写清楚参考答案选的口径可能和你不一样。练习时把口径定义写在SQL注释里是避免纠结的好习惯。4.2 运行时间过长的排查思路练习SQL时如果一条语句跑超过十分钟基本能怀疑是资源或写法出了问题。最常见的是没有加分区过滤导致全表扫描。我自己的模拟数据只有几百行但公司测试环境上有大量历史数据一旦忘了写分区条件就可能把整张表都扫描一遍虽然结果还是能出但浪费的时间就不值得了。另一个常见问题是小表关联大表。Hive的默认行为是都用shuffle join如果有一张只有几十行的小表应该用map join把表加载进内存避免shuffle。写法是在SQL里加/* MAPJOIN(小表别名) */。练习时多写map join提示能明显缩短运行时间也有助于理解Hive的执行优化机制。最后看是不是数据倾斜。练习数据量小不怎么倾斜但值得留个心眼。如果在真实环境里某几个key的数据量特别大可以加distribute by对倾斜值打散或者用两阶段聚合的思路。练习题虽然用不上但排查思路得养成。4.3 Hive语法报错的常见雷区写HiveQL最容易被语法细节绊倒我整理了练习过程中踩得最多的几个雷。字段名的反引号问题。Hive里如果字段名跟关键字冲突比如date、user这些相对敏感的词建议加反引号括起来。但反引号只解决字段命名问题表名还是尽量用下划线命名。字符串引号问题。Hive里字符串单双引号都可以但别在一条SQL里混用。日期比较时强烈建议在SQL前面写清楚日期格式比如2024-01-01和2024/01/01在Hive里是不同的数据混了查不出数。子查询必须加别名。很多人从MySQL过来习惯写from (select...)直接把括号关掉Hive严格要求子查询要有别名不加别名直接语法错误。这是所有Hive新手都会遇到的第一道坎。4.4 排查SQL结果正确性的思维检查清单我通过这50道题的练习总结了一个结果自查清单现在每条SQL跑完都会过一遍第一结果行数是否符合预期行数明显异常时先怀疑join产生了放大或数据没去重第二关键字段是否有null或空字符串这种情况通常能一眼看出第三抽查具体某一行数据手动按业务逻辑算一遍验证SQL逻辑第四如果题目要求排序检查排序字段是否有并列值没有考虑到并列会导致取数不一样第五对比参考的SQL和结果逐字段逐个核对差异而不是盯着一个数字不放4.5 刷题的顺序安排与时间分配最后分享一点刷题节奏的经验。不建议一天做完全部50道知识吸收效率太低更合理的做法是分四轮推进。第一轮按题型刷每天做同一类别的10道左右目标是把该类型的基本写法练熟。第二轮打乱顺序每天做5道模拟综合能力。第三轮只看题目不写答案在脑子里过一遍SQL框架发现自己卡住的知识点。第四轮挑自己老错的题再写一遍做错题本。时间上我大概花了两个礼拜。第一周前三天过完基础查询和分组聚合中间三天集中攻窗口函数两天做行列转换和综合第二周就是交叉练习和查漏补缺。这个节奏因人而异但如果目标是应对面试窗口函数部分一定要多花时间因为面试官对窗口函数的追问可以深入很多层。5. 这套练习还可以怎样扩展5.1 升级为带业务口径的专题练习50道题做完之后可以把每道题往真实业务套一下。比如按月统计用户数可以扩展成统计活跃用户数的日活月活每个用户最近一笔订单可以扩展成计算用户最后访问时间行转列的商品列表可以扩展成用户标签的聚合展示。只要把表名换成业务里的明细表字段名换成业务字段然后补上真正的业务口径这套练习就立刻从做题变成干活。我个人的体会是这种转化训练比再刷100道题更值钱。5.2 结合调度平台做定时取数训练大一点的数据团队都会用调度平台跑数。练习题做出来后可以把其中几个有价值的SQL包成脚本加上参数传递放到开发环境试跑一遍。日期参数、业务参数通过变量动态传入这样锻炼一套和正式数据仓库开发完全一致的流程。这个扩展方向适合已经工作的人它逼着你考虑分区、时间窗口、空数据告警这些生产环境才会遇到的问题。练习题答案是静态的线上取数脚本是动态的这一步跨过去了才算真正入了数仓的门。5.3 从Hive迁移到Spark SQL的对照练习Hive和Spark SQL语法大部分一致但在函数支持、优化器行为上有细微差异。50道题里那些窗口函数和行列转换的题目完全可以直接拿到Spark SQL里跑一遍。我自己对照跑下来大部分题无需修改直接能跑通只有少数函数因为Spark版本不同有差异。这种对照练习的好处是当你以后需要从Hive迁移到Spark SQL时不会因为语法细节卡壳迁移成本会低很多。5.4 带着练习题去验证官方文档很多人学Hive只看题目不看官方文档遇到不会的函数就搜别人的博客。我的经验是做完一批题后翻一遍Hive官方文档的函数列表往往能发现更优雅的解法。比如collect_set、sort_array、map_keys这类便捷函数很多答案没有用到但可以在文档里看到练习时多探索一两个用法比死记硬背答案强得多。6. 写在最后的一点个人经验刷完这50道题我最深的一点体会是Hive SQL的价值不在于语法多难而在于你能不能把复杂业务问题拆成清晰的计算步骤。题目是别人出的但分析问题的路径是你自己的两者重合越多你写SQL的水平涨得越快。再分享一个小技巧每道题做完之后试着把答案中所有你一开始没想到的函数和语法列出来用一个笔记软件按函数类型存档。隔几天翻一次下次写SQL时你会发现那些记过的函数会主动跳出来比你临时百度要快得多。如果你把50道题全部搞定并且每一道都能随口说出为什么这样写、换了表能不能复用那Hive这块的基础就算扎扎实实打好了。之后无论是看数仓建模还是接触更复杂的计算引擎走的都会顺畅很多。
