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 ： 返回组中数据窗口的最后一个值。