Hive中的窗口函数为复杂数据分析提供了强大的支持。它们在不改变原始行数的前提下，对与当前行相关的“窗口”内数据进行计算。核心在于OVER()子句，它定义了窗口的范围（分区、排序、帧）。
思维导图
一、核心语法回顾
window_function([args]) OVER ([PARTITION BY cols] [ORDER BY cols [ASC|DESC]] [window_frame])
二、排名函数 (Ranking Functions) 用法与示例
用于为分区内的行分配名次。
ROW_NUMBER(): 唯一连续排名。
示例：为每个部门的员工按薪水从高到低编号。
SELECT emp_name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;
RANK(): 并列跳号排名。
示例：同上，但薪水相同者名次相同。
SELECT emp_name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;
DENSE_RANK(): 并列不跳号排名。
示例：同上，但排名更紧凑。
SELECT emp_name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank_in_dept
FROM employees;
NTILE(n): 将分区内数据平均分成n个桶，返回桶号。
示例：将每个部门的员工按薪水分为高、中、低三档。
SELECT emp_name, department, salary,
NTILE(3) OVER (PARTITION BY department ORDER BY salary DESC) as salary_tier
FROM employees;
三、分析函数 (Analytic Functions) / 值函数 (Value Functions) 用法与示例
用于获取窗口内其他行的值。
LAG(col, offset, default_value): 取当前行在分区内排序后向上第offset行的col值。
示例：计算每位员工当月销售额与上月销售额的比较。
SELECT emp_id, sale_month, sales_amount,
LAG(sales_amount, 1, 0) OVER (PARTITION BY emp_id ORDER BY sale_month) as prev_month_sales
FROM monthly_sales;
LEAD(col, offset, default_value): 取当前行在分区内排序后向下第offset行的col值。
示例：预测下一次购买时间。
SELECT user_id, purchase_date,
LEAD(purchase_date, 1) OVER (PARTITION BY user_id ORDER BY purchase_date) as next_purchase_date
FROM purchases;
FIRST_VALUE(col): 取当前窗口框架内 第一行的col值。
示例：显示每个部门中薪水最低的员工的薪水。
SELECT emp_name, department, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary_in_dept
FROM employees;
LAST_VALUE(col): 取当前窗口框架内 最后一行的col值。
获取分区真正最后一个值示例：
SELECT emp_name, department, salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_salary_in_dept
FROM employees;
四、聚合函数作为窗口函数 (Aggregate Functions as Window Functions) 用法与示例
标准聚合函数 (SUM, AVG, COUNT, MAX, MIN) 可与 OVER() 结合。
SUM() OVER(...): 累积求和或分区总和。
示例1 (分区总和)：
SELECT emp_name, department, salary,
SUM(salary) OVER (PARTITION BY department) as total_dept_salary
FROM employees;
示例2 (累积求和)：
SELECT emp_name, department, salary, emp_id,
SUM(salary) OVER (PARTITION BY department ORDER BY emp_id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_salary_by_id
FROM employees;
AVG() OVER(...): 移动平均或分区平均。
示例 (3日移动平均销售额)：
SELECT sale_date, sales_amount,
AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3day
FROM daily_sales;
COUNT() OVER(...): 分区内计数或累积计数。
示例：
SELECT emp_name, department,
COUNT(*) OVER (PARTITION BY department) as num_employees_in_dept
FROM employees;
MAX() OVER(...)/MIN() OVER(...): 分区内最大/最小值。
示例：
SELECT emp_name, department, salary,
MAX(salary) OVER (PARTITION BY department) as max_salary_in_dept
FROM employees;
五、窗口帧 (Window Frame Clause) 的详细用法与示例
精确定义聚合计算的行范围，主要与 ORDER BY 配合。
{ROWS | RANGE} BETWEEN frame_start AND frame_end
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从分区开始到当前行。
示例 (默认累积行为)：计算每个产品按日期顺序的累计销售额。
SELECT product_id, sale_date, sales_amount,
SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_date) as cumulative_sales_default_frame -- 默认即此帧
FROM product_sales;
等同于：
SELECT product_id, sale_date, sales_amount,
SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales_explicit_frame
FROM product_sales;
ROWS BETWEEN N PRECEDING AND CURRENT ROW: 当前行及前N行。
示例 (最近3次活动的平均时长)：
SELECT emp_id, activity_date, duration_minutes,
AVG(duration_minutes) OVER (PARTITION BY emp_id ORDER BY activity_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as avg_duration_last_3_activities
FROM employee_activity;
ROWS BETWEEN CURRENT ROW AND N FOLLOWING: 当前行及后N行。
示例 (当前及未来2次访问的总页面数)：
SELECT session_id, start_time, pages_viewed,
SUM(pages_viewed) OVER (PARTITION BY user_id ORDER BY start_time ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) as total_pages_current_and_next_2_sessions
FROM web_sessions;
ROWS BETWEEN N PRECEDING AND M FOLLOWING: 当前行前N行到后M行。
示例 (中心化的5日移动平均，当前行为中心)：
SELECT sale_date, sales_amount,
AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) as centered_moving_avg_5day
FROM daily_sales;
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 整个分区。
示例 (用于FIRST_VALUE/LAST_VALUE获取分区首尾值)：
SELECT emp_name, department, salary,
FIRST_VALUE(emp_name) OVER (PARTITION BY department ORDER BY salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_paid_emp_in_dept,
LAST_VALUE(emp_name) OVER (PARTITION BY department ORDER BY salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_paid_emp_in_dept
FROM employees;
RANGE BETWEEN 的说明：
基于 ORDER BY 列的值来定义边界。Hive 对 RANGE 的支持，特别是涉及 INTERVAL 的复杂场景，可能不如其他数据库完善或直观，ROWS 通常更常用且行为更可预测。
总结： Hive 窗口函数通过灵活的分区、排序和窗口帧定义，极大增强了SQL在数据分析中的表达能力。掌握不同类型函数的特性和组合，是进行深度数据洞察的关键。
练习题
背景 Hive 表：
| 表名 | 字段 |
|---|---|
product_sales_hive |
product_id INT, sale_date DATE, sales_amount DECIMAL(10,2), region STRING, category STRING |
employee_activity_hive |
emp_id INT, activity_date DATE, activity_type STRING, duration_minutes INT, department STRING |
web_sessions_hive |
session_id STRING, user_id INT, start_time TIMESTAMP, end_time TIMESTAMP, country STRING |
(请自行准备样例数据)
题目：
- 为
product_sales_hive表中每个category内的产品，按sales_amount从高到低进行DENSE_RANK()排名。 - 计算
employee_activity_hive表中每个员工 (emp_id) 的总活动时长 (duration_minutes)，并将此总时长显示在该员工的每条活动记录旁。 - 对于
product_sales_hive表，计算每个产品 (product_id) 在每个销售日 (sale_date) 的销售额与该产品上一个销售日的销售额的差额。 - 找出
employee_activity_hive表中每个department内，activity_type为 ‘Meeting’ 且duration_minutes最长的前2条记录 (如果并列，选择任意2条)。 - 计算
product_sales_hive表中每个region按sale_date升序的累计销售总额。 - 将
employee_activity_hive表中department = 'Engineering'的员工，按其在activity_date介于 ‘2023-01-01’ 和 ‘2023-03-31’ 之间的总duration_minutes分为4个等级 (使用NTILE(4))。 - 对于
web_sessions_hive表，找出每个用户 (user_id) 当前会话 (session_id) 开始后，其下一个会话的开始时间。 - 计算
product_sales_hive表中每个产品当前销售日期及其前后各1个销售日 (共3个销售日窗口) 的平均销售额，按region分区。 - 在
employee_activity_hive表中，找出每个department中，duration_minutes最短的activity_type(如果多个活动类型时长并列最短，显示一个即可，并显示该时长)。 - 对于
product_sales_hive表，计算每次销售的sales_amount占该product_id在该region总销售额的百分比。
答案：
- 分类内产品销售额排名:
SELECT product_id, category, sales_amount,
DENSE_RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) as rank_in_category
FROM product_sales_hive;
- 员工总活动时长:
SELECT emp_id, activity_date, activity_type, duration_minutes,
SUM(duration_minutes) OVER (PARTITION BY emp_id) as total_emp_duration
FROM employee_activity_hive;
- 产品日销售额与上一日差额:
SELECT product_id, sale_date, sales_amount,
sales_amount - LAG(sales_amount, 1, 0.0) OVER (PARTITION BY product_id ORDER BY sale_date) as diff_from_prev_day_sales
FROM product_sales_hive;
- 部门会议时长Top2记录:
SELECT emp_id, activity_date, duration_minutes, department
FROM (
SELECT emp_id, activity_date, duration_minutes, department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY duration_minutes DESC) as rn
FROM employee_activity_hive
WHERE activity_type = 'Meeting'
) ranked_meetings
WHERE rn <= 2;
- 区域累计销售总额:
SELECT region, sale_date, sales_amount,
SUM(sales_amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_region_sales
FROM product_sales_hive;
- 工程部员工Q1活动时长等级:
SELECT emp_id, total_q1_duration,
NTILE(4) OVER (ORDER BY total_q1_duration DESC) as duration_tier
FROM (
SELECT emp_id, SUM(duration_minutes) as total_q1_duration
FROM employee_activity_hive
WHERE department = 'Engineering' AND activity_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY emp_id
) emp_q1_durations;
- 用户下一个会话开始时间:
SELECT user_id, session_id, start_time,
LEAD(start_time, 1) OVER (PARTITION BY user_id ORDER BY start_time) as next_session_start_time
FROM web_sessions_hive;
- 产品区域内3日移动平均销售额:
SELECT product_id, region, sale_date, sales_amount,
AVG(sales_amount) OVER (PARTITION BY product_id, region ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg_3day_sales
FROM product_sales_hive;
- 部门最短活动类型及时长:
SELECT department, activity_type, duration_minutes
FROM (
SELECT department, activity_type, duration_minutes,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY duration_minutes ASC, activity_type ASC) as rn -- activity_type ASC for tie-breaking
FROM employee_activity_hive
) ranked_activities
WHERE rn = 1;
- 销售额占产品区域总额百分比:
SELECT product_id, region, sale_date, sales_amount,
sales_amount * 100.0 / SUM(sales_amount) OVER (PARTITION BY product_id, region) as percentage_of_region_sales
FROM product_sales_hive;