Hive函数——窗口函数

  |   0 评论   |   2,473 浏览

准备数据

[caixiaoyu@gw3 ~]$ cat tmp_order.txt

1001,20170101,20170101,创建,150
1001,20170101,20170104,支付,150
1001,20170101,20170106,支付,150
1001,20170101,20170107,完成,150
1001,20170101,20170108,完成,150
1005,20170101,20170101,创建,270
1005,20170101,20170102,支付,270
1005,20170101,20170103,支付,270
1005,20170101,20170104,支付,270
1005,20170101,20170105,取消,270
1005,20170101,20170109,取消,270
1008,20170102,20170102,创建,300
1008,20170102,20170105,取消,300

建表

CREATE EXTERNAL TABLE `tmp_order`(
`id` int, 
`order_date` string, 
`update_date` string,
`state` string,
`fee` int)
ROW FORMAT SERDE 
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
'field.delim'=',', 
'line.delim'='\n') 
STORED AS INPUTFORMAT 
'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
'
org.apache.hadoop.hive.ql.io
.HiveIgnoreKeyTextOutputFormat'
LOCATION
'
hdfs://ns1/tmp/caixiaoyu/tmp_order
'

 

tmp_order.id

tmp_order.order_date

tmp_order.update_date

tmp_order.state

tmp_order.fee

110012017010120170101创建150
210012017010120170104支付150
310012017010120170106支付150
410012017010120170107完成150
510012017010120170108完成150
610052017010120170101创建270
710052017010120170102支付270
810052017010120170103支付270
910052017010120170104支付270
1010052017010120170105取消270
1110052017010120170109取消270
1210082017010220170102创建300
1310082017010220170105取消300

窗口函数

LAG

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)

SELECT id,
order_date,update_date,
state,
fee,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY update_date) AS num,
LAG(update_date, 1) OVER(PARTITION BY id ORDER BY update_date) AS end_date
FROM tmp_order

 

 

id

order_date

update_date

state

fee

num

end_date

110012017010120170101创建1501NULL
210012017010120170104支付150220170101
310012017010120170106支付150320170104
410012017010120170107完成150420170106
510012017010120170108完成150520170107
610052017010120170101创建2701NULL
710052017010120170102支付270220170101
810052017010120170103支付270320170102
910052017010120170104支付270420170103
1010052017010120170105取消270520170104
1110052017010120170109取消270620170105
1210082017010220170102创建3001NULL
1310082017010220170105取消300220170102

LEAD

与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)

SELECT id,
order_date,update_date,
state,
fee,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY update_date) AS num,
LEAD(update_date, 1) OVER(PARTITION BY id ORDER BY update_date) AS lead_date
FROM tmp_order

 

 

id

order_date

update_date

state

fee

num

lead_date

110012017010120170101创建150120170104
210012017010120170104支付150220170106
310012017010120170106支付150320170107
410012017010120170107完成150420170108
510012017010120170108完成1505NULL
610052017010120170101创建270120170102
710052017010120170102支付270220170103
810052017010120170103支付270320170104
910052017010120170104支付270420170105
1010052017010120170105取消270520170109
1110052017010120170109取消2706NULL
1210082017010220170102创建300120170105
1310082017010220170105取消3002NULL

FIRST_VALUE

取分组内排序后,截止到当前行,第一个值

SELECT id,
order_date,
update_date,
state,
fee,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY update_date) AS num,
FIRST_VALUE(update_date) OVER(PARTITION BY id ORDER BY update_date) AS first_date
FROM tmp_order

 

 

id

order_date

update_date

state

fee

num

first_date

110012017010120170101创建150120170101
210012017010120170104支付150220170101
310012017010120170106支付150320170101
410012017010120170107完成150420170101
510012017010120170108完成150520170101
610052017010120170101创建270120170101
710052017010120170102支付270220170101
810052017010120170103支付270320170101
910052017010120170104支付270420170101
1010052017010120170105取消270520170101
1110052017010120170109取消270620170101
1210082017010220170102创建300120170102
1310082017010220170105取消300220170102

LAST_VALUE

取分组内排序后,截止到当前行,最后一个值

SELECT id,
order_date,
update_date,
state,
fee,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY update_date) AS num,
LAST_VALUE(update_date) OVER(PARTITION BY id ORDER BY update_date) AS last_date
FROM tmp_order

 

OVER从句

用法

  1. 使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG 

  2. 使用PARTITION BY语句,使用一个或者多个原始数据类型的列 

  3. 使用PARTITION BY与ORDER BY语句,使用一个或者多个数据类型的分区或者排序列

  4. 使用窗口规范,语法如下



窗口规范

基本语法

ROWS betweenCURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND  UNBOUNDED FOLLOWING | [num] FOLLOWING| CURRENT ROW


RANGE between [num] PRECEDING  AND [num]FOLLOWING

ROWS是物理窗口,从行数上控制窗口的尺寸的;

RANGE是逻辑窗口,从列值上控制窗口的尺寸。

窗口从句条件


win.jpg

当ORDER BY后面缺少窗口从句条件,窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

OVER从句支持以下函数, 但是并不支持和窗口一起使用它们。 

Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank. 

Lead 和 Lag 函数.

实例

SELECT id,
order_date,
update_date,
state,
fee,
MAX(update_date) OVER(PARTITION BY id ORDER BY update_date) AS date1, -- 默认为从起点到当前行
MAX(update_date) OVER(PARTITION BY id ORDER BY update_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS date2, --从起点到当前行,结果同date1 
MAX(update_date) OVER(PARTITION BY id) AS date3, --分组内所有行
MAX(update_date) OVER(PARTITION BY id ORDER BY update_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS date4, --当前行+往前2行
MAX(update_date) OVER(PARTITION BY id ORDER BY update_date ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS date5, --当前行+往前2行+往后1行
MAX(update_date) OVER(PARTITION BY id ORDER BY update_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS date6 ---当前行+往后所有行
FROM tmp_order
ORDER BY id, update_date

 


读后有收获可以支付宝请作者喝咖啡