HiveSQL面试必刷:5种大厂高频开窗函数实战解析(附直播人数统计完整代码)
如果你正在准备大数据方向的面试,尤其是那些要求HiveSQL熟练度的岗位,那么开窗函数绝对是绕不开的核心考点。我见过太多候选人,对基础的GROUP BY和聚合函数了如指掌,但一遇到需要跨行计算、排名、累积的场景就卡壳。实际上,在大厂的真实业务场景中,无论是用户行为分析、业务指标计算还是数据清洗,开窗函数都是提升SQL表达能力和解决复杂问题的利器。这篇文章,我将抛开教科书式的罗列,直接带你深入五个最常被考到的开窗函数组合应用场景,并用一个完整的“直播平台最高峰同时在线人数”案例,手把手拆解解题思路和代码实现。你会发现,掌握了这些核心模式,很多看似棘手的面试题都能迎刃而解。
1. 开窗函数:从“分组聚合”到“行间洞察”的思维跃迁
在传统SQL里,GROUP BY是我们的老朋友,它把数据分成一个个“桶”,然后在每个桶内部进行聚合计算,最终每个桶输出一行结果。这个模型简单直观,但它有一个致命的限制:它丢失了桶内记录之间的顺序和关系。你无法轻松地回答“每个用户上一笔订单是什么?”、“当前销售额累积到多少了?”、“这个月的排名相比上个月是上升还是下降了?”这类问题。
开窗函数(Window Function)正是为此而生。它引入了“窗口”的概念。你可以这样理解:GROUP BY是把数据切成互不重叠的块,而开窗函数是为每一行数据都“开一扇窗”,这扇窗定义了与当前行相关的一组行(即窗口),然后在这组行上进行计算,计算结果会附加到当前行上,而不会减少行数。这保留了原始数据的粒度,同时赋予了强大的分析能力。
一个标准的开窗函数语法如下:
<窗口函数> OVER (
[PARTITION BY <列清单>]
[ORDER BY <排序用列清单>]
[ROWS BETWEEN <起始行> AND <结束行>]
)
- PARTITION BY:类似于
GROUP BY,定义窗口的分区。计算会在每个分区内独立进行。 - ORDER BY:定义窗口内行的排序顺序,这对于计算排名、累积和、前后值对比至关重要。
- ROWS BETWEEN:定义窗口的框架,即具体哪些行参与计算。例如
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从分区第一行到当前行。
提示:面试中常考的是对
PARTITION BY和ORDER BY组合的理解。记住,有ORDER BY没PARTITION BY,窗口就是整个结果集;两者都有,窗口就是每个分区内部;两者都没有,窗口函数会对所有行进行计算(但通常没什么意义)。
开窗函数主要分为几类:
- 聚合类:
SUM(),AVG(),COUNT(),MAX(),MIN()。但用在窗口里,它们不再聚合掉行。 - 排名类:
ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()。用于生成序号,处理并列排名是考察重点。 - 取值类:
LAG(),LEAD(),FIRST_VALUE(),LAST_VALUE()。用于访问窗口内其他行的值,是处理“前后关系”问题的核心。
下面,我们就进入实战,看看这些函数是如何在具体问题中组合发挥威力的。
2. 实战场景一:时间序列分析与前后对比(LAG/LEAD)
这是面试中最经典的一类问题,核心是分析当前记录与它“邻居”之间的关系。LAG(col, n)获取当前行之前第n行的值,LEAD(col, n)则获取之后第n行的值。
典型考题:股票价格波峰波谷识别
问题:给定每日股票收盘价,找出所有波峰(价格高于前一天和后一天)和波谷(价格低于前一天和后一天)的日期和价格。
解题思路拆解:
- 核心需求是让每一行都能“看到”它前一天和后一天的价格。这正是
LAG和LEAD的用武之地。 - 我们需要按股票代码分区(
PARTITION BY ts_code),确保每只股票独立计算。 - 在分区内,按交易日期排序(
ORDER BY trade_date),这样“前一天”、“后一天”才有明确意义。 - 使用
CASE WHEN语句,比较当前价格、前一天价格(LAG结果)、后一天价格(LEAD结果),判断是否为波峰或波谷。
完整代码示例:
WITH price_with_neighbors AS (
SELECT
ts_code,
trade_date,
close,
-- 获取前一天的收盘价
LAG(close, 1) OVER (PARTITION BY ts_code ORDER BY trade_date) AS prev_close,
-- 获取后一天的收盘价
LEAD(close, 1) OVER (PARTITION BY ts_code ORDER BY trade_date) AS next_close
FROM stock_price_table
)
SELECT
ts_code,
trade_date,
close,
CASE
WHEN close > prev_close AND close > next_close THEN '波峰'
WHEN close < prev_close AND close < next_close THEN '波谷'
ELSE '其他'
END AS point_type
FROM price_with_neighbors
WHERE (close > prev_close AND close > next_close)
OR (close < prev_close AND close < next_close);
面试延伸思考:
- 边界处理:对于第一行,
LAG(close, 1)会是NULL;对于最后一行,LEAD(close, 1)会是NULL。上面的WHERE子句会过滤掉这些边界行(因为它们无法同时满足大于前后或小于前后)。如果题目要求识别“起始波峰/波谷”,就需要用NVL或COALESCE函数给边界值一个默认值再比较。 - 性能提示:当数据量极大时,
LAG/LEAD由于只需要访问有限的前后行,通常比自连接(Self-Join)性能要好得多。
3. 实战场景二:累积计算与区间统计(SUM/AVG OVER)
当需要计算“截至当前”的累积值、移动平均值时,SUM() OVER和AVG() OVER配合ORDER BY和窗口框架(ROWS BETWEEN)就成了必备工具。
典型考题:直播平台最高峰同时在线人数
这是字节跳动等大厂高频考题。给定主播的上下播时间流水,计算平台在一天内同时在线人数的峰值。
解题思路的巧妙转换: 这个问题最精妙的地方在于状态转换。直接计算任意时间点的在线人数非常困难。我们转换思路:将一次直播看作两个事件——上播(+1人)和下播(-1人)。把原始表“展开”成一个事件流表,每个事件带有一个人数变化值(+1或-1)。
- 数据重塑:使用
UNION ALL将开始时间和结束时间合并为一列action_time,并标记change_cnt(开始为+1,结束为-1)。 - 按时间排序:对这个事件流表按
action_time升序排序。 - 累积求和:对
change_cnt进行开窗累积求和(SUM(change_cnt) OVER (ORDER BY action_time))。这个累积和的结果,就是在每个事件发生的精确时刻,平台的实时在线人数。 - 求最大值:最后,对这个实时在线人数序列求最大值,即得到最高峰同时在线人数。
完整代码示例:
-- 假设表 livestream_log 包含 user_id, start_time, end_time
WITH event_stream AS (
-- 上播事件,人数+1
SELECT
user_id,
start_time AS action_time,
1 AS change_cnt
FROM livestream_log
UNION ALL
-- 下播事件,人数-1
SELECT
user_id,
end_time AS action_time,
-1 AS change_cnt
FROM livestream_log
),
online_cnt_at_moment AS (
SELECT
action_time,
-- 关键:按时间顺序累积计算在线人数
SUM(change_cnt) OVER (ORDER BY action_time ASC) AS concurrent_users
FROM event_stream
)
-- 找出累积过程中的最大值
SELECT MAX(concurrent_users) AS peak_concurrent_users
FROM online_cnt_at_moment;
面试延伸与优化:
- 时间精度:如果时间戳精确到毫秒,且同一毫秒有多条记录,
ORDER BY action_time可能无法确定唯一顺序,导致结果微小波动。可以添加第二排序键,如user_id。 - 窗口框架:这里
SUM() OVER (ORDER BY action_time ASC)的默认窗口框架是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,即从第一行累积到当前行,正是我们需要的。 - 变体问题:面试官可能会问“请找出最高峰发生的时间段”。这需要在上述查询基础上,筛选出
concurrent_users等于最大值的那些action_time,然后分析其时间分布。
4. 实战场景三:排名与分组Top-N问题(ROW_NUMBER/RANK/DENSE_RANK)
这类函数用于在分区内排序,ROW_NUMBER()生成唯一连续序号,RANK()并列会跳号,DENSE_RANK()并列不跳号。
典型考题:部门内员工薪水排名、销售额前N的品类
问题:有一张销售表sales(region, salesperson, amount),请列出每个区域销售额排名前3的销售员。
解题与误区辨析:
SELECT *
FROM (
SELECT
region,
salesperson,
amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales
) ranked_sales
WHERE rn <= 3;
这里使用ROW_NUMBER(),即使一个区域内有并列第三名,也只会取其中一个。如果题目要求“包含所有并列第三名”,就应该使用DENSE_RANK()或RANK(),并判断rnk <= 3。但RANK()可能会返回多于3行(如果并列导致跳号)。需要仔细辨别题目要求。
另一个高级应用:巧解区间合并问题 问题:合并重叠的日期区间(如会议室预定、活动排期)。
| hall_id | start_date | end_date |
|---|---|---|
| 1 | 2023-01-13 | 2023-01-20 |
| 1 | 2023-01-14 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
思路:这需要判断当前行的开始日期是否小于等于之前所有行的最大结束日期。如果是,则属于同一个连续区间。
- 使用
MAX(end_date) OVER (PARTITION BY hall_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)计算“到上一行为止的最大结束日期”。 - 比较当前
start_date与此最大值。如果start_date <= max_end_date_so_far,则属于同一组;否则,开启新的一组。 - 对第二步产生的分组标记进行累积求和(
SUM(is_new_group) OVER...),生成唯一的group_id。 - 最后按
hall_id和group_id分组,取最小start_date和最大end_date。
这个解法完美融合了MAX() OVER(作为窗口函数)和SUM() OVER(用于生成分组ID)以及CASE WHEN逻辑判断。
5. 实战场景四:首尾值获取与差值计算(FIRST_VALUE/LAST_VALUE)
FIRST_VALUE和LAST_VALUE用于获取窗口内第一行或最后一行的值,常与ROWS BETWEEN一起定义具体的窗口范围。
典型考题:用户会话分析,计算每次会话的起始和结束事件
假设有用户行为日志user_events(user_id, event_time, event_type),需要划分会话(session),并标记每个会话的起始页面和结束页面(假设event_type为页面ID)。
解题思路:
- 首先需要定义会话。一个简单规则是:如果两次事件间隔超过30分钟,则视为新会话开始。这可以用
LAG计算时间差,并用SUM(CASE WHEN...)累积生成会话ID(类似之前区间合并的技巧)。 - 在得到会话ID后,在会话内按时间排序,使用
FIRST_VALUE(event_type) OVER (PARTITION BY session_id ORDER BY event_time)获取会话起始页。 - 使用
LAST_VALUE(event_type) OVER (PARTITION BY session_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)获取会话结束页。注意:LAST_VALUE的默认窗口是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,如果不指定UNBOUNDED FOLLOWING,得到的是到当前行为止的最后一个值,而非整个分区的最后一个值。这是面试中的一个常见陷阱。
6. 实战场景五:百分比与比例计算(NTILE, CUME_DIST, PERCENT_RANK)
这类函数用于分析数据的分布。
NTILE(n):将分区内的数据尽可能平均地分成n个桶,并返回当前行所在的桶号。CUME_DIST():计算小于等于当前行值的行数所占的比例。PERCENT_RANK():(当前行的RANK值 - 1) / (分区总行数 - 1)
典型考题:将用户按消费金额分为高、中、低三组,或计算成绩的百分位数。
-- 将每个部门的员工按薪水分为4个等级
SELECT
emp_id,
depart_id,
salary,
NTILE(4) OVER (PARTITION BY depart_id ORDER BY salary DESC) AS salary_quartile
FROM employee_salary;
-- 计算每个学生的成绩在班级中的百分位排名
SELECT
student_id,
score,
PERCENT_RANK() OVER (ORDER BY score) AS percent_rank
FROM exam_scores;
7. 性能优化与面试避坑指南
掌握了函数用法,还要在面试中展现出工程思维。以下是一些关键点:
1. 分区键(PARTITION BY)的选择:
分区不当会导致数据倾斜。例如,按user_id分区,如果某个“羊毛党”用户有上百万条记录,而其他用户只有几条,那么这个分区会非常庞大,拖慢整个任务。面试中可以讨论如何识别和缓解数据倾斜,比如先对热点key进行采样过滤或单独处理。
2. 排序键(ORDER BY)与窗口框架的性能:
ORDER BY通常需要排序操作。当窗口定义为ROWS BETWEEN N PRECEDING AND CURRENT ROW(如计算移动平均)时,Hive可以优化为只维护一个大小为N的滑动窗口,内存消耗小。但如果窗口是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(累积计算),虽然计算逻辑简单,但需要保存分区内所有历史状态,在数据量大时可能内存压力较大。
3. 组合使用与执行计划:
多个开窗函数依次执行时,如果它们的PARTITION BY和ORDER BY子句相同,Hive有可能在一个MapReduce或Tez任务中合并执行。如果不同,则可能触发多个阶段,增加I/O和Shuffle开销。在写复杂SQL时,尽量让开窗逻辑的排序和分区一致。
4. 别忘了WHERE和HAVING的执行顺序: 这是一个经典陷阱。观察以下查询:
SELECT user_id, AVG(score) OVER (PARTITION BY user_id) as avg_score
FROM logs
WHERE date = '2023-10-01';
窗口函数是在WHERE过滤之后才计算的。如果你需要先计算全量数据的窗口函数(比如排名),再过滤,就需要把窗口计算放在子查询中,在外部过滤。
最后,我个人的经验是,面对开窗函数相关的面试题,先别急着写代码。花一分钟在白板上画出数据样例,想清楚你希望为每一行计算出什么新的信息,这个信息需要依赖哪些其他行。明确了这个“窗口”的定义,再套用PARTITION BY、ORDER BY和合适的窗口函数,答案自然就清晰了。多找一些真实的大厂面试题练习,把上述五种场景的解题模板内化,你就能在面试中更加游刃有余。
235



被折叠的 条评论
为什么被折叠?



