Hive中的分析窗口函数 LAG,LEAD,FIRST_VALUE,LAST_VALUE的经典例子

绝地灬酷狼 2022-12-03 00:46 138阅读 0赞

## 题目背景： ##

> 登录表 t\_login:  user\_id,login\_time,login\_ip  
> 需求：  
> 1）、  加工出一张用户首末次登录的信息表。   
> user\_id,first\_login\_time,first\_login\_ip,last\_login\_time,last\_login\_ip
> 
> 2）、  新增一个字段--距离上次登录的时间间隔（sub\_dur）

## 0）、准备数据和表 ##

> 用户id,访问时间,访问国家  
> s001|100|en  
> s001|120|cn  
> s001|45|ls  
> s001|34|ca  
> s002|38|en  
> s002|150|cn  
> s002|90|ls  
> s003|90|ls  
> s003|33|ca  
> s004|39|zw
> 
> CREATE TABLE \`tb\_login\`(  
>   \`stu\_id\` string,   
>   \`login\_time\` int,  
>   \`cname\` string)  
> ROW FORMAT DELIMITED   
>   FIELDS TERMINATED BY '|'   
> STORED AS INPUTFORMAT   
>   'org.apache.hadoop.mapred.TextInputFormat'   
> OUTPUTFORMAT   
>   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> 
> load data  local inpath '/home/xxxx/tmp/tb\_login.txt' overwrite into table tb\_login;
> 
> hive> select \* from tb\_login;  
> OK  
> s001    100    en  
> s001    120    cn  
> s001    45    ls  
> s001    34    ca  
> s002    38    en  
> s002    150    cn  
> s002    90    ls  
> s003    90    ls  
> s003    33    ca  
> s004    39    zw  
> Time taken: 0.356 seconds, Fetched: 10 row(s)

## 1）、加工出一张用户首末次登录的信息表 ##

### 小试牛刀 ###

> spark-sql> select \* from tb\_login;  
> s001    100    en  
> s001    120    cn  
> s001    45    ls  
> s001    34    math  
> s002    38    en  
> s002    150    cn  
> s002    90    ls  
> s003    90    ls  
> s003    33    math  
> s004    39    zw
> 
>   
> select stu\_id,first\_value(login\_time) over(partition by stu\_id order by login\_time asc) first\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time asc) first\_cname  
> from tb\_login;  
> s001    34  ca                                                                
> s001    34    ca  
> s001    34    ca  
> s001    34    ca  
> s002    38    en  
> s002    38    en  
> s002    38    en  
> s003    33    ca  
> s003    33    ca  
> s004    39    zw
> 
> select stu\_id,last\_value(login\_time) over(partition by stu\_id order by login\_time asc) last\_login\_time,last\_value(cname) over(partition by stu\_id order by login\_time asc) last\_cname  
> from tb\_login;  
> s001    34      ca                                                                
> s001    45    ls  
> s001    100    en  
> s001    120    cn  
> s002    38    en  
> s002    90    ls  
> s002    150    cn  
> s003    33    ca  
> s003    90    ls  
> s004    39    zw
> 
> select stu\_id,last\_value(login\_time) over(partition by stu\_id order by login\_time desc) last\_login\_time,last\_value(cname) over(partition by stu\_id order by login\_time desc) last\_cname  
> from tb\_login;  
> s001    120     cn                                                                
> s001    100    en  
> s001    45    ls  
> s001    34    ca  
> s002    150    cn  
> s002    90    ls  
> s002    38    en  
> s003    90    ls  
> s003    33    ca  
> s004    39    zw
> 
> select stu\_id  
> ,first\_value(login\_time) over(partition by stu\_id order by login\_time asc) first\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time asc) first\_cname  
> ,last\_value(login\_time) over(partition by stu\_id order by login\_time asc) last\_login\_time,last\_value(cname) over(partition by stu\_id order by login\_time asc) last\_cname  
> from tb\_login;
> 
> s001    34  math    34  math                                              
> s001    34    math    45    ls  
> s001    34    math    100    en  
> s001    34    math    120    cn  
> s002    38    en    38    en  
> s002    38    en    90    ls  
> s002    38    en    150    cn  
> s003    33    math    33    math  
> s003    33    math    90    ls  
> s004    39    zw    39    zw
> 
> select stu\_id  
> ,last\_value(login\_time) over(partition by stu\_id order by login\_time asc) first\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time asc) first\_cname  
> ,last\_value(login\_time) over(partition by stu\_id order by login\_time desc) last\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time desc) last\_cname  
> from tb\_login
> 
> s001    120     ca      120     cn                                                
> s001    100    ca    100    cn  
> s001    45    ca    45    cn  
> s001    34    ca    34    cn  
> s002    150    en    150    cn  
> s002    90    en    90    cn  
> s002    38    en    38    cn  
> s003    90    ca    90    ls  
> s003    33    ca    33    ls  
> s004    39    zw    39    zw

### 正确答案 ###

> select stu\_id  
> ,first\_value(login\_time) over(partition by stu\_id order by login\_time asc) first\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time asc) first\_cname  
> ,first\_value(login\_time) over(partition by stu\_id order by login\_time desc) last\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time desc) last\_cname  
> from tb\_login
> 
> s001    34      ca      120     cn                                                
> s001    34    ca    120    cn  
> s001    34    ca    120    cn  
> s001    34    ca    120    cn  
> s002    38    en    150    cn  
> s002    38    en    150    cn  
> s002    38    en    150    cn  
> s003    33    ca    90    ls  
> s003    33    ca    90    ls  
> s004    39    zw    39    zw
> 
> select distinct stu\_id  
> ,first\_value(login\_time) over(partition by stu\_id order by login\_time asc) first\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time asc) first\_cname  
> ,first\_value(login\_time) over(partition by stu\_id order by login\_time desc) last\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time desc) last\_cname  
> from tb\_login;
> 
> s001    34      ca      120     cn                                                
> s002    38    en    150    cn  
> s003    33    ca    90    ls  
> s004    39    zw    39    zw

根据[hive中groupby和distinct区别以及性能比较][hive_groupby_distinct]，能够distinct的地方，最好换成 group by：都会在map阶段count，但reduce阶段，distinct只有一个， group by 可以有多个进行并行聚合，所以group by会快。

> select stu\_id,first\_login\_time,first\_cname,last\_login\_time,last\_cname  
> from   
> (  
> select stu\_id  
> ,first\_value(login\_time) over(partition by stu\_id order by login\_time asc) first\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time asc) first\_cname  
> ,first\_value(login\_time) over(partition by stu\_id order by login\_time desc) last\_login\_time,first\_value(cname) over(partition by stu\_id order by login\_time desc) last\_cname  
> from tb\_login  
> )tb\_final  
> group by stu\_id,first\_login\_time,first\_cname,last\_login\_time,last\_cname;
> 
> s001    34      ca      120     cn                                                
> s002    38    en    150    cn  
> s003    33    ca    90    ls  
> s004    39    zw    39    zw

## 2）、新增一个字段--距离上次登录的时间间隔（sub\_dur) ##

##    小试牛刀 ##

> select stu\_id,login\_time,cname  
> ,row\_number() over(partition by stu\_id order by login\_time asc) as rk  
> ,lag(login\_time,1) over(partition by stu\_id order by login\_time asc) last\_login\_time  
> ,login\_time-lag(login\_time,1) over(partition by stu\_id order by login\_time asc) sub\_dur  
> from tb\_login;
> 
> OK  
> s001    34      ca      1       NULL    NULL                                      
> s001    45    ls    2    34    11  
> s001    100    en    3    45    55  
> s001    120    cn    4    100    20  
> s002    38    en    1    NULL    NULL  
> s002    90    ls    2    38    52  
> s002    150    cn    3    90    60  
> s003    33    ca    1    NULL    NULL  
> s003    90    ls    2    33    57  
> s004    39    zw    1    NULL    NULL  
> Time taken: 32.27 seconds, Fetched: 10 row(s)
> 
> select stu\_id,login\_time,cname  
> ,row\_number() over(partition by stu\_id order by login\_time asc) as rk  
> ,lag(login\_time,1,0) over(partition by stu\_id order by login\_time asc) last\_login\_time  
> ,login\_time-lag(login\_time,1,0) over(partition by stu\_id order by login\_time asc) sub\_dur  
> from tb\_login;  
> s001    34    ca    1    0    34  
> s001    45    ls    2    34    11  
> s001    100    en    3    45    55  
> s001    120    cn    4    100    20  
> s002    38    en    1    0    38  
> s002    90    ls    2    38    52  
> s002    150    cn    3    90    60  
> s003    33    ca    1    0    33  
> s003    90    ls    2    33    57  
> s004    39    zw    1    0    39
> 
> Time taken: 48.621 seconds, Fetched: 10 row(s)

### 正确答案 ###

> select stu\_id,login\_time,cname  
> ,row\_number() over(partition by stu\_id order by login\_time asc) as rk  
> ,lag(login\_time,1,0) over(partition by stu\_id order by login\_time asc) last\_login\_time  
> ,login\_time-lag(login\_time,1,login\_time) over(partition by stu\_id order by login\_time asc) sub\_dur  
> from tb\_login;
> 
> s001    34  ca  1   0    0                                         
> s001    45    ls    2    34    11  
> s001    100    en    3    45    55  
> s001    120    cn    4    100    20  
> s002    38    en    1    0    0  
> s002    90    ls    2    38    52  
> s002    150    cn    3    90    60  
> s003    33    ca    1    0    0  
> s003    90    ls    2    33    57  
> s004    39    zw    1    0    0

参考：http://lxw1234.com/archives/2015/04/190.htm  
      https://www.cnblogs.com/wqbin/p/11050970.html

[hive_groupby_distinct]: https://www.cnblogs.com/wqbin/p/11050970.html