Hive窗口函数用法_hive排名函数怎么用-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_50227709/article/details/146222372

Hive 的窗口函数（或称分析函数）使你能在不改变原始结果集行数的前提下，对数据进行排序、分组、累计、排名等复杂的计算。这些函数通常与 OVER 子句一起使用，可以定义“窗口”——也就是一个数据子集，用于进行聚合、排序、偏移或其它分析计算。下面详细介绍常见的 Hive 窗口函数及其使用方法。

1. 窗口函数的基本语法

所有窗口函数都需要使用 OVER 子句，基本语法如下：

<窗口函数> OVER (
  [PARTITION BY partition_column1, partition_column2, ...]  
  [ORDER BY order_column [ASC|DESC] [,...]]
  [ROWS BETWEEN <起始行> AND <结束行>]
)

PARTITION BY 用于把数据分成不同的分区（组），每个分区内的窗口函数独立计算。
ORDER BY 用于指定分区内行的排序顺序。
ROWS BETWEEN 可选，用于明确定义计算范围，默认值通常为“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”（即从分区第一行到当前行）。

2. 常见窗口函数分类及详解

2.1 排名函数

这些函数主要用于在分区内为行排序并分配排名。

row_number()
为每行分配一个唯一的连续序号，排序依据 ORDER BY。
示例：
```
SELECT emp_id, salary,
       row_number() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employee;
```
说明：如果同一部门中有相同薪水，row_number() 仍会为每行赋予不同的序号。
rank()
根据排序为每行分配排名，相同值获得相同排名，后续排名会跳过（出现排名“空缺”）。
示例：
```
SELECT emp_id, salary,
       rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employee;
```
说明：如果部门内最高薪水有两个人，都为 1 名，下一个人排名将是 3。
dense_rank()
与 rank() 类似，但不会出现排名跳跃。
示例：
```
SELECT emp_id, salary,
       dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM employee;
```
说明：如果最高薪有两个人，都为 1 名，下一个人则为 2。

percent_rank()
计算当前行的排名在分区内所占比例（0~1）。
示例：

SELECT emp_id, salary,
       percent_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS pr
FROM employee;

cume_dist()
计算当前行及之前所有行占总行数的比例，即累计分布。
示例：

SELECT emp_id, salary,
       cume_dist() OVER (PARTITION BY dept ORDER BY salary DESC) AS cd
FROM employee;

ntile(n)
将分区内的行平均分为 n 组，并返回每行所在的组号（1 到 n）。
示例：

SELECT emp_id, salary,
       ntile(4) OVER (PARTITION BY dept ORDER BY salary DESC) AS quartile
FROM employee;

2.2 偏移函数

这些函数允许你在当前行之外引用其他行的数据。

lag(column, offset, default)
返回当前行前 offset 行中指定列的值。如果不存在，则返回 default（可选）。
示例：
```
SELECT emp_id, salary,
       lag(salary, 1, 0) OVER (ORDER BY emp_id) AS prev_salary
FROM employee;
```
说明：用于计算行与前一行之间的差异。

lead(column, offset, default)
与 lag() 类似，但返回当前行之后的值。
示例：

SELECT emp_id, salary,
       lead(salary, 1, 0) OVER (ORDER BY emp_id) AS next_salary
FROM employee;

2.3 值提取函数

这些函数用于提取窗口内的特定行的值。

first_value(column)
返回窗口（通常由 ORDER BY 定义）内的第一行值。
示例：

SELECT emp_id, salary,
       first_value(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS first_salary
FROM employee;

last_value(column)
返回窗口内的最后一行值。
注意： 默认窗口框架可能只计算到当前行，故常需要使用完整窗口范围，如：

SELECT emp_id, salary,
       last_value(salary) OVER (PARTITION BY dept ORDER BY hire_date
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary
FROM employee;

2.4 聚合窗口函数

你可以将普通聚合函数（如 sum、avg、min、max、count）与 OVER 子句结合使用，来计算累计值、移动平均等。

运行累计和
示例：

SELECT emp_id, salary,
       sum(salary) OVER (PARTITION BY dept ORDER BY emp_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employee;

说明：在每个部门内，按照 emp_id 排序，对薪水进行累计求和。

3. 窗口函数的使用注意事项

默认窗口框架：
当仅指定 ORDER BY 时，默认窗口范围是 “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”。这对于累计求和、排名等常见场景很合适，但对于 last_value() 等函数，如果希望获取整个分区的最后值，可能需要明确指定窗口范围为 “ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”。
分区与排序：
使用 PARTITION BY 将数据分组，使得每个分组内的计算相互独立；使用 ORDER BY 则决定了窗口内数据的顺序，这对排名、lag/lead 函数尤为关键。
Hive 版本：
不同 Hive 版本对窗口函数的支持可能略有差异，部分函数（如 nth_value）在较老版本中可能不可用。建议在使用前查阅当前版本的文档。