天天看點
傳回

Hive分析視窗函數(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

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. CREATE EXTERNAL TABLE lxw1234 (
  16. cookieid string,
  17. createtime string, --頁面通路時間
  18. url STRING --被通路頁面
  19. ) ROW FORMAT DELIMITED
  20. FIELDS TERMINATED BY ','
  21. stored as textfile location '/tmp/lxw11/';
  22. hive> select * from lxw1234;
  23. OK
  24. cookie1 2015-04-10 10:00:02 url2
  25. cookie1 2015-04-10 10:00:00 url1
  26. cookie1 2015-04-10 10:03:04 1url3
  27. cookie1 2015-04-10 10:50:05 url6
  28. cookie1 2015-04-10 11:00:00 url7
  29. cookie1 2015-04-10 10:10:00 url4
  30. cookie1 2015-04-10 10:50:01 url5
  31. cookie2 2015-04-10 10:00:02 url22
  32. cookie2 2015-04-10 10:00:00 url11
  33. cookie2 2015-04-10 10:03:04 1url33
  34. cookie2 2015-04-10 10:50:05 url66
  35. cookie2 2015-04-10 11:00:00 url77
  36. cookie2 2015-04-10 10:10:00 url44
  37. 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. cookieid createtime url rn last_1_time last_2_time
  9. -------------------------------------------------------------------------------------------
  10. cookie1 2015-04-10 10:00:00 url1 1 1970-01-01 00:00:00 NULL
  11. cookie1 2015-04-10 10:00:02 url2 2 2015-04-10 10:00:00 NULL
  12. cookie1 2015-04-10 10:03:04 1url3 3 2015-04-10 10:00:02 2015-04-10 10:00:00
  13. cookie1 2015-04-10 10:10:00 url4 4 2015-04-10 10:03:04 2015-04-10 10:00:02
  14. cookie1 2015-04-10 10:50:01 url5 5 2015-04-10 10:10:00 2015-04-10 10:03:04
  15. cookie1 2015-04-10 10:50:05 url6 6 2015-04-10 10:50:01 2015-04-10 10:10:00
  16. cookie1 2015-04-10 11:00:00 url7 7 2015-04-10 10:50:05 2015-04-10 10:50:01
  17. cookie2 2015-04-10 10:00:00 url11 1 1970-01-01 00:00:00 NULL
  18. cookie2 2015-04-10 10:00:02 url22 2 2015-04-10 10:00:00 NULL
  19. cookie2 2015-04-10 10:03:04 1url33 3 2015-04-10 10:00:02 2015-04-10 10:00:00
  20. cookie2 2015-04-10 10:10:00 url44 4 2015-04-10 10:03:04 2015-04-10 10:00:02
  21. cookie2 2015-04-10 10:50:01 url55 5 2015-04-10 10:10:00 2015-04-10 10:03:04
  22. cookie2 2015-04-10 10:50:05 url66 6 2015-04-10 10:50:01 2015-04-10 10:10:00
  23. cookie2 2015-04-10 11:00:00 url77 7 2015-04-10 10:50:05 2015-04-10 10:50:01
  24. last_1_time: 指定了往上第1行的值，default為'1970-01-01 00:00:00'
  25. cookie1第一行，往上1行為NULL,是以取預設值 1970-01-01 00:00:00
  26. cookie1第三行，往上1行值為第二行值，2015-04-10 10:00:02
  27. cookie1第六行，往上1行值為第五行值，2015-04-10 10:50:01
  28. last_2_time: 指定了往上第2行的值，為指定預設值
  29. cookie1第一行，往上2行為NULL
  30. cookie1第二行，往上2行為NULL
  31. cookie1第四行，往上2行為第二行值，2015-04-10 10:00:02
  32. 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. cookieid createtime url rn next_1_time next_2_time
  9. -------------------------------------------------------------------------------------------
  10. cookie1 2015-04-10 10:00:00 url1 1 2015-04-10 10:00:02 2015-04-10 10:03:04
  11. cookie1 2015-04-10 10:00:02 url2 2 2015-04-10 10:03:04 2015-04-10 10:10:00
  12. cookie1 2015-04-10 10:03:04 1url3 3 2015-04-10 10:10:00 2015-04-10 10:50:01
  13. cookie1 2015-04-10 10:10:00 url4 4 2015-04-10 10:50:01 2015-04-10 10:50:05
  14. cookie1 2015-04-10 10:50:01 url5 5 2015-04-10 10:50:05 2015-04-10 11:00:00
  15. cookie1 2015-04-10 10:50:05 url6 6 2015-04-10 11:00:00 NULL
  16. cookie1 2015-04-10 11:00:00 url7 7 1970-01-01 00:00:00 NULL
  17. cookie2 2015-04-10 10:00:00 url11 1 2015-04-10 10:00:02 2015-04-10 10:03:04
  18. cookie2 2015-04-10 10:00:02 url22 2 2015-04-10 10:03:04 2015-04-10 10:10:00
  19. cookie2 2015-04-10 10:03:04 1url33 3 2015-04-10 10:10:00 2015-04-10 10:50:01
  20. cookie2 2015-04-10 10:10:00 url44 4 2015-04-10 10:50:01 2015-04-10 10:50:05
  21. cookie2 2015-04-10 10:50:01 url55 5 2015-04-10 10:50:05 2015-04-10 11:00:00
  22. cookie2 2015-04-10 10:50:05 url66 6 2015-04-10 11:00:00 NULL
  23. cookie2 2015-04-10 11:00:00 url77 7 1970-01-01 00:00:00 NULL
  24. --邏輯與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. cookieid createtime url rn first1
  8. ---------------------------------------------------------
  9. cookie1 2015-04-10 10:00:00 url1 1 url1
  10. cookie1 2015-04-10 10:00:02 url2 2 url1
  11. cookie1 2015-04-10 10:03:04 1url3 3 url1
  12. cookie1 2015-04-10 10:10:00 url4 4 url1
  13. cookie1 2015-04-10 10:50:01 url5 5 url1
  14. cookie1 2015-04-10 10:50:05 url6 6 url1
  15. cookie1 2015-04-10 11:00:00 url7 7 url1
  16. cookie2 2015-04-10 10:00:00 url11 1 url11
  17. cookie2 2015-04-10 10:00:02 url22 2 url11
  18. cookie2 2015-04-10 10:03:04 1url33 3 url11
  19. cookie2 2015-04-10 10:10:00 url44 4 url11
  20. cookie2 2015-04-10 10:50:01 url55 5 url11
  21. cookie2 2015-04-10 10:50:05 url66 6 url11
  22. 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. cookieid createtime url rn last1
  8. -----------------------------------------------------------------
  9. cookie1 2015-04-10 10:00:00 url1 1 url1
  10. cookie1 2015-04-10 10:00:02 url2 2 url2
  11. cookie1 2015-04-10 10:03:04 1url3 3 1url3
  12. cookie1 2015-04-10 10:10:00 url4 4 url4
  13. cookie1 2015-04-10 10:50:01 url5 5 url5
  14. cookie1 2015-04-10 10:50:05 url6 6 url6
  15. cookie1 2015-04-10 11:00:00 url7 7 url7
  16. cookie2 2015-04-10 10:00:00 url11 1 url11
  17. cookie2 2015-04-10 10:00:02 url22 2 url22
  18. cookie2 2015-04-10 10:03:04 1url33 3 1url33
  19. cookie2 2015-04-10 10:10:00 url44 4 url44
  20. cookie2 2015-04-10 10:50:01 url55 5 url55
  21. cookie2 2015-04-10 10:50:05 url66 6 url66
  22. 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. cookieid createtime url first2
  7. ----------------------------------------------
  8. cookie1 2015-04-10 10:00:02 url2 url2
  9. cookie1 2015-04-10 10:00:00 url1 url2
  10. cookie1 2015-04-10 10:03:04 1url3 url2
  11. cookie1 2015-04-10 10:50:05 url6 url2
  12. cookie1 2015-04-10 11:00:00 url7 url2
  13. cookie1 2015-04-10 10:10:00 url4 url2
  14. cookie1 2015-04-10 10:50:01 url5 url2
  15. cookie2 2015-04-10 10:00:02 url22 url22
  16. cookie2 2015-04-10 10:00:00 url11 url22
  17. cookie2 2015-04-10 10:03:04 1url33 url22
  18. cookie2 2015-04-10 10:50:05 url66 url22
  19. cookie2 2015-04-10 11:00:00 url77 url22
  20. cookie2 2015-04-10 10:10:00 url44 url22
  21. cookie2 2015-04-10 10:50:01 url55 url22
  22. SELECT cookieid,
  23. createtime,
  24. url,
  25. LAST_VALUE(url) OVER(PARTITION BY cookieid) AS last2
  26. FROM lxw1234;
  27. cookieid createtime url last2
  28. ----------------------------------------------
  29. cookie1 2015-04-10 10:00:02 url2 url5
  30. cookie1 2015-04-10 10:00:00 url1 url5
  31. cookie1 2015-04-10 10:03:04 1url3 url5
  32. cookie1 2015-04-10 10:50:05 url6 url5
  33. cookie1 2015-04-10 11:00:00 url7 url5
  34. cookie1 2015-04-10 10:10:00 url4 url5
  35. cookie1 2015-04-10 10:50:01 url5 url5
  36. cookie2 2015-04-10 10:00:02 url22 url55
  37. cookie2 2015-04-10 10:00:00 url11 url55
  38. cookie2 2015-04-10 10:03:04 1url33 url55
  39. cookie2 2015-04-10 10:50:05 url66 url55
  40. cookie2 2015-04-10 11:00:00 url77 url55
  41. cookie2 2015-04-10 10:10:00 url44 url55
  42. 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. cookieid createtime url rn last1 last2
  10. -------------------------------------------------------------
  11. cookie1 2015-04-10 10:00:00 url1 1 url1 url7
  12. cookie1 2015-04-10 10:00:02 url2 2 url2 url7
  13. cookie1 2015-04-10 10:03:04 1url3 3 1url3 url7
  14. cookie1 2015-04-10 10:10:00 url4 4 url4 url7
  15. cookie1 2015-04-10 10:50:01 url5 5 url5 url7
  16. cookie1 2015-04-10 10:50:05 url6 6 url6 url7
  17. cookie1 2015-04-10 11:00:00 url7 7 url7 url7
  18. cookie2 2015-04-10 10:00:00 url11 1 url11 url77
  19. cookie2 2015-04-10 10:00:02 url22 2 url22 url77
  20. cookie2 2015-04-10 10:03:04 1url33 3 1url33 url77
  21. cookie2 2015-04-10 10:10:00 url44 4 url44 url77
  22. cookie2 2015-04-10 10:50:01 url55 5 url55 url77
  23. cookie2 2015-04-10 10:50:05 url66 6 url66 url77
  24. cookie2 2015-04-10 11:00:00 url77 7 url77 url77
hive etl
上一篇: Excel導出大量資料案例，poi導出案例
下一篇: poi之SXSSFWorkbook大量資料導出至excel準備工作：在pom.xml中引入依賴SXSSFWorkbook導出excel示例測試一下:運作測試方法,控制台輸出一下資訊

繼續閱讀