1. 为什么“Hive数仓SQL面试题”至今仍是数据岗的硬通货
翻看各大招聘平台的数据开发、数仓工程师、ETL工程师岗位JD，Hive 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,3）；dense_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 view：
SELECT 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小表、热点key |
| group by倾斜 | 两阶段聚合、加盐 | 热点维度聚合 |
| count distinct | approx_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，你会发现很多优化思路其实就藏在那些面试题的追问里。
