Hive 支持的核心开窗函数分类及常用函数

在 Hive 中，开窗函数（Window Functions）允许在数据集的特定窗口（子集） 上执行计算，同时保留原始行明细。以下是 Hive 支持的核心开窗函数分类及常用函数：

---

一、核心开窗函数分类

1. 聚合类开窗函数

在窗口内执行标准聚合操作，但不折叠行（保留明细）：

• SUM(col) OVER (...)
• AVG(col) OVER (...)
• COUNT(col) OVER (...)
• MAX(col) OVER (...)
• MIN(col) OVER (...)

2. 排序排名类函数

为窗口内数据生成排名或顺序号：

• ROW_NUMBER()
  唯一递增序号（相同值按顺序排不同号）

  SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;
  

• RANK()
  并列时跳过后续序号（如：1,1,3）
• DENSE_RANK()
  并列时不跳号（如：1,1,2）
• NTILE(n)
  将数据均分为 n 个桶并分配桶号（用于数据分位数分析）

3. 偏移分析函数

访问窗口内其他行的数据：

• LAG(col, n, default)
  获取当前行之前第 n 行的值（默认 n=1）
• LEAD(col, n, default)
  获取当前行之后第 n 行的值

  SELECT date, sales, 
         LAG(sales, 1, 0) OVER (ORDER BY date) AS prev_sales, -- 昨日销售额
         LEAD(sales, 1) OVER (ORDER BY date) AS next_sales    -- 明日销售额
  FROM daily_sales;
  

• FIRST_VALUE(col)
  返回窗口内第一行的值
• LAST_VALUE(col)
  返回窗口内最后一行的值（需注意窗口范围！）

4. 分布分析函数

计算数据的相对分布位置：

• CUME_DIST()
  计算当前行的累积分布（≤当前值的行数 / 总行数）
• PERCENT_RANK()
  计算当前行的百分比排名（(rank - 1) / (总行数 - 1)）

---

二、开窗函数关键语法：OVER() 子句

定义窗口范围是开窗函数的核心，通过 OVER() 指定：

函数名(...) OVER (
  [PARTITION BY col1, col2]  -- 按列分区（类似GROUP BY，但保留明细）
  [ORDER BY col3 [ASC|DESC]] -- 窗口内排序
  [window_frame]             -- 定义窗口范围（ROWS/RANGE）
)

窗口范围（Window Frame）类型

• ROWS BETWEEN ... AND ...
  基于物理行的偏移：

  SUM(sales) OVER (
    PARTITION BY dept 
    ORDER BY month 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 当前行+前2行（3个月滑动累加）
  )
  

• RANGE BETWEEN ... AND ...
  基于值范围的偏移（需搭配 ORDER BY）：

  AVG(price) OVER (
    ORDER BY date 
    RANGE BETWEEN INTERVAL 7 DAYS PRECEDING AND CURRENT ROW -- 7天内的均价
  )
  

---

三、经典应用场景

1. 滚动计算

   -- 计算每个员工当前月薪与部门平均薪的差值
   SELECT name, dept, salary,
          salary - AVG(salary) OVER (PARTITION BY dept) AS diff_from_avg
   FROM employees;
   

2. Top-N 分析

   -- 每个部门薪资最高的3名员工
   SELECT * FROM (
     SELECT name, dept, salary,
            ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
     FROM employees
   ) t WHERE rn <= 3;
   

3. 时间序列分析

   -- 计算销售额周环比增长率
   SELECT date, sales,
          (sales / LAG(sales, 7) OVER (ORDER BY date) - 1) * 100 AS week_growth_rate
   FROM daily_sales;
   

---

四、注意事项

1. 性能：开窗函数可能引发数据倾斜（如全局排序），需结合 PARTITION BY 分散计算。
2. 执行顺序：开窗函数在 JOIN/WHERE/GROUP BY 之后执行，但位于 SELECT 之前。
3. Hive 版本：部分高级函数（如 RANGE 基于时间间隔）需 Hive 2.0+ 支持。

开窗函数是 Hive 高级分析的利器，熟练掌握可高效实现复杂业务逻辑（如排名、滑动平均、趋势分析），避免冗长的多步子查询！