hive常用的开窗函数用法

一row_number() over()
使用row_number函数，对表中的数据按照省份分组，按照人数倒序排序并进行标记：

select
province_name
,city_name
,pc_cnt
,row_number() over(partition by province_name order by pc_cnt desc) as rn
from
wedw_tmp.t_rn
;

然后，利用上面的结果，查询出rn<=2的即为最终需求

select
tmp.province_name
,tmp.city_name
,tmp.pc_cnt
from
(
select
province_name
,city_name
,pc_cnt
,row_number() over(partition by province_name order by pc_cnt desc) as rn
from
wedw_tmp.t_rn
) tmp
where tmp.rn <= 2
;

二 sum() over()
对于每个人的一个月的销售额和累计到当前月的销售总额

select
user_name
,month_id
,sale_amt
,sum(sale_amt) over(partition by user_name order by month_id rows between unbounded preceding and current row) as all_sale_amt
from wedw_tmp.t_sum_over;

注：这些窗口的划分都是在分区内部！超过分区大小就无效了
可以看到如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;
关键是理解 ROWS BETWEEN 含义,也叫做window子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点
其他测试：

select
user_name
,month_id
,sale_amt
,==sum(sale_amt) over(partition by user_name order by month_id) as all_sale_amt1 ==–默认为从起点行到当前行
,sum(sale_amt) over(partition by user_name order by month_id rows between unbounded preceding and current row) as all_sale_amt2 --从起点行到当前行
,sum(sale_amt) over(partition by user_name order by month_id rows between 3 preceding and current row) as all_sale_amt3 --当前行及往前3行之和
,sum(sale_amt) over(partition by user_name order by month_id rows between 3 preceding and 1 following) as all_sale_amt4 --当前行及往前3行往后1行之和
,sum(sale_amt) over(partition by user_name order by month_id rows between current row and unbounded following) as all_sale_amt5 --当前行及往后所有行之和
from wedw_tmp.t_sum_over;

三lag/lead() over()
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
以lag() over()为例：
数据准备:

create table t_hosp(
user_name string
,age int
,in_hosp date
,out_hosp date)
row format delimited fields terminated by ‘,’;
xiaohong,25,2020-05-12,2020-06-03
xiaoming,30,2020-06-06,2020-06-15
xiaohong,25,2020-06-14,2020-06-19
xiaoming,30,2020-06-20,2020-07-02
user_name:用户名
age:年龄
in_hosp:住院日期
out_hosp：出院日期

需求：求同一个患者每次住院与上一次出院的时间间隔
第一步：

select
user_name
,age
,in_hosp
,out_hosp
,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc) AS pre_out_date
from
t_hosp
;
其中，LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc)
表示根据user_name分组按照out_hosp升序取每条数据的上一条数据的out_hosp，
如果上一条数据为空，则使用默认值in_hosp来代替

第二步：每条数据的in_hosp与pre_out_date的差值即本次住院日期与上次出院日期的间隔:
select
user_name
,age
,in_hosp
,out_hosp
,==datediff(in_hosp,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc)) as days ==
from
t_hosp
;

补充：
一.聚合分析函数
SUM ： 该函数计算组中表达式的累积和
COUNT ： 对一组内发生的事情进行累积计数
MIN ： 在一个组中的数据窗口中查找表达式的最小值
MAX ： 在一个组中的数据窗口中查找表达式的最大值
AVG ： 用于计算一个组和数据窗口内表达式的平均值。
二.排名分析函数
ROW_NUMBER ： – 正常排序[1,2,3,4] – 必须有order_by
RANK ： – 跳跃排序[1,2,2,4] – 必须有order_by
DENSE_RANK ： – 密集排序[1,2,2,3] – 必须有order_by
FIRST ： 从DENSE_RANK返回的集合中取出排在最前面的一个值的行
LAST ： 从DENSE_RANK返回的集合中取出排在最后面的一个值的行
FIRST_VALUE ： 返回组中数据窗口的第一个值
LAST_VALUE ： 返回组中数据窗口的最后一个值。