詳解Hive窗口函數及10道實戰練習詳情 - hive,面試,窗口函數,數據,Hive,Hive,大數據 feiry 博客

📑 目錄（Table of Contents）

一、示例數據
二、常見窗口函數詳解

1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. SUM OVER
5. AVG OVER
6. LAG
7. LEAD
8. FIRST_VALUE
9. LAST_VALUE
10. NTILE

三、窗口函數應用場景
四、窗口函數面試常見問題
五、總結

窗口函數（Window Function）是 Hive 中最強大的分析工具之一，支持對同組數據進行排名、累計、前後行對比、分位數計算等操作，而無需改變原錶行數。

本篇文章通過 完整示例 + 示例輸出 + 面試高頻問題，幫你係統掌握 Hive 窗口函數，適合學習 & 面試 & 實戰備查。

一、示例數據

文中的所有示例都基於同一張員工工資表，方便你對照理解：

WITH emp AS (
  SELECT * FROM (
    SELECT 'A' dept, 'Tom'   name, 5000 salary UNION ALL
    SELECT 'A' dept, 'Jack'  name, 7000 salary UNION ALL
    SELECT 'A' dept, 'Lucy'  name, 7000 salary UNION ALL
    SELECT 'B' dept, 'Bob'   name, 4000 salary UNION ALL
    SELECT 'B' dept, 'Amy'   name, 8000 salary
  ) t
)

emp 表數據如下：

dept	name	salary
A	Tom	5000
A	Jack	7000
A	Lucy	7000
B	Bob	4000
B	Amy	8000

下文所有 SQL 示例都可以直接在 Hive 中執行：
WITH emp AS (...) SELECT ... FROM emp;

二、常見窗口函數詳解（每個都有示例輸出）

1. ROW_NUMBER()：組內唯一且連續的排名

語法：

SELECT dept, name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM emp;

含義：

按部門分組（PARTITION BY dept）
每個部門內部按工資降序排序
生成連續且不併列的排名（1,2,3,4,…）

示例輸出：

dept	name	salary	rn
A	Jack	7000	1
A	Lucy	7000	2
A	Tom	5000	3
B	Amy	8000	1
B	Bob	4000	2

2. RANK()：允許並列，排名會跳號

語法：

SELECT dept, name, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM emp;

含義：

同一部門內工資一樣的員工，排名相同
但會跳號：例如 1,1,3

示例輸出：

dept	name	salary	rnk
A	Jack	7000	1
A	Lucy	7000	1
A	Tom	5000	3
B	Amy	8000	1
B	Bob	4000	2

3. DENSE_RANK()：允許並列，但不跳號

語法：

SELECT dept, name, salary,
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM emp;

含義：

與 RANK() 類似，也會並列
但不會跳號：例如 1,1,2

示例輸出：

dept	name	salary	dr
A	Jack	7000	1
A	Lucy	7000	1
A	Tom	5000	2
B	Amy	8000	1
B	Bob	4000	2

4. SUM() OVER：組內累計求和

語法：

SELECT dept, name, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS running_total
FROM emp;

含義：

在每個部門內，按工資從高到低排序
對工資做累計求和

示例輸出：

dept	name	salary	running_total
A	Jack	7000	7000
A	Lucy	7000	14000
A	Tom	5000	19000
B	Amy	8000	8000
B	Bob	4000	12000

適合做：累計金額、累計用户數、累計訪問量等分析場景。

5. AVG() OVER：組內運行平均值（移動平均）

語法：

SELECT dept, name, salary,
       AVG(salary) OVER (PARTITION BY dept ORDER BY salary) AS avg_salary
FROM emp;

含義：

每個部門中，按工資升序
對“當前行之前（包括當前行）”的工資求平均

示例輸出：

dept	name	salary	avg_salary
A	Tom	5000	5000.0
A	Jack	7000	6000.0
A	Lucy	7000	6333.33
B	Bob	4000	4000.0
B	Amy	8000	6000.0

可用於：移動平均、趨勢平滑、時間序列分析等。

6. LAG()：獲取上一行的值（環比）

語法：

SELECT name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
       salary - LAG(salary, 1, 0) OVER (ORDER BY salary) AS diff_with_prev
FROM emp;

含義：

LAG(col, 1, 默認值)：獲取上一行的 col
可直接用於環比、差值計算

示例輸出：

name	salary	prev_salary	diff_with_prev
Bob	4000	0	4000
Tom	5000	4000	1000
Jack	7000	5000	2000
Lucy	7000	7000	0
Amy	8000	7000	1000

7. LEAD()：獲取下一行的值（對比下一期）

語法：

SELECT name, salary,
       LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary,
       LEAD(salary, 1, 0) OVER (ORDER BY salary) - salary AS diff_with_next
FROM emp;

含義：

LEAD(col, 1, 默認值)：獲取下一行的 col
常用於：預測、對比“下一期”的指標

示例輸出：

name	salary	next_salary	diff_with_next
Bob	4000	5000	1000
Tom	5000	7000	2000
Jack	7000	7000	0
Lucy	7000	8000	1000
Amy	8000	0	-8000

8. FIRST_VALUE()：組內第一個值（如最高工資人）

語法：

SELECT dept, name, salary,
       FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner
FROM emp;

含義：

在部門內按工資降序
獲取該部門工資最高的那個人（第一名）

示例輸出：

dept	name	salary	top_earner
A	Jack	7000	Jack
A	Lucy	7000	Jack
A	Tom	5000	Jack
B	Amy	8000	Amy
B	Bob	4000	Amy

典型應用：每行數據都帶上“組內最佳/最大/最小”的參考值。

9. LAST_VALUE()：組內最後一個值（注意 frame）

語法：

SELECT dept, name, salary,
       LAST_VALUE(name) OVER (
         PARTITION BY dept
         ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS lowest_earner
FROM emp;

含義：

在部門內按工資降序
獲取組內最後一行（即工資最低的人）
必須指定：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，否則默認只看到當前行之前數據，會導致結果不對。

示例輸出：

dept	name	salary	lowest_earner
A	Jack	7000	Tom
A	Lucy	7000	Tom
A	Tom	5000	Tom
B	Amy	8000	Bob
B	Bob	4000	Bob

10. NTILE(n)：分桶函數（分位數分析）

語法：

SELECT name, salary,
       NTILE(3) OVER (ORDER BY salary DESC) AS bucket
FROM emp;

含義：

按工資從高到低排序
分成 3 個桶（儘量平均）
常用於：用户等級劃分、分位數統計

示例輸出：

name	salary	bucket
Amy	8000	1
Jack	7000	1
Lucy	7000	2
Tom	5000	2
Bob	4000	3

三、窗口函數應用場景速查表

場景	推薦函數
TopN / 去重	ROW_NUMBER
帶並列的排名	RANK / DENSE_RANK
累計金額/數量	SUM() OVER
移動平均	AVG() OVER
環比、前後對比	LAG / LEAD
獲取組內頭尾	FIRST_VALUE / LAST_VALUE
分位數 / 分層	NTILE

四、窗口函數面試常見問題（高頻）

❓ 1. 窗口函數和 GROUP BY 的根本區別？

特性	窗口函數	GROUP BY
是否改變行數	❌ 否	✔ 是
是否保留明細	✔ 是	❌ 否
前後行對比	✔ 支持（LAG/LEAD）	❌ 不支持

一句話：GROUP BY 做彙總，窗口函數做分析。

❓ 2. ROW_NUMBER、RANK、DENSE_RANK 區別？

函數	是否允許並列	是否跳號
ROW_NUMBER	❌ 不允許	❌ 不跳號
RANK	✔ 允許	✔ 跳號
DENSE_RANK	✔ 允許	❌ 不跳號

❓ 3. 為什麼 LAST_VALUE() 經常“不對勁”？

因為默認窗口 frame 為：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

只能看到“當前行及之前”的數據，看不到後面的行，導致 LAST_VALUE() 實際上得到的是“當前行”附近的值而不是組內真正的最後一行。

正確寫法：

LAST_VALUE(col) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

❓ 4. 窗口函數能在 WHERE 裏直接用嗎？

不能。

窗口函數執行順序在 SELECT 階段之後，WHERE 在其之前，因此必須通過子查詢/CTE 包一層：

WITH t AS (
  SELECT name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM emp
)
SELECT * FROM t WHERE rn <= 3;

❓ 5. LAG 和 LEAD 的典型應用場景？

日/周/月環比
連續登錄天數
金融流水前後對比
訂單金額變化趨勢

❓ 6. NTILE(n) 如何分桶？

規則：儘量平均。例如 5 行數據，NTILE(3) 的結果為：

1, 1, 2, 2, 3

❓ 7. 窗口函數的執行順序大致是怎樣的？

簡化後的執行順序：

FROM / JOIN
WHERE
GROUP BY
HAVING
窗口函數計算（OVER）
SELECT
ORDER BY

❓ 8. 窗口函數會帶來哪些性能問題？如何優化？

問題：

需要排序（ORDER BY），大數據量時會產生大量 shuffle
同一個 SQL 中多次使用不同窗口可能重複排序

優化思路：

儘量複用相同的 PARTITION BY + ORDER BY
減少分區大小（如按業務分區）
在上游預聚合減小數據量

五、總結

窗口函數不改變行數，但可以在一行中引用“同組其他行”的數據，是分析類 SQL 的核心工具。
熟練掌握 ROW_NUMBER / RANK / DENSE_RANK / SUM OVER / LAG / LEAD / NTILE 等函數，就已經覆蓋了 80% 的實際業務場景。
面試中，窗口函數幾乎是大數據/Hive 開發崗位的必考內容。

建議：把文中的示例 SQL 在自己的 Hive 環境跑一遍，配合結果對照理解，效果會非常好。

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

feiry 博客

feiry 博客

博客 / 詳情