Hive中有哪些开窗函数

Hive 的 开窗函数（Window Function） 是处理 分组内排序、累计、移动平均、Top-N 等场景的核心利器，自 Hive 0.11 起支持。它能在不减少行数的前提下，对每行数据进行“窗口内计算”。

---

✅ 一、开窗函数基本语法

function_name(expression) OVER (
  [PARTITION BY partition_expr, ...]   -- 分组（类似 GROUP BY）
  [ORDER BY order_expr [ASC|DESC], ...] -- 窗口内排序
  [window_frame]                       -- 窗口范围（可选）
)

🔑 核心：OVER() 定义了“窗口”——即当前行参与计算的数据范围。

---

✅ 二、常用开窗函数分类

1. 排名函数（Ranking Functions）

函数	特点	示例
ROW_NUMBER()	无并列，连续排名	1,2,3,4
RANK()	有并列，跳过后续名次	1,1,3,4
DENSE_RANK()	有并列，不跳过名次	1,1,2,3

示例：每个部门薪资 Top 3 员工

SELECT 
  dept,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employee
WHERE rn <= 3;  -- ❌ 错误！不能在 WHERE 中用别名

✅ 正确写法（子查询 or CTE）：

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

---

2. 聚合函数（作为开窗函数）

普通聚合（如 SUM()）会压缩行数，但加 OVER() 后保留原行，并在窗口内聚合。

函数	用途
SUM(col) OVER(...)	累计和
AVG(col) OVER(...)	移动平均
COUNT(col) OVER(...)	窗口内计数
MAX/MIN(col) OVER(...)	窗口内最值

示例：用户每日登录的累计天数

SELECT 
  user_id,
  login_date,
  COUNT(1) OVER (PARTITION BY user_id ORDER BY login_date) AS cum_days
FROM user_login_log;

---

3. 分析函数（Analytic Functions）

函数	说明
LAG(col, n, default)	取当前行前第 n 行的值
LEAD(col, n, default)	取当前行后第 n 行的值
FIRST_VALUE(col)	窗口内第一行值
LAST_VALUE(col)	窗口内最后一行值（注意默认窗口范围！）

示例：计算用户每次登录与上一次的时间间隔

SELECT 
  user_id,
  login_time,
  LAG(login_time, 1) OVER (PARTITION BY user_id ORDER BY login_time) AS last_login,
  UNIX_TIMESTAMP(login_time) - UNIX_TIMESTAMP(LAG(login_time, 1) OVER (...)) AS gap_sec
FROM user_login;

---

✅ 三、窗口范围（Window Frame）详解

当 ORDER BY 存在时，窗口默认为 从第一行到当前行（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。

常见窗口定义：

写法	含义
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW	当前行 + 前2行（共3行）
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	整个分区
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW	时间范围内（需配合时间排序）

示例：7日滚动平均销售额

SELECT 
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date 
    RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
  ) AS avg_7d
FROM sales;

⚠️ 注意：RANGE 要求 ORDER BY 字段是数值或日期类型；ROWS 按行数计算。

---

✅ 四、性能与注意事项

问题	建议
大窗口全量计算	避免 ORDER BY 无限制（如全表排序），会导致单 Reducer
LAST_VALUE 结果异常	默认窗口是“到当前行”，若要整个分区最后值，需显式指定： LAST_VALUE(col) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
NULL 处理	LAG/LEAD 可指定默认值：LAG(col, 1, 'N/A')
结合 PARTITION BY	必须合理分组，避免数据倾斜

---

📌 面试总结（一句话）

Hive 开窗函数通过 OVER(PARTITION BY ... ORDER BY ...) 定义计算窗口，实现分组内排名、累计、移动计算等需求，典型应用包括 Top-N、留存分析、时间间隔计算等；使用时需注意窗口范围、性能瓶颈（避免全局排序）和 LAST_VALUE 的默认行为。

掌握开窗函数，是写出高效、简洁 HQL 的关键能力，也是数仓工程师的必备技能。