`

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

    博客分类：
  • hive
hivelagleadfirst_valuelast_value 
阅读更多
问题导读

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. 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
复制代码


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

 

 

本文转自：http://www.aboutyun.com/thread-12848-1-1.html
分享到：
| shell报bash: service: command not found ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    hive高级分析函数与优化.rar_hive_hive 函数优化_hive 分析 大数据_hive高级优化_oldwlc

    高级分析函数如统计学习函数（如KMEANS、NAIVE_BAYES等）和时间序列分析函数（如LAG、LEAD跨越时间窗口的计算）则能进行更复杂的分析任务。 2. **Hive函数优化**： 函数优化主要涉及函数重写、谓词下推和计算下推...

    hive窗口函数.docx

    Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口函数的基本语法结构...

    test_hive.rar_ThriftHive.php_hive_hive php_php hive_php hive Thr

    另一个文件“hive_lib”可能包含了与Hive相关的其他辅助函数或者依赖的库。 使用这样的库，开发者可以进行以下操作： 1. **创建Hive连接**：通过ThriftHive.php，开发者可以初始化一个Thrift客户端，连接到运行...

    02.hive内置函数--窗口分析函数--row_number_over.mp4

    02.hive内置函数--窗口分析函数--row_number_over.mp4

    大数据hive中窗口函数的一些常用函数

    窗口函数是Hive中一种强大的分析工具，可以对数据进行复杂的分析和计算。窗口函数可以像聚合函数一样对一组数据进行分析并返回结果，但不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回...

    Hive SQL大厂必考常用窗口函数及面试题.pdf

    【Hive SQL大厂必考常用窗口函数及面试题】主要涵盖了窗⼝函数在大数据分析中的应用，尤其在OLAP（在线分析处理）场景中的重要性。窗⼝函数是一种标准SQL功能，它允许对数据库数据进行实时分析处理，如市场分析、...

    hive客户端安装_hive客户端安装_hive_

    2. 修改Hive配置文件（$HIVE_HOME/conf/hive-site.xml），添加以下内容： ```xml <name>javax.jdo.option.ConnectionURL <value>jdbc:derby:;databaseName=/var/lib/hive/metastore;create=true</value> ...

    Hive窗口函数，定义、demo

    3. **分组函数**：如`LEAD()`, `LAG()`, `FIRST_VALUE()`, `LAST_VALUE()`，它们可以访问窗口内的前一行、后一行或者第一行、最后一行的值。 4. **累积函数**：如`SUM() OVER (ORDER BY column)`, 它可以实现累计...

    hive窗口函数

    窗口函数为Hive用户提供了强大的数据分析能力，特别是在处理时间序列数据和进行复杂的统计分析时。通过对SUM、AVG、MIN、MAX等基础函数的学习和实践，我们可以更加灵活地进行数据探索和挖掘，为业务决策提供有力支持...

    Hive_UDF.rar_hive_sqoop

    总之，“Hive_UDF.rar_hive_sqoop”压缩包提供了关于如何利用 Hive 自定义函数增强数据分析能力，以及如何借助 Sqoop 实现数据在 RDBMS 和 Hadoop 生态系统之间的流动的详细信息。这些工具的结合使用，为大数据处理...

    Hive_JDBC.zip_hive java_hive jdbc_hive jdbc pom_java hive_maven连

    Hive JDBC（Java Database Connectivity）是Hive提供的一种接口，使得其他编程语言，如Java，能够通过JDBC驱动程序与Hive进行交互，实现数据查询、分析和操作。本教程将深入讲解如何使用Hive JDBC在Java项目中建立与...

    Hive用户指南(Hive_user_guide)_中文版

    ### Hive用户指南中文版知识点概览 #### 一、Hive结构 **1.1 Hive架构** Hive作为建立在Hadoop之上的数据仓库基础架构，其主要目标是简化大数据的处理过程。Hive的架构主要包括以下几个核心组件： - **用户接口*...

    Hive开窗函数测试-cube，rollup

    在Hive中，窗口函数包括`row_number()`, `rank()`, `dense_rank()`, `percent_rank()`, `ntile()`, `lag()`, `lead()`等，它们对于排序数据、分组计算以及追踪行之间的关系特别有用。例如，`lag()`可以用来查看当前...

    Hive用户指南(Hive_user_guide)_中文版.pdf

    Hive是基于Hadoop的数据仓库工具，提供了类似关系数据库的查询功能，让用户可以使用SQL语句来查询和分析大规模数据。下面是Hive用户指南中的一些重要知识点： 1. Hive架构 Hive架构主要由以下几个组件组成：...

    Hive用户指南(Hive_user_guide)_中文版pdf

    Hive是由Facebook开发并开源的一种基于Hadoop的数据仓库工具，它允许使用SQL（HQL，Hive Query Language）对大规模数据集进行分析和查询。本指南主要涵盖了Hive的基本概念、安装配置、数据模型、查询语言、表管理和...

    hive_windows可执行文件

    - 提供的"hive_windows可执行文件"是一个适用于Windows系统的Hive版本，包含了运行Hive所需的基本组件。 2. **解压和替换**： - 用户需要将下载的压缩包解压，解压后的文件包含Hive的命令行工具和配置脚本。 - ...

    hive日期函数next_day小计.txt

    hive中使用的日期函数next_day小计，主要用于记录该函数的使用方法以及一些示例，方便其他人查找使用

    hive学习实战-guli_video_orc-guli_video_user_orc-相关资料.7z

    - 学习Hive SQL语法，包括SELECT、JOIN、GROUP BY、WHERE等基本操作，以及窗口函数、分区等高级特性。 - 掌握ORC格式的优点和使用场景，了解如何在Hive中创建、加载和查询ORC表。 - 分析guli_video_orc和guli_video_...

Magicbox
Global site tag (gtag.js) - Google Analytics