hive常见的开窗函数

### Hive 常用开窗函数概述 开窗函数是一类特殊的 SQL 函数，在查询结果的某个窗口内进行计算。这类函数不会将结果集缩减到单一行，而是保留所有行并在此基础上执行特定操作[^1]。 #### 开窗函数基本语法结构 开窗函数的一般形式如下： ```sql function_name (expression) OVER ( [PARTITION BY partition_expression] [ORDER BY sort_expression] [window_frame_clause] ) ``` - `PARTITION BY` 定义逻辑分区。 - `ORDER BY` 对指定列排序。 - `window_frame_clause` 设置窗口范围，默认情况下为整个表。 #### 常见开窗函数介绍及示例 ##### 1. 排名函数 这些函数用于给定数据集中按一定顺序排列记录，并分配相应的排名编号。 - **ROW_NUMBER()**: 返回每一分区内的唯一序号。 ```sql SELECT studentId, language, math, english, ROW_NUMBER() OVER(PARTITION BY departmentId ORDER BY math DESC) AS row_num FROM student_scores; ``` - **RANK()**: 类似于 `ROW_NUMBER()` ，但是会处理相同值的情况，即跳过重复项后的下一个自然数作为后续排名。 ```sql SELECT studentId, language, math, english, RANK() OVER(PARTITION BY departmentId ORDER BY math DESC) AS rank_val FROM student_scores; ``` - **DENSE_RANK()**: 和 `RANK()` 功能相似，区别在于不跳跃排名次序。 ```sql SELECT studentId, language, math, english, DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY math DESC) AS dense_rank_val FROM student_scores; ``` ##### 2. 分析函数 此类函数主要用于统计分析目的，提供更复杂的聚合能力而不改变原始数据量级。 - **LEAD()/LAG()**: 获取当前行前后若干行的数据。 ```sql SELECT studentId, language, math, english, LAG(math, 1) OVER(ORDER BY studentId) prev_math_score, LEAD(math, 1) OVER(ORDER BY studentId) next_math_score FROM student_scores; ``` - **FIRST_VALUE/LAST_VALUE**: 取得窗口中的首个或最后一个值。 ```sql SELECT studentId, language, math, english, FIRST_VALUE(math) OVER(PARTITION BY classId ORDER BY math DESC) highest_math_in_class FROM student_scores; ``` ##### 3. 聚合函数 虽然传统意义上的聚合函数（如 SUM(), AVG() 等）也可以作为开窗函数使用，但这里主要指那些专门为窗口运算设计的功能更强的版本。 - **SUM() / COUNT() / MIN() / MAX()** 这些标准SQL聚合函数可以直接应用于窗口上下文中，从而实现更加灵活多样的数据分析需求。 ```sql SELECT studentId, language, math, english, SUM(math) OVER(PARTITION BY departmentId) total_math_by_dept FROM student_scores; ```