1. 为什么2021年的Hive SQL面试题至今还在被反复问
先抛一个我自己的观察：这两年帮团队筛简历、做技术面，发现一个挺有意思的现象——很多候选人简历上写着“精通Hive数仓”，结果一问窗口函数就卡壳，一让写连续登录就露馅。2021年前后那批Hive SQL面试题，放到今天依然高频出现，原因其实不复杂：数仓这套东西的底层逻辑没怎么变。存储换了、引擎换了、调度换了，但“怎么把业务问题翻译成SQL”这个核心能力，面试官永远在考。
我自己是从传统数仓一路做到现在的湖仓架构，面过的人没有一百也有八十。说句实在话，Hive SQL面试题看着老，但它考的东西特别扎实：窗口函数、行列转换、连续区间、TopN、去重计数、数据倾斜，这几类题几乎覆盖了数仓日常开发的八成场景。你把这几类吃透，不光面试能过，真到了写生产任务的时候也会发现，翻来覆去就是这些套路在变体。
这篇内容我打算按“面试官到底想考什么”这个角度来拆，而不是干巴巴地列题目和答案。因为我自己当面试官的时候，最烦的就是候选人背答案——题目稍微一变就不会了。所以下面每一类题，我都会先讲它背后的业务场景是什么，再讲解题的通用思路，最后给可以直接跑的SQL。适合准备面试的同学，也适合已经工作但想系统梳理一遍SQL能力的朋友。
提示：下面所有SQL都基于Hive的语法习惯来写，部分函数在Spark SQL、Presto里也能用，但细节会有差异，实际跑之前建议先确认引擎版本。
2. 窗口函数：面试出现频率最高的一块硬骨头
2.1 为什么面试官偏爱窗口函数
窗口函数是Hive SQL面试的绝对核心，没有之一。我统计过自己出过的题，十道里有六道会用到窗口函数。原因很简单：它同时考察了你对分组、排序、行间关系的理解。一个只会group by的人，遇到“每个用户最近一次登录”这种需求就懵了，而窗口函数能优雅地解决。
很多人对窗口函数的理解停留在“row_number()排个序”，这远远不够。真正要掌握的是三类：
- 排名类：
row_number()、rank()、dense_rank() - 聚合类：
sum() over()、avg() over()、count() over() - 偏移类：
lag()、lead()、first_value()、last_value()
这三类的区别和适用场景，下面用具体题目来说。
2.2 排名三兄弟：row_number、rank、dense_rank到底怎么选
这是最经典的送分题，也是最容易翻车的题。我见过太多人张口就说“都是排名”，结果一问并列情况就答不上来。
假设有一张成绩表score_table，字段是student_id、class_id、score。现在要按班级给成绩排名，三个函数的输出是这样的：
| student_id | score | row_number | rank | dense_rank |
|---|---|---|---|---|
| A | 95 | 1 | 1 | 1 |
| B | 95 | 2 | 1 | 1 |
| C | 90 | 3 | 3 | 2 |
| D | 85 | 4 | 4 | 3 |
规律很清楚：
row_number()：强行不并列，哪怕值一样也给你编上1、2、3、4rank()：并列后跳号，两个95都是第1，下一个直接跳到第3dense_rank()：并列后不跳号，两个95都是第1，下一个是第2
那实际业务里怎么选？我的经验是：
- 要取每组TopN且不允许重复（比如抽奖、去重取一条），用
row_number() - 要排名次且允许并列（比如成绩榜、销售榜），用
rank()或dense_rank() - 具体用哪个，看业务方要不要“跳号”。一般排行榜用
dense_rank()更符合直觉
SELECT student_id, class_id, score, row_number() OVER (PARTITION BY class_id ORDER BY score DESC) AS rn, rank() OVER (PARTITION BY class_id ORDER BY score DESC) AS rk, dense_rank() OVER (PARTITION BY class_id ORDER BY score DESC) AS drk FROM score_table;注意：
PARTITION BY后面跟的是分组字段，ORDER BY后面跟的是排序字段，这两个写反了结果就完全错了。我面过的人里，至少三成在这里栽过。
2.3 偏移函数：lag和lead解决“和上一行比较”的问题
lag()和lead()是处理同比、环比、连续行为的利器。它们的含义是：取当前行往前（lag）或往后（lead）第N行的值。
举个真实场景：有一张用户访问日志表visit_log，字段是user_id、visit_date。现在要找出连续两天都访问的用户。
思路是这样的：先用lag()把每个用户上一次的访问日期取出来，然后判断“当前日期 - 上一次日期 = 1天”。
SELECT user_id, visit_date, lag(visit_date, 1) OVER (PARTITION BY user_id ORDER BY visit_date) AS prev_date FROM visit_log;拿到prev_date之后，用datediff一减，等于1的就是连续两天。
SELECT DISTINCT user_id FROM ( SELECT user_id, visit_date, lag(visit_date, 1) OVER (PARTITION BY user_id ORDER BY visit_date) AS prev_date FROM visit_log ) t WHERE datediff(visit_date, prev_date) = 1;这个套路可以无限扩展：连续三天、连续N天、连续登录的最大天数，都是在这个基础上加条件或加聚合。核心就一句话：用lag把上一行拉过来，然后做差判断。
2.4 窗口帧：rows between到底在框什么
窗口帧（window frame）是很多人忽略的知识点，但面试官一旦问到“累计求和”“滑动平均”，不会窗口帧就写不出来。
默认情况下，如果ORDER BY存在，窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是从第一行到当前行。这就是为什么sum() over(order by ...)能实现累计求和。
但如果你要算“最近3天的滑动平均”，就得显式指定ROWS BETWEEN 2 PRECEDING AND CURRENT ROW。
SELECT dt, amount, sum(amount) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum, avg(amount) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d FROM daily_sales;这里有个坑要提醒：ROWS和RANGE的区别。ROWS是按物理行数算，RANGE是按值算。如果排序字段有重复值，RANGE会把相同值的行都算进去，结果可能和你想的不一样。日常开发我基本都用ROWS，因为它的行为更可预测。
3. 连续区间问题：从“连续登录”到“连续编号”的通用解法
3.1 连续登录问题的本质是什么
连续登录是Hive SQL面试的“常青树”，2021年考，现在还在考。但很多人只会背那一种写法，题目一变就废。我先讲清楚它的本质。
连续登录的本质是：给连续的行编上相同的组号。怎么编？用“日期减去排名”这个技巧。
假设用户A在1月1、2、3、5、6号登录。我们按日期排序，给每行编个号：
| visit_date | rn | date - rn |
|---|---|---|
| 2021-01-01 | 1 | 2020-12-31 |
| 2021-01-02 | 2 | 2020-12-31 |
| 2021-01-03 | 3 | 2020-12-31 |
| 2021-01-05 | 4 | 2021-01-01 |
| 2021-01-06 | 5 | 2021-01-01 |
看出来了吗？连续的日期减去连续的编号，差值是一样的。1、2、3号减完都是2020-12-31，5、6号减完都是2021-01-01。这个差值就是天然的“连续组号”。
3.2 完整SQL与逐步拆解
SELECT user_id, min(visit_date) AS start_date, max(visit_date) AS end_date, count(1) AS continuous_days FROM ( SELECT user_id, visit_date, date_sub(visit_date, row_number() OVER (PARTITION BY user_id ORDER BY visit_date)) AS group_id FROM ( SELECT DISTINCT user_id, visit_date FROM visit_log ) a ) b GROUP BY user_id, group_id HAVING count(1) >= 3;拆解一下：
- 最内层
DISTINCT：去重。同一天多次登录只算一次，这一步很多人漏掉，导致结果偏大 - 中间层：用
date_sub减去row_number，得到group_id - 最外层：按
user_id和group_id分组，count就是连续天数
提示：
date_sub是Hive的日期函数，第一个参数是日期，第二个是减去的天数。如果你用的是Spark SQL，也可以用date_sub，但要注意日期格式必须是yyyy-MM-dd。
3.3 举一反三：连续编号、连续区间都能套
这个“减去排名”的套路，不止能解连续登录。我举几个变体：
- 连续编号问题：给一串数字，找出连续递增的区间。思路一样，用
数字 - row_number，差值相同的归为一组 - 连续空位问题：找出座位号里连续的空位。同样处理
- 连续达标问题：连续N天销售额超过目标。把日期换成业务日期，逻辑不变
我面试的时候特别喜欢在这个题上追问：“如果要求连续登录的最大天数呢？”这时候你只需要把上面的结果再套一层max：
SELECT user_id, max(continuous_days) AS max_continuous_days FROM ( -- 上面的查询 ) t GROUP BY user_id;能答出这一层，面试官基本就认可你的SQL思维了。
4. 行列转换：collect_list、collect_set和explode的组合拳
4.1 行转列：把多行合并成一行
行转列的需求在数仓里太常见了。比如用户标签表，一个用户有多个标签，每个标签一行，现在要合并成“标签1,标签2,标签3”这种形式。
Hive里用collect_list或collect_set配合concat_ws：
SELECT user_id, concat_ws(',', collect_list(tag)) AS tags FROM user_tag GROUP BY user_id;collect_list和collect_set的区别：
collect_list：保留重复，按出现顺序收集collect_set：去重，但不保证顺序
选哪个看业务。如果标签本身不该重复，用collect_set更安全。如果要保留顺序，用collect_list，但要注意Hive里collect_list的顺序在分布式环境下不保证稳定，这是个坑。
注意：
collect_list在大数据量下可能造成单行过大，甚至OOM。如果标签特别多，建议先做限制或者分批处理。
4.2 列转行：explode把一行炸成多行
反过来，如果一张表里存的是“标签1,标签2,标签3”这样的字符串，要拆成多行，就用explode配合split：
SELECT user_id, tag FROM user_tag_concat LATERAL VIEW explode(split(tags, ',')) t AS tag;LATERAL VIEW是Hive特有的写法，作用是把炸裂出来的虚拟表跟原表做笛卡尔积。explode负责把数组炸成多行，split负责把字符串切成数组。
这里有个细节：如果tags是空字符串或者null，explode会直接把这行丢掉。如果你希望保留空值，得用explode_outer或者先做coalesce处理。
4.3 一个综合案例：用户行为路径分析
把行转列和列转行结合起来，能解决很复杂的路径分析问题。比如有一张用户行为表，记录每个用户的操作序列，现在要分析“访问首页 -> 搜索 -> 下单”这条路径的转化率。
思路是：先用collect_list按时间顺序把行为拼成数组，再用concat_ws拼成字符串，最后用like或正则去匹配路径。
SELECT count(1) AS total_users, sum(if(path like '%首页%搜索%下单%', 1, 0)) AS converted_users FROM ( SELECT user_id, concat_ws('>', collect_list(action)) AS path FROM ( SELECT user_id, action, action_time FROM user_behavior ORDER BY user_id, action_time ) a GROUP BY user_id ) b;这个写法在数据量不大的时候很好用，但数据量大了之后collect_list会成为瓶颈。生产环境我一般会用窗口函数或者专门的路径分析工具来做。
5. TopN与去重计数：面试官最爱追问的两个点
5.1 分组TopN：为什么不能直接用limit
“每个部门工资最高的3个人”这种题，新手最容易写成：
SELECT * FROM employee ORDER BY salary DESC LIMIT 3;这是全局TopN，不是分组TopN。正确的做法是用窗口函数：
SELECT * FROM ( SELECT emp_id, dept_id, salary, row_number() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn <= 3;面试官在这里通常会追问：“如果同一个人有两条记录怎么办？”这时候就要考虑用dense_rank还是先去重。分组TopN的核心是PARTITION BY，把分组字段想清楚，题目就解了一半。
5.2 去重计数：count distinct的坑和优化
count(distinct)是数仓里最常用的操作之一，但它有个大问题：数据倾斜。当某个值特别多的时候，所有数据会集中到一个reduce上，跑得特别慢。
优化思路是用group by改写：
-- 原始写法，容易倾斜 SELECT count(distinct user_id) FROM visit_log; -- 优化写法，先分组再去重 SELECT count(1) FROM ( SELECT user_id FROM visit_log GROUP BY user_id ) t;这两种写法结果一样，但后者在数据量大时通常更快，因为它把去重的工作分散到了多个reduce上。
还有一个更高级的技巧：如果只是要近似去重计数，可以用approx_distinct（Presto）或者HyperLogLog相关的函数，牺牲一点精度换性能。这个在面试里说出来是加分项。
5.3 面试追问：如何求中位数
中位数是TopN的变体，也是高频题。Hive里没有现成的中位数函数（老版本），需要用窗口函数算。
思路：先给每行编号，再根据总行数确定中位数的位置。
SELECT avg(salary) AS median_salary FROM ( SELECT salary, row_number() OVER (ORDER BY salary) AS rn, count(1) OVER () AS total FROM employee ) t WHERE rn IN (floor((total + 1) / 2), ceil((total + 1) / 2));这个写法的巧妙之处在于：奇数行时两个位置重合，偶数行时取中间两个的平均。floor和ceil配合，一个SQL搞定两种情况。
6. 数据倾斜：面试里区分“会用”和“懂原理”的分水岭
6.1 数据倾斜到底是怎么产生的
数据倾斜是Hive面试的“压轴题”，也是实际工作中最头疼的问题。它的本质是：在shuffle阶段，某些key的数据量远大于其他key，导致个别reduce任务跑得特别慢。
举个典型场景：一张订单表，90%的订单来自同一个大客户，剩下10%分散在几千个小客户。你按客户ID做group by，那个大客户的数据全跑到一个reduce上，其他reduce早就跑完了，就它还在那儿转。
面试官问数据倾斜，其实是想看你有没有真正处理过生产问题。光背“加盐”“打散”这些词没用，得说清楚什么场景用什么方案。
6.2 常见倾斜场景与对应方案
我把常见的倾斜场景和方案整理成一张表：
| 场景 | 表现 | 解决方案 |
|---|---|---|
| group by倾斜 | 某个key数据量过大 | 两阶段聚合（加随机前缀） |
| join倾斜 | 大表join小表 | MapJoin（小表广播） |
| join倾斜 | 大表join大表，key分布不均 | 加盐打散后join |
| count distinct倾斜 | 单key去重 | 先group by再去重 |
| 空值倾斜 | null值集中 | 过滤null或给null加随机值 |
两阶段聚合是最常用的方案，思路是：先给key加上随机前缀做一次聚合，再去掉前缀做第二次聚合。
-- 第一阶段：加随机前缀 SELECT concat(key, '_', cast(rand() * 10 as int)) AS salted_key, sum(value) AS partial_sum FROM source_table GROUP BY concat(key, '_', cast(rand() * 10 as int)); -- 第二阶段：去掉前缀再聚合 SELECT split(salted_key, '_')[0] AS key, sum(partial_sum) AS total_sum FROM stage_one GROUP BY split(salted_key, '_')[0];这个方案的核心是把一个大key拆成10个小key，让数据分散到多个reduce上，最后再合并。
6.3 MapJoin：小表广播的正确打开方式
如果倾斜发生在join阶段，而且其中一张表比较小（比如几万行），用MapJoin是最优解。原理是把小表加载到每个map节点的内存里，在map阶段就完成join，避免了shuffle。
Hive里开启MapJoin有两种方式：
-- 方式一：hint SELECT /*+ MAPJOIN(small_table) */ * FROM big_table JOIN small_table ON big_table.id = small_table.id; -- 方式二：设置参数 SET hive.auto.convert.join = true; SET hive.mapjoin.smalltable.filesize = 25000000; -- 25MB注意：MapJoin的小表不能太大，否则会撑爆内存。一般建议小表在25MB以内，具体看集群配置。如果小表超过这个大小，还是老老实实用加盐打散。
6.4 我踩过的坑：倾斜不一定是数据问题
说个我自己的教训。有一次线上任务跑得特别慢，我第一反应是数据倾斜，查了半天发现是join的key类型不一致——一边是string，一边是bigint，导致Hive没法用MapJoin，只能走普通的shuffle join。改完类型之后，任务从40分钟降到3分钟。
所以遇到慢任务，先别急着加盐，先看执行计划（explain），确认瓶颈到底在哪。很多时候问题出在类型、分区裁剪、小文件这些地方，跟倾斜没关系。
7. 面试之外：这些SQL能力在实际工作中怎么用
7.1 从面试题到生产任务的思维转换
面试题是“给定表结构，写出结果”，生产任务是“给定业务需求，设计表结构和SQL”。这两者之间有个鸿沟，很多人面试过了，到了实际工作还是不会写。
我的经验是：把每个业务需求先翻译成“输入是什么、输出是什么、中间要经过几步”。比如“统计每个用户的连续登录天数”，输入是日志表，输出是用户和天数，中间要经过去重、编号、分组三步。想清楚这三步，SQL自然就出来了。
另外，生产环境还要考虑性能、可维护性、数据质量。面试题只要求结果对，生产任务还要求跑得快、别人看得懂、异常能处理。这些是面试不会考但工作天天用的能力。
7.2 几个我常用的SQL优化习惯
分享几个我写SQL时雷打不动的习惯：
- 先过滤再join：能提前过滤的数据绝不带到join里，减少shuffle量
- 分区裁剪：where条件里一定带上分区字段，避免全表扫描
- 小表放右边：虽然Hive会自动优化，但显式写清楚更保险
- 避免select *：只取需要的列，减少IO
- 用explain看计划：写完复杂SQL先explain一下，看看有没有笛卡尔积、有没有走MapJoin
这些习惯看着简单，但坚持下来能让你的任务快不少。我带过的几个新人，改掉select *和漏分区这两个毛病之后，任务耗时普遍降了三成以上。
7.3 关于刷题的一点个人看法
最后说点掏心窝的话。现在网上Hive SQL面试题一搜一大把，很多人就是刷题背答案。我不反对刷题，但刷题的目的是建立思维模式，不是记住答案。
我的建议是：每做一道题，问自己三个问题——这道题考的是什么知识点？如果条件变一下还能不能解？有没有更优的写法？把这三个问题想清楚，一道题顶十道。反过来，如果只是把答案抄一遍，遇到变体照样懵。
数仓这个方向，SQL是基本功，但基本功之上还有建模、调度、数据治理、性能调优一大堆东西。面试题只是敲门砖，真正决定你能走多远的，是解决实际问题的能力。我自己到现在还在不断学新东西，因为数据量在涨、引擎在换、业务在变，没有一劳永逸的事。
如果你正在准备面试，把上面这几类题吃透，基本就够用了。如果你已经工作了，不妨拿这些题当镜子，照照自己哪块还有短板。SQL这东西，写多了自然就有感觉了。
