图文精华

About云-梭伦科技»专题 › 技术学习(版主发帖区) › 大数据学习 › Hive|数据仓库 › 连载型› Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
分享

Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

sstutu 2015-4-30 20:57:16 发表于 连载型 [显示全部楼层] 回帖奖励 阅读模式 关闭右栏 1 71853
问题导读

1.LAG功能是什么？
2.LEAD与LAG功能有什么相似的地方那个？
3.FIRST_VALUE与LAST_VALUE分别完成什么功能？





接上篇Hive分析窗口函数(二、三) NTILE,ROW_NUMBER,RANK,DENSE_RANK

继续学习这四个分析函数。
注意： 这几个函数不支持WINDOW子句。（什么是WINDOW子句，Hive分析窗口函数（一）SUM,AVG,MIN,MAx）

Hive版本为 apache-hive-0.13.1
数据准备：

  1. cookie1,2015-04-10 10:00:02,url2

  2. cookie1,2015-04-10 10:00:00,url1

  3. cookie1,2015-04-10 10:03:04,1url3

  4. cookie1,2015-04-10 10:50:05,url6

  5. cookie1,2015-04-10 11:00:00,url7

  6. cookie1,2015-04-10 10:10:00,url4

  7. cookie1,2015-04-10 10:50:01,url5

  8. cookie2,2015-04-10 10:00:02,url22

  9. cookie2,2015-04-10 10:00:00,url11

  10. cookie2,2015-04-10 10:03:04,1url33

  11. cookie2,2015-04-10 10:50:05,url66

  12. cookie2,2015-04-10 11:00:00,url77

  13. cookie2,2015-04-10 10:10:00,url44

  14. cookie2,2015-04-10 10:50:01,url55

  15.  

  16. CREATE EXTERNAL TABLE lxw1234 (

  17. cookieid string,

  18. createtime string,  --页面访问时间

  19. url STRING       --被访问页面

  20. ) ROW FORMAT DELIMITED 

  21. FIELDS TERMINATED BY ',' 

  22. stored as textfile location '/tmp/lxw11/';

  23.  

  24.  

  25. hive> select * from lxw1234;

  26. OK

  27. cookie1 2015-04-10 10:00:02     url2

  28. cookie1 2015-04-10 10:00:00     url1

  29. cookie1 2015-04-10 10:03:04     1url3

  30. cookie1 2015-04-10 10:50:05     url6

  31. cookie1 2015-04-10 11:00:00     url7

  32. cookie1 2015-04-10 10:10:00     url4

  33. cookie1 2015-04-10 10:50:01     url5

  34. cookie2 2015-04-10 10:00:02     url22

  35. cookie2 2015-04-10 10:00:00     url11

  36. cookie2 2015-04-10 10:03:04     1url33

  37. cookie2 2015-04-10 10:50:05     url66

  38. cookie2 2015-04-10 11:00:00     url77

  39. cookie2 2015-04-10 10:10:00     url44

  40. cookie2 2015-04-10 10:50:01     url55
复制代码




LAG

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

  1. SELECT cookieid,

  2. createtime,

  3. url,

  4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,

  5. LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,

  6. LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 

  7. FROM lxw1234;

  8.  

  9.  

  10. cookieid createtime             url    rn       last_1_time             last_2_time

  11. -------------------------------------------------------------------------------------------

  12. cookie1 2015-04-10 10:00:00     url1    1       1970-01-01 00:00:00     NULL

  13. cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:00:00     NULL

  14. cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:00:02     2015-04-10 10:00:00

  15. cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:03:04     2015-04-10 10:00:02

  16. cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:10:00     2015-04-10 10:03:04

  17. cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 10:50:01     2015-04-10 10:10:00

  18. cookie1 2015-04-10 11:00:00     url7    7       2015-04-10 10:50:05     2015-04-10 10:50:01

  19. cookie2 2015-04-10 10:00:00     url11   1       1970-01-01 00:00:00     NULL

  20. cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:00:00     NULL

  21. cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:00:02     2015-04-10 10:00:00

  22. cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:03:04     2015-04-10 10:00:02

  23. cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:10:00     2015-04-10 10:03:04

  24. cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 10:50:01     2015-04-10 10:10:00

  25. cookie2 2015-04-10 11:00:00     url77   7       2015-04-10 10:50:05     2015-04-10 10:50:01

  26.  

  27.  

  28. last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'  

  29.              cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00

  30.              cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02

  31.              cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01

  32. last_2_time: 指定了往上第2行的值，为指定默认值

  33.                                                  cookie1第一行，往上2行为NULL

  34.                                                  cookie1第二行，往上2行为NULL

  35.                                                  cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02

  36.                                                  cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01
复制代码


LEAD

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

  1. SELECT cookieid,

  2. createtime,

  3. url,

  4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,

  5. LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,

  6. LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time 

  7. FROM lxw1234;

  8.  

  9.  

  10. cookieid createtime             url    rn       next_1_time             next_2_time 

  11. -------------------------------------------------------------------------------------------

  12. cookie1 2015-04-10 10:00:00     url1    1       2015-04-10 10:00:02     2015-04-10 10:03:04

  13. cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:03:04     2015-04-10 10:10:00

  14. cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:10:00     2015-04-10 10:50:01

  15. cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:50:01     2015-04-10 10:50:05

  16. cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:50:05     2015-04-10 11:00:00

  17. cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 11:00:00     NULL

  18. cookie1 2015-04-10 11:00:00     url7    7       1970-01-01 00:00:00     NULL

  19. cookie2 2015-04-10 10:00:00     url11   1       2015-04-10 10:00:02     2015-04-10 10:03:04

  20. cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:03:04     2015-04-10 10:10:00

  21. cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:10:00     2015-04-10 10:50:01

  22. cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:50:01     2015-04-10 10:50:05

  23. cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:50:05     2015-04-10 11:00:00

  24. cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 11:00:00     NULL

  25. cookie2 2015-04-10 11:00:00     url77   7       1970-01-01 00:00:00     NULL

  26.  

  27. --逻辑与LAG一样，只不过LAG是往上，LEAD是往下。
复制代码



FIRST_VALUE

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

  1. SELECT cookieid,

  2. createtime,

  3. url,

  4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,

  5. FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 

  6. FROM lxw1234;

  7.  

  8. cookieid  createtime            url     rn      first1

  9. ---------------------------------------------------------

  10. cookie1 2015-04-10 10:00:00     url1    1       url1

  11. cookie1 2015-04-10 10:00:02     url2    2       url1

  12. cookie1 2015-04-10 10:03:04     1url3   3       url1

  13. cookie1 2015-04-10 10:10:00     url4    4       url1

  14. cookie1 2015-04-10 10:50:01     url5    5       url1

  15. cookie1 2015-04-10 10:50:05     url6    6       url1

  16. cookie1 2015-04-10 11:00:00     url7    7       url1

  17. cookie2 2015-04-10 10:00:00     url11   1       url11

  18. cookie2 2015-04-10 10:00:02     url22   2       url11

  19. cookie2 2015-04-10 10:03:04     1url33  3       url11

  20. cookie2 2015-04-10 10:10:00     url44   4       url11

  21. cookie2 2015-04-10 10:50:01     url55   5       url11

  22. cookie2 2015-04-10 10:50:05     url66   6       url11

  23. cookie2 2015-04-10 11:00:00     url77   7       url11
复制代码


LAST_VALUE

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

  1. SELECT cookieid,

  2. createtime,

  3. url,

  4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,

  5. LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 

  6. FROM lxw1234;

  7.  

  8.  

  9. cookieid  createtime            url    rn       last1  

  10. -----------------------------------------------------------------

  11. cookie1 2015-04-10 10:00:00     url1    1       url1

  12. cookie1 2015-04-10 10:00:02     url2    2       url2

  13. cookie1 2015-04-10 10:03:04     1url3   3       1url3

  14. cookie1 2015-04-10 10:10:00     url4    4       url4

  15. cookie1 2015-04-10 10:50:01     url5    5       url5

  16. cookie1 2015-04-10 10:50:05     url6    6       url6

  17. cookie1 2015-04-10 11:00:00     url7    7       url7

  18. cookie2 2015-04-10 10:00:00     url11   1       url11

  19. cookie2 2015-04-10 10:00:02     url22   2       url22

  20. cookie2 2015-04-10 10:03:04     1url33  3       1url33

  21. cookie2 2015-04-10 10:10:00     url44   4       url44

  22. cookie2 2015-04-10 10:50:01     url55   5       url55

  23. cookie2 2015-04-10 10:50:05     url66   6       url66

  24. cookie2 2015-04-10 11:00:00     url77   7       url77
复制代码

如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果
  1. SELECT cookieid,

  2. createtime,

  3. url,

  4. FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  

  5. FROM lxw1234;

  6.  

  7. cookieid  createtime            url     first2

  8. ----------------------------------------------

  9. cookie1 2015-04-10 10:00:02     url2    url2

  10. cookie1 2015-04-10 10:00:00     url1    url2

  11. cookie1 2015-04-10 10:03:04     1url3   url2

  12. cookie1 2015-04-10 10:50:05     url6    url2

  13. cookie1 2015-04-10 11:00:00     url7    url2

  14. cookie1 2015-04-10 10:10:00     url4    url2

  15. cookie1 2015-04-10 10:50:01     url5    url2

  16. cookie2 2015-04-10 10:00:02     url22   url22

  17. cookie2 2015-04-10 10:00:00     url11   url22

  18. cookie2 2015-04-10 10:03:04     1url33  url22

  19. cookie2 2015-04-10 10:50:05     url66   url22

  20. cookie2 2015-04-10 11:00:00     url77   url22

  21. cookie2 2015-04-10 10:10:00     url44   url22

  22. cookie2 2015-04-10 10:50:01     url55   url22

  23.  

  24. SELECT cookieid,

  25. createtime,

  26. url,

  27. LAST_VALUE(url) OVER(PARTITION BY cookieid) AS last2  

  28. FROM lxw1234;

  29.  

  30. cookieid  createtime            url     last2

  31. ----------------------------------------------

  32. cookie1 2015-04-10 10:00:02     url2    url5

  33. cookie1 2015-04-10 10:00:00     url1    url5

  34. cookie1 2015-04-10 10:03:04     1url3   url5

  35. cookie1 2015-04-10 10:50:05     url6    url5

  36. cookie1 2015-04-10 11:00:00     url7    url5

  37. cookie1 2015-04-10 10:10:00     url4    url5

  38. cookie1 2015-04-10 10:50:01     url5    url5

  39. cookie2 2015-04-10 10:00:02     url22   url55

  40. cookie2 2015-04-10 10:00:00     url11   url55

  41. cookie2 2015-04-10 10:03:04     1url33  url55

  42. cookie2 2015-04-10 10:50:05     url66   url55

  43. cookie2 2015-04-10 11:00:00     url77   url55

  44. cookie2 2015-04-10 10:10:00     url44   url55

  45. cookie2 2015-04-10 10:50:01     url55   url55
复制代码

如果想要取分组内排序后最后一个值，则需要变通一下：
  1. SELECT cookieid,

  2. createtime,

  3. url,

  4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,

  5. LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,

  6. FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 

  7. FROM lxw1234 

  8. ORDER BY cookieid,createtime;

  9.  

  10. cookieid  createtime            url     rn     last1    last2

  11. -------------------------------------------------------------

  12. cookie1 2015-04-10 10:00:00     url1    1       url1    url7

  13. cookie1 2015-04-10 10:00:02     url2    2       url2    url7

  14. cookie1 2015-04-10 10:03:04     1url3   3       1url3   url7

  15. cookie1 2015-04-10 10:10:00     url4    4       url4    url7

  16. cookie1 2015-04-10 10:50:01     url5    5       url5    url7

  17. cookie1 2015-04-10 10:50:05     url6    6       url6    url7

  18. cookie1 2015-04-10 11:00:00     url7    7       url7    url7

  19. cookie2 2015-04-10 10:00:00     url11   1       url11   url77

  20. cookie2 2015-04-10 10:00:02     url22   2       url22   url77

  21. cookie2 2015-04-10 10:03:04     1url33  3       1url33  url77

  22. cookie2 2015-04-10 10:10:00     url44   4       url44   url77

  23. cookie2 2015-04-10 10:50:01     url55   5       url55   url77

  24. cookie2 2015-04-10 10:50:05     url66   6       url66   url77

  25. cookie2 2015-04-10 11:00:00     url77   7       url77   url77
复制代码

提示：在使用分析函数的过程中，要特别注意ORDER BY子句，用的不恰当，统计出的结果就不是你所期望的



相关内容：


Hive分析窗口函数(一) SUM,AVG,MIN,MAX

Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK


Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE


Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP



来源：：西安 大数据 » Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
欢迎加入about云群425860289 、432264021 ，云计算爱好者群，关注about云腾讯认证空间
回复

使用道具 举报

自定义广告语

已有(1)人评论

跳转到指定楼层
正序浏览
凌飞羽 发表于 2015-4-30 21:23:25
回复

使用道具 举报

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

发表新帖
sstutu

实习版主

关注

  • 406

    主题

  • 948

    帖子

  • 143

    粉丝

TA的主题
关闭

推荐上一条 /2 下一条

关于我们· 联系我们· 加入我们· 小黑屋· 合作伙伴   

Copyright © 2001-2024 About云-梭伦科技 Powered by Discuz! X3.4 Licensed Discuz Team.

简书 / Smrz 京ICP备2020039040号 Wxb 简书网举报电话：021-34700000