1. 为什么“Hive数仓SQL面试题”至今仍是数据岗的硬通货翻看各大招聘平台的数据开发、数仓工程师、ETL工程师岗位JDHive SQL几乎从未缺席。2021年前后那批面试题尤其有代表性——它们恰好卡在传统数仓向湖仓一体演进的转折点上既保留了经典维度建模的硬核考察又开始掺入窗口函数、行列转换、连续区间合并这类更贴近真实业务的题型。我前后带过几批新人也帮朋友做过模拟面试发现一个规律能把2021年那批Hive SQL题吃透的人上手写生产任务的速度明显快一截。原因不复杂那批题覆盖了数仓开发80%的日常操作——去重、聚合、排名、同环比、漏斗、留存、连续登录这些不是八股是每天都要写的东西。这篇文章面向三类人准备数据岗面试但SQL手感生疏的求职者、从业务转数仓需要补SQL实战的开发者、以及想系统梳理Hive SQL解题套路的老手。我会把2021年高频出现的题型拆成可复用的模板每道题讲清楚为什么这么写、Hive里有什么坑、生产环境怎么改。你不需要死记答案理解了解题框架换个数仓、换个业务场景照样能套。2. 数仓SQL面试的底层考察逻辑2.1 面试官到底在考什么很多人以为面试考的是“能不能写出来”其实面试官看的是三层能力。第一层是语法熟练度窗口函数、collect_list、explode这些能不能信手拈来第二层是逻辑拆解能力拿到“连续登录”这种题能不能先想清楚“怎么定义连续”再动手第三层是工程意识写出来的SQL在千万级数据量下会不会炸、有没有考虑数据倾斜、分区裁剪用没用上。我见过不少候选人题目逻辑对了但写了个count(distinct)在亿级表上面试官当场就问“这个跑多久”。所以准备面试题不能只对答案要顺带想一句“生产上我会怎么优化”。2.2 2021年题型的时代特征2021年的Hive面试题有个明显分水岭。早几年的题偏重group by、join、case when这些基础操作2021年开始大量出现窗口函数和复杂逻辑题。这跟当时的技术背景有关——Hive 2.x普及了窗口函数企业数据量涨到需要处理“连续N天”“同环比”这类时序问题面试题自然跟着业务走。另一个特征是场景化。不再是干巴巴的“求每个部门薪资第二高”而是包装成“用户行为日志”“订单流水”“直播打赏”这类真实场景。这意味着你不能只会写SQL还得能读懂业务表结构、理解字段含义。2.3 解题的通用框架我总结了一个四步框架几乎能套用所有数仓SQL题明确输出最终要什么粒度一行代表什么有哪些字段定位数据源涉及哪几张表关联键是什么需不需要过滤分区拆解中间步骤把复杂逻辑拆成多个CTE每步解决一个小问题。选择函数排名用row_number还是rank去重用distinct还是group by行列转换用collect_list还是explode这个框架的好处是把“写SQL”变成“填流程”思路不容易乱。下面几章我会用具体题型演示怎么套。3. 高频题型一排名与TopN的多种变体3.1 分组TopN的标准写法最经典的题每个部门薪资最高的前3名员工。标准解法用窗口函数SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, row_number() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 3;这里有个高频追问row_number、rank、dense_rank有什么区别面试官问这个不是让你背定义是看你会不会根据业务选。row_number是严格序号并列也分先后rank并列后跳号1,1,3dense_rank并列不跳号1,1,2。如果业务说“薪资并列的都算第一”那就得用dense_rank。注意row_number在并列时结果不确定因为排序字段相同的情况下Hive不保证顺序稳定。生产上如果要求可复现得在ORDER BY里加个唯一键兜底比如ORDER BY salary DESC, emp_id。3.2 组内TopN取多列进阶题每个部门薪资前3的员工同时要拿到他们的入职时间和岗位。有人会想用join回原表其实窗口函数里直接SELECT就行SELECT dept_id, emp_name, salary, hire_date, job FROM ( SELECT dept_id, emp_name, salary, hire_date, job, row_number() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 3;窗口函数在子查询里执行时原表所有列都能带出来不需要额外join。这个点很多人不知道白白多写一个join。3.3 全局TopN与分组TopN的取舍如果题目是“全公司薪资前10”那就不需要PARTITION BY直接ORDER BY salary DESC取前10。但要注意全局TopN在数据量大时ORDER BY会触发单Reducer容易OOM。生产上更稳的做法是先在各分区内取TopN再合并SELECT * FROM ( SELECT emp_name, salary, row_number() OVER (ORDER BY salary DESC) AS rn FROM emp ) t WHERE rn 10;数据量特别大时可以先用DISTRIBUTE BY打散再排序或者用sort by加limit。面试时如果面试官追问“亿级数据怎么取TopN”能答出“先局部TopN再全局合并”就是加分项。4. 高频题型二连续区间问题的通用解法4.1 连续登录N天的经典套路连续登录是数仓面试的“必考题”。核心思路是日期减去排名连续日期的差值相同SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, row_number() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp FROM (SELECT DISTINCT user_id, login_date FROM login_log) t ) t2 GROUP BY user_id, grp HAVING COUNT(*) 3;这个套路的精髓在date_sub(login_date, rn)。如果日期连续减去序号后得到的值相同天然形成分组。我第一次看到这个解法时觉得挺巧妙后来发现它能解决一大类问题——连续签到、连续消费、连续在线时长全是同一个模子。注意一定要先DISTINCT去重。如果同一天有多次登录记录不去重会导致排名错乱连续判断失效。这是新手最常踩的坑。4.2 连续区间的变体连续消费金额递增有些题会把“连续日期”换成“连续递增”。比如求用户连续消费金额递增的最长天数。这时候date_sub就不管用了得换思路——用lag比较相邻两行SELECT user_id, MAX(cnt) AS max_increase_days FROM ( SELECT user_id, dt, amount, SUM(CASE WHEN amount prev_amount THEN 0 ELSE 1 END) OVER (PARTITION BY user_id ORDER BY dt) AS grp FROM ( SELECT user_id, dt, amount, LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY dt) AS prev_amount FROM consume_log ) t ) t2 GROUP BY user_id, grp;这里用SUM(CASE WHEN...)做累积标记遇到不递增就换组。这个技巧叫“累积分组标记”处理“连续满足某条件”的题都能用。4.3 连续区间合并的工程坑生产环境做连续区间合并最大的坑是数据倾斜。如果某个用户有几千万条记录PARTITION BY user_id会让这个Reduce任务特别慢。解决办法是加盐——给user_id拼个随机后缀打散算完再聚合。面试时如果被问到“某个用户数据特别多怎么办”能说出加盐思路就说明你有生产经验。另一个坑是日期格式。Hive里日期可能是string类型date_sub要求标准yyyy-MM-dd格式。如果源数据是yyyyMMdd得先from_unixtime(unix_timestamp(dt,yyyyMMdd),yyyy-MM-dd)转一下。这种细节面试官不一定问但实际工作中天天遇到。5. 高频题型三行列转换与复杂聚合5.1 行转列collect_list与concat_ws组合行转列是数仓ETL的日常操作。比如把用户的多条标签记录拼成一行SELECT user_id, concat_ws(,, collect_list(tag)) AS tags FROM user_tag GROUP BY user_id;collect_list会保留重复值collect_set会去重。选哪个看业务——标签通常要去重用collect_set如果是行为序列得保留顺序和重复用collect_list配合sort_array。注意collect_list的顺序在Hive里不保证稳定。如果业务要求按时间排序拼接得用sort_array(collect_list(concat_ws(|, cast(ts as string), tag)))这种技巧把时间戳拼进去再排序最后再截取。5.2 列转行explode的用法与限制列转行用explode配合lateral viewSELECT user_id, tag FROM user_tag LATERAL VIEW explode(split(tags, ,)) t AS tag;explode只能处理数组或Map所以得先用split把字符串切成数组。这里有个性能坑如果tags字段特别长split会产生大量中间数据容易OOM。生产上建议控制单行长度或者改用explode前先过滤。另一个限制是explode不能和其他聚合函数同时出现在SELECT里。比如SELECT user_id, explode(tags), count(*)会报错。解决办法是把explode放在子查询里外层再聚合。5.3 复杂聚合同环比计算同环比是数仓面试的常客。求每个品类本月销售额、上月销售额、环比增长率SELECT category, SUM(CASE WHEN month 2021-06 THEN amount ELSE 0 END) AS cur_month, SUM(CASE WHEN month 2021-05 THEN amount ELSE 0 END) AS prev_month, ROUND( (SUM(CASE WHEN month 2021-06 THEN amount ELSE 0 END) - SUM(CASE WHEN month 2021-05 THEN amount ELSE 0 END)) / SUM(CASE WHEN month 2021-05 THEN amount ELSE 0 END) * 100, 2 ) AS growth_rate FROM sales WHERE month IN (2021-05, 2021-06) GROUP BY category;这种写法叫“条件聚合”比自连接效率高。但要注意分母为0的情况生产上得加CASE WHEN兜底否则会报除零错误。如果月份不固定可以用lag窗口函数动态取上月SELECT category, month, amount, LAG(amount, 1) OVER (PARTITION BY category ORDER BY month) AS prev_amount FROM monthly_sales;lag的优点是月份动态缺点是只能取相邻行如果中间缺月份会取错。所以生产上通常先补全月份维度再算。6. 高频题型四留存、漏斗与路径分析6.1 次日留存率的计算留存是用户增长的核心指标。次日留存的标准写法SELECT a.dt, COUNT(DISTINCT a.user_id) AS new_users, COUNT(DISTINCT b.user_id) AS retained_users, ROUND(COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id) * 100, 2) AS retention_rate FROM (SELECT user_id, MIN(dt) AS dt FROM login_log GROUP BY user_id) a LEFT JOIN login_log b ON a.user_id b.user_id AND b.dt date_add(a.dt, 1) GROUP BY a.dt;这里a表是每个用户的首次登录日b表是次日登录记录。LEFT JOIN保证新用户都保留没留存的记为0。注意COUNT(DISTINCT)在数据量大时很慢。生产上如果只是看趋势可以用approx_count_distinct近似去重误差1%左右但快很多。面试时提一句这个取舍面试官会觉得你懂工程。6.2 漏斗分析的分步聚合漏斗题通常给一张行为日志表求“浏览→加购→下单→支付”各步人数。标准解法是分步COUNT DISTINCT再合并SELECT COUNT(DISTINCT CASE WHEN event view THEN user_id END) AS view_users, COUNT(DISTINCT CASE WHEN event cart THEN user_id END) AS cart_users, COUNT(DISTINCT CASE WHEN event order THEN user_id END) AS order_users, COUNT(DISTINCT CASE WHEN event pay THEN user_id END) AS pay_users FROM event_log WHERE dt 2021-06-01;但严格漏斗要求“按顺序发生”上面的写法没考虑顺序。要按顺序得用窗口函数标记每个用户的事件序列再判断是否依次出现。这个难度高一些面试时如果被追问“怎么保证顺序”能答出“用collect_list按时间聚合事件序列再判断子序列”就是亮点。6.3 路径分析的自连接思路路径分析求“A→B→C”的用户数可以用自连接SELECT COUNT(DISTINCT a.user_id) FROM event_log a JOIN event_log b ON a.user_id b.user_id AND b.dt a.dt AND b.event B JOIN event_log c ON b.user_id c.user_id AND c.dt b.dt AND c.event C WHERE a.event A;自连接的坑是数据量爆炸。三个事件表各1000万行join后可能上亿。生产上更常用collect_list把用户事件序列拼成数组再用array_contains或自定义UDF判断路径。面试时能说出“自连接简单但性能差生产用序列聚合”就到位了。7. 面试现场解题思路与表达技巧7.1 拿到题先别急着写我见过太多候选人一上来就敲SQL写到一半发现逻辑不对又删掉重来。正确做法是先花30秒跟面试官确认三件事输出粒度、数据范围、边界条件。比如“连续登录”题要问清楚“连续”是指自然日连续还是工作日连续、是否允许中间断一天、数据里有没有重复记录。这些确认能避免你写出方向错误的SQL。确认完再口述思路“我打算先用窗口函数给每个用户的登录日期排名然后用日期减排名得到分组标记最后按标记聚合求连续天数。”面试官听到这个框架基本就知道你会了。7.2 写SQL时的表达节奏写的时候不要闷头写边写边解释。比如写到row_number() OVER (PARTITION BY user_id ORDER BY login_date)顺口说一句“这里按用户分组、按日期排序给每个用户的登录记录编个号”。面试官能跟上你的思路也显得你条理清晰。如果卡住了不要慌。可以说“我先用CTE把中间结果拆出来这样逻辑更清楚”然后一步步写。分步写的好处是即使最后没写完面试官也能看到你的思路是对的。7.3 被追问优化时的回答框架面试官追问“这个SQL在亿级数据上怎么优化”回答框架是先定位瓶颈再给方案。瓶颈通常在三处——join、group by、count distinct。对应方案瓶颈点优化手段适用场景join倾斜加盐打散、map join大表join小表、热点keygroup by倾斜两阶段聚合、加盐热点维度聚合count distinctapprox_count_distinct趋势分析、非精确场景全表扫描分区裁剪、列裁剪所有场景能对着这个表说清楚优化题基本稳了。8. 从面试题到生产SQL的差距8.1 面试题不会告诉你的分区陷阱面试题通常给一张干净的表生产环境的表动辄几千个分区。写SQL第一件事是确认分区字段WHERE dt 2021-06-01这种条件必须加否则全表扫描能把集群跑挂。我见过新人写了个没带分区的SQL跑了两个小时被DBA找上门。另一个坑是分区字段类型。有些表dt是string有些是int写条件时格式要对。dt 20210601和dt 2021-06-01可能一个走分区一个不走取决于表定义。8.2 数据倾斜的真实案例面试题里数据均匀分布生产环境经常遇到某个key占了80%的数据。比如按province聚合某个省的用户量是其他省的总和。这时候GROUP BY province会有一个Reducer特别慢。解决办法是两阶段聚合先给province加随机后缀打散聚合一次再去掉后缀聚合第二次。-- 第一阶段加盐打散 SELECT province, concat(province, _, cast(rand() * 10 as int)) AS salt_key, SUM(amount) AS partial_sum FROM sales GROUP BY province, concat(province, _, cast(rand() * 10 as int)); -- 第二阶段去掉盐聚合 SELECT province, SUM(partial_sum) AS total FROM stage1 GROUP BY province;这个套路面试时不一定考但生产上几乎每周都用。8.3 面试答案与生产SQL的取舍面试时追求“逻辑正确、写法优雅”生产上追求“跑得快、不出错”。两者有时冲突。比如面试写COUNT(DISTINCT user_id)没问题生产上亿级数据得换approx_count_distinct。面试写多层嵌套CTE很清晰生产上可能因为CTE物化导致性能差得改成临时表。我的建议是面试时先给标准答案然后主动补一句“生产上我会怎么调整”。这样既展示了基础功又体现了工程意识。9. 我踩过的坑与给你的实操建议准备Hive SQL面试光刷题不够得动手跑。我建议你本地搭个Hive环境或者用Spark SQL模拟把每道题都跑一遍。跑的过程中你会遇到各种报错——类型不匹配、窗口函数不支持、collect_list顺序乱——这些报错比答案更有价值。几个具体建议。第一建一张测试表反复练。字段不用多user_id、dt、event、amount四个就够能覆盖80%的题型。第二每道题写两种解法。比如连续登录一种用date_sub一种用lag对比哪种更直观。第三记录报错和解决过程。我当年整理了一个错题本后来发现面试时被问到的坑大半都在本子里。最后说个心态问题。面试题做不出来不代表你不行很多时候是题目表述不清或者面试官故意加压。遇到不会的坦诚说“这个思路我没想到但我可以试试从XX角度拆解”比硬编一个错误答案强。数仓这行逻辑清晰比背答案重要得多。如果你正在准备面试建议按“排名→连续→行列转换→留存漏斗”的顺序刷每类刷5道基本能覆盖2021年那批题的所有变体。刷完再回头看生产SQL你会发现很多优化思路其实就藏在那些面试题的追问里。