HIVE函数汇总--开窗函数篇

开窗函数

一、含义

开窗函数用于为行定义一个窗口（指运算将要操作的行的集合），它对一组值进行操作，不需要使用 Group By 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

二、语法

函数() over(partition by 列名1 
          order by 列名2 
          rows between 
          		[[unbounded|num] preceding | current row]
          and 
          		[[unbounded|num] following | current row]) 

over() 前为一个函数，如果是聚合函数，那么 order by 不能一起使用

rows between：作用为划分表中窗口边界

​ unbounded preceding：表示表中窗口无上边界

​ num preceding：表示表中窗口上界到距离当前行向上num行

​ current row：表示当前行

​ num following：表示表中窗口下界到距离当前行向下num行

​ unbounded following：表示表中窗口无下边界

​ rows between unbounded preceding and unbounded following：

​ 表示本窗口在表中无上边界也无下边界，此时可省略

三、分类

1、窗口函数

• lag(col,n,val)：查询当前行前边第n行数据，如果没有默认为val

• lead(col,n,val)：查询当前行后边第n行数据，如果没有默认为val

• first_value(col,true/false)：查询当前窗口第一条数据，第二个参数为true，跳过空值

• last_value(col,true/false)：查询当前窗口最后一条数据，第二个参数为true，跳过空值

2、排名开窗函数（RANK、DENSE_RANK、ROW_NUMBER、NTILE）

• 排名开窗函数可以单独使用 Order by 语句，也可以和 Partition By 同时使用

• Partition By 用于将结果集进行分组，开窗函数应用于每一组

• Order By 指定排名开窗函数的顺序，在排名开窗函数中必须使用 Order By 语句

3、聚合开窗函数（SUM、AVG、MAX、MIN、COUNT）

• 聚合开窗函数只能使用 Partition By 子句，Order By 不能与聚合开窗函数一同使用

四、使用方法-实例

1、窗口函数

select user_id,create_date,
       lag(create_date,1,'0000-00-00') over (partition by user_id) lag_date,
       lead(create_date,1,'9999-99-99') over (partition by user_id) lead_date,
       first_value(create_date) over (partition by user_id) first_date,
       last_value(create_date) over (partition by user_id) last_date
from order_info;

说明：

lag：获取上一行的create_date，如果没有，默认为0000-00-00

lead：获取上一行的create_date，如果没有，默认为9999-99-99

first_value：以user_id为分组，获取每组第一条create_date

last_value：以user_id为分组，获取每组最后一条create_date

2、排名开窗函数

row_number() over(partition by col1 order by col2)  

• 表示根据 col1列进行分组，在分组后的内部数据根据 col2列进行排序， 而此函数计算的值就表示每组内部排序后的顺序编号
• 实例
• 源表 (student) 数据

• 查询语句，根据学科分组，查询每个学生的成绩等级

select *,row_number() over (partition by course order by grade desc) from student

五、排名开窗函数（ROW_NUMBER、DENSE_RANK、RANK）区别

语句：

1、Row_Number：按行排序，行号

select *,row_number() over (partition by course order by grade desc) from student

结果：

说明：grade 列相同，相同数字，在前边排名优先，没有重复值

2、DENSE_RANK：排名相同时会重复，总数减少

select *,dense_rank() over (partition by course order by grade desc) from student

结果：

说明：grade 列相同，rank 连续排序，如 s、d 的数学成绩并列为第一，都标为 1，下一位将是第 2 名

3、Rank：排名相同时会重复，总数不减少

select *,rank() over (partition by course order by grade desc) from student

结果：

说明：跳跃排序，相同数据，排名相同，如 s、d 的数学成绩并列为第一，下一位将是第 3 名

HIVE

1、hive 操作语句–DDL篇
2、hive 操作语句–DML篇
3、hive 函数汇总–开窗函数篇
4、hive 函数汇总–日期函数篇