开窗函数
一、含义
开窗函数用于为行定义一个窗口（指运算将要操作的行的集合），它对一组值进行操作，不需要使用 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
