热门标签 | HotTags
当前位置:  开发笔记 > 运维 > 正文

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

1.LAG功能是什么?2.LEAD与LAG功能有什么相的地方那个?3.FIRST_VALUE与LAST_VALUE分别完成什么功能?继续学习这四个分析函数。注意:这几个函数不支持WINDOW子句。Hive版本为apache-hive-0.13.1数据准备:水电费cookie1,2015-04-1010:00:02,url2

1.LAG功能是什么? 2.LEAD与LAG功能有什么相的地方那个? 3.FIRST_VALUE与LAST_VALUE分别完成什么功能? 继续学习这四个分析函数。注意: 这几个函数不支持WINDOW子句。 Hive版本为 apache-hive-0.13.1 数据准备: 水电费 COOKIE1,2015-04-10 10:00:02,url2

1.LAG功能是什么?
2.LEAD与LAG功能有什么相似的地方那个?

3.FIRST_VALUE与LAST_VALUE分别完成什么功能?


继续学习这四个分析函数。 注意: 这几个函数不支持WINDOW子句。 Hive版本为 apache-hive-0.13.1 数据准备:

水电费

    COOKIE1,2015-04-10 10:00:02,url2
    COOKIE1,2015-04-10 10:00:00,url1
    COOKIE1,2015-04-10 10:03:04,1url3
    COOKIE1,2015-04-10 10:50:05,url6
    COOKIE1,2015-04-10 11:00:00,url7
    COOKIE1,2015-04-10 10:10:00,url4
    COOKIE1,2015-04-10 10:50:01,url5
    COOKIE2,2015-04-10 10:00:02,url22
    COOKIE2,2015-04-10 10:00:00,url11
    COOKIE2,2015-04-10 10:03:04,1url33
    COOKIE2,2015-04-10 10:50:05,url66
    COOKIE2,2015-04-10 11:00:00,url77
    COOKIE2,2015-04-10 10:10:00,url44
    COOKIE2,2015-04-10 10:50:01,url55

    CREATE EXTERNAL TABLE lxw1234 (
    COOKIEid string,
    createtime string,  --页面访问时间
    url STRING       --被访问页面
    ) ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    stored as textfile location '/tmp/lxw11/';


    hive> select * from lxw1234;
    OK
    COOKIE1 2015-04-10 10:00:02     url2
    COOKIE1 2015-04-10 10:00:00     url1
    COOKIE1 2015-04-10 10:03:04     1url3
    COOKIE1 2015-04-10 10:50:05     url6
    COOKIE1 2015-04-10 11:00:00     url7
    COOKIE1 2015-04-10 10:10:00     url4
    COOKIE1 2015-04-10 10:50:01     url5
    COOKIE2 2015-04-10 10:00:02     url22
    COOKIE2 2015-04-10 10:00:00     url11
    COOKIE2 2015-04-10 10:03:04     1url33
    COOKIE2 2015-04-10 10:50:05     url66
    COOKIE2 2015-04-10 11:00:00     url77
    COOKIE2 2015-04-10 10:10:00     url44
    COOKIE2 2015-04-10 10:50:01     url55
LAG

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

    SELECT COOKIEid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY COOKIEid ORDER BY createtime) AS rn,
    LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY COOKIEid ORDER BY createtime) AS last_1_time,
    LAG(createtime,2) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS last_2_time
    FROM lxw1234;


    COOKIEid createtime             url    rn       last_1_time             last_2_time
    -------------------------------------------------------------------------------------------
    COOKIE1 2015-04-10 10:00:00     url1    1       1970-01-01 00:00:00     NULL
    COOKIE1 2015-04-10 10:00:02     url2    2       2015-04-10 10:00:00     NULL
    COOKIE1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:00:02     2015-04-10 10:00:00
    COOKIE1 2015-04-10 10:10:00     url4    4       2015-04-10 10:03:04     2015-04-10 10:00:02
    COOKIE1 2015-04-10 10:50:01     url5    5       2015-04-10 10:10:00     2015-04-10 10:03:04
    COOKIE1 2015-04-10 10:50:05     url6    6       2015-04-10 10:50:01     2015-04-10 10:10:00
    COOKIE1 2015-04-10 11:00:00     url7    7       2015-04-10 10:50:05     2015-04-10 10:50:01
    COOKIE2 2015-04-10 10:00:00     url11   1       1970-01-01 00:00:00     NULL
    COOKIE2 2015-04-10 10:00:02     url22   2       2015-04-10 10:00:00     NULL
    COOKIE2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:00:02     2015-04-10 10:00:00
    COOKIE2 2015-04-10 10:10:00     url44   4       2015-04-10 10:03:04     2015-04-10 10:00:02
    COOKIE2 2015-04-10 10:50:01     url55   5       2015-04-10 10:10:00     2015-04-10 10:03:04
    COOKIE2 2015-04-10 10:50:05     url66   6       2015-04-10 10:50:01     2015-04-10 10:10:00
    COOKIE2 2015-04-10 11:00:00     url77   7       2015-04-10 10:50:05     2015-04-10 10:50:01


    last_1_time: 指定了往上第1行的值,default为'1970-01-01 00:00:00'  
                 COOKIE1第一行,往上1行为NULL,因此取默认值 1970-01-01 00:00:00
                 COOKIE1第三行,往上1行值为第二行值,2015-04-10 10:00:02
                 COOKIE1第六行,往上1行值为第五行值,2015-04-10 10:50:01
    last_2_time: 指定了往上第2行的值,为指定默认值
                                                     COOKIE1第一行,往上2行为NULL
                                                     COOKIE1第二行,往上2行为NULL
                                                     COOKIE1第四行,往上2行为第二行值,2015-04-10 10:00:02
                                                     COOKIE1第七行,往上2行为第五行值,2015-04-10 10:50:01

LEAD

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

    SELECT COOKIEid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY COOKIEid ORDER BY createtime) AS rn,
    LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY COOKIEid ORDER BY createtime) AS next_1_time,
    LEAD(createtime,2) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS next_2_time
    FROM lxw1234;


    COOKIEid createtime             url    rn       next_1_time             next_2_time
    -------------------------------------------------------------------------------------------
    COOKIE1 2015-04-10 10:00:00     url1    1       2015-04-10 10:00:02     2015-04-10 10:03:04
    COOKIE1 2015-04-10 10:00:02     url2    2       2015-04-10 10:03:04     2015-04-10 10:10:00
    COOKIE1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:10:00     2015-04-10 10:50:01
    COOKIE1 2015-04-10 10:10:00     url4    4       2015-04-10 10:50:01     2015-04-10 10:50:05
    COOKIE1 2015-04-10 10:50:01     url5    5       2015-04-10 10:50:05     2015-04-10 11:00:00
    COOKIE1 2015-04-10 10:50:05     url6    6       2015-04-10 11:00:00     NULL
    COOKIE1 2015-04-10 11:00:00     url7    7       1970-01-01 00:00:00     NULL
    COOKIE2 2015-04-10 10:00:00     url11   1       2015-04-10 10:00:02     2015-04-10 10:03:04
    COOKIE2 2015-04-10 10:00:02     url22   2       2015-04-10 10:03:04     2015-04-10 10:10:00
    COOKIE2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:10:00     2015-04-10 10:50:01
    COOKIE2 2015-04-10 10:10:00     url44   4       2015-04-10 10:50:01     2015-04-10 10:50:05
    COOKIE2 2015-04-10 10:50:01     url55   5       2015-04-10 10:50:05     2015-04-10 11:00:00
    COOKIE2 2015-04-10 10:50:05     url66   6       2015-04-10 11:00:00     NULL
    COOKIE2 2015-04-10 11:00:00     url77   7       1970-01-01 00:00:00     NULL

    --逻辑与LAG一样,只不过LAG是往上,LEAD是往下。

FIRST_VALUE

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

    SELECT COOKIEid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY COOKIEid ORDER BY createtime) AS rn,
    FIRST_VALUE(url) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS first1
    FROM lxw1234;

    COOKIEid  createtime            url     rn      first1
    ---------------------------------------------------------
    COOKIE1 2015-04-10 10:00:00     url1    1       url1
    COOKIE1 2015-04-10 10:00:02     url2    2       url1
    COOKIE1 2015-04-10 10:03:04     1url3   3       url1
    COOKIE1 2015-04-10 10:10:00     url4    4       url1
    COOKIE1 2015-04-10 10:50:01     url5    5       url1
    COOKIE1 2015-04-10 10:50:05     url6    6       url1
    COOKIE1 2015-04-10 11:00:00     url7    7       url1
    COOKIE2 2015-04-10 10:00:00     url11   1       url11
    COOKIE2 2015-04-10 10:00:02     url22   2       url11
    COOKIE2 2015-04-10 10:03:04     1url33  3       url11
    COOKIE2 2015-04-10 10:10:00     url44   4       url11
    COOKIE2 2015-04-10 10:50:01     url55   5       url11
    COOKIE2 2015-04-10 10:50:05     url66   6       url11
    COOKIE2 2015-04-10 11:00:00     url77   7       url11

LAST_VALUE

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


    SELECT COOKIEid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY COOKIEid ORDER BY createtime) AS rn,
    LAST_VALUE(url) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS last1
    FROM lxw1234;


    COOKIEid  createtime            url    rn       last1  
    -----------------------------------------------------------------
    COOKIE1 2015-04-10 10:00:00     url1    1       url1
    COOKIE1 2015-04-10 10:00:02     url2    2       url2
    COOKIE1 2015-04-10 10:03:04     1url3   3       1url3
    COOKIE1 2015-04-10 10:10:00     url4    4       url4
    COOKIE1 2015-04-10 10:50:01     url5    5       url5
    COOKIE1 2015-04-10 10:50:05     url6    6       url6
    COOKIE1 2015-04-10 11:00:00     url7    7       url7
    COOKIE2 2015-04-10 10:00:00     url11   1       url11
    COOKIE2 2015-04-10 10:00:02     url22   2       url22
    COOKIE2 2015-04-10 10:03:04     1url33  3       1url33
    COOKIE2 2015-04-10 10:10:00     url44   4       url44
    COOKIE2 2015-04-10 10:50:01     url55   5       url55
    COOKIE2 2015-04-10 10:50:05     url66   6       url66
    COOKIE2 2015-04-10 11:00:00     url77   7       url77

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

    SELECT COOKIEid,
    createtime,
    url,
    FIRST_VALUE(url) OVER(PARTITION BY COOKIEid) AS first2  
    FROM lxw1234;

    COOKIEid  createtime            url     first2
    ----------------------------------------------
    COOKIE1 2015-04-10 10:00:02     url2    url2
    COOKIE1 2015-04-10 10:00:00     url1    url2
    COOKIE1 2015-04-10 10:03:04     1url3   url2
    COOKIE1 2015-04-10 10:50:05     url6    url2
    COOKIE1 2015-04-10 11:00:00     url7    url2
    COOKIE1 2015-04-10 10:10:00     url4    url2
    COOKIE1 2015-04-10 10:50:01     url5    url2
    COOKIE2 2015-04-10 10:00:02     url22   url22
    COOKIE2 2015-04-10 10:00:00     url11   url22
    COOKIE2 2015-04-10 10:03:04     1url33  url22
    COOKIE2 2015-04-10 10:50:05     url66   url22
    COOKIE2 2015-04-10 11:00:00     url77   url22
    COOKIE2 2015-04-10 10:10:00     url44   url22
    COOKIE2 2015-04-10 10:50:01     url55   url22

    SELECT COOKIEid,
    createtime,
    url,
    LAST_VALUE(url) OVER(PARTITION BY COOKIEid) AS last2  
    FROM lxw1234;

    COOKIEid  createtime            url     last2
    ----------------------------------------------
    COOKIE1 2015-04-10 10:00:02     url2    url5
    COOKIE1 2015-04-10 10:00:00     url1    url5
    COOKIE1 2015-04-10 10:03:04     1url3   url5
    COOKIE1 2015-04-10 10:50:05     url6    url5
    COOKIE1 2015-04-10 11:00:00     url7    url5
    COOKIE1 2015-04-10 10:10:00     url4    url5
    COOKIE1 2015-04-10 10:50:01     url5    url5
    COOKIE2 2015-04-10 10:00:02     url22   url55
    COOKIE2 2015-04-10 10:00:00     url11   url55
    COOKIE2 2015-04-10 10:03:04     1url33  url55
    COOKIE2 2015-04-10 10:50:05     url66   url55
    COOKIE2 2015-04-10 11:00:00     url77   url55
    COOKIE2 2015-04-10 10:10:00     url44   url55
    COOKIE2 2015-04-10 10:50:01     url55   url55

如果想要取分组内排序后最后一个值,则需要变通一下:

    SELECT COOKIEid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY COOKIEid ORDER BY createtime) AS rn,
    LAST_VALUE(url) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS last1,
    FIRST_VALUE(url) OVER(PARTITION BY COOKIEid ORDER BY createtime DESC) AS last2
    FROM lxw1234
    ORDER BY COOKIEid,createtime;

    COOKIEid  createtime            url     rn     last1    last2
    -------------------------------------------------------------
    COOKIE1 2015-04-10 10:00:00     url1    1       url1    url7
    COOKIE1 2015-04-10 10:00:02     url2    2       url2    url7
    COOKIE1 2015-04-10 10:03:04     1url3   3       1url3   url7
    COOKIE1 2015-04-10 10:10:00     url4    4       url4    url7
    COOKIE1 2015-04-10 10:50:01     url5    5       url5    url7
    COOKIE1 2015-04-10 10:50:05     url6    6       url6    url7
    COOKIE1 2015-04-10 11:00:00     url7    7       url7    url7
    COOKIE2 2015-04-10 10:00:00     url11   1       url11   url77
    COOKIE2 2015-04-10 10:00:02     url22   2       url22   url77
    COOKIE2 2015-04-10 10:03:04     1url33  3       1url33  url77
    COOKIE2 2015-04-10 10:10:00     url44   4       url44   url77
    COOKIE2 2015-04-10 10:50:01     url55   5       url55   url77
    COOKIE2 2015-04-10 10:50:05     url66   6       url66   url77
    COOKIE2 2015-04-10 11:00:00     url77   7       url77   url77




推荐阅读
  • 解决JAX-WS动态客户端工厂弃用问题并迁移到XFire
    在处理Java项目中的JAR包冲突时,我们遇到了JaxWsDynamicClientFactory被弃用的问题,并成功将其迁移到org.codehaus.xfire.client。本文详细介绍了这一过程及解决方案。 ... [详细]
  • 本文详细分析了Hive在启动过程中遇到的权限拒绝错误,并提供了多种解决方案,包括调整文件权限、用户组设置以及环境变量配置等。 ... [详细]
  • XNA 3.0 游戏编程:从 XML 文件加载数据
    本文介绍如何在 XNA 3.0 游戏项目中从 XML 文件加载数据。我们将探讨如何将 XML 数据序列化为二进制文件,并通过内容管道加载到游戏中。此外,还会涉及自定义类型读取器和写入器的实现。 ... [详细]
  • 本文详细介绍了 Apache Jena 库中的 Txn.executeWrite 方法,通过多个实际代码示例展示了其在不同场景下的应用,帮助开发者更好地理解和使用该方法。 ... [详细]
  • PHP 5.5.0rc1 发布:深入解析 Zend OPcache
    2013年5月9日,PHP官方发布了PHP 5.5.0rc1和PHP 5.4.15正式版,这两个版本均支持64位环境。本文将详细介绍Zend OPcache的功能及其在Windows环境下的配置与测试。 ... [详细]
  • 本文详细介绍了 Java 中 org.apache.xmlbeans.SchemaType 类的 getBaseEnumType() 方法,提供了多个代码示例,并解释了其在不同场景下的使用方法。 ... [详细]
  • 本文介绍如何在Java项目中使用Log4j库进行日志记录。我们将详细说明Log4j库的引入、配置及简单应用,帮助开发者快速上手。 ... [详细]
  • 本文详细介绍了如何在ECharts中使用线性渐变色,通过echarts.graphic.LinearGradient方法实现。文章不仅提供了完整的代码示例,还解释了各个参数的具体含义及其应用场景。 ... [详细]
  • 本文详细介绍了如何解决MyBatis中常见的BindingException错误,提供了多种排查和修复方法,确保Mapper接口与XML文件的正确配置。 ... [详细]
  • 本文详细介绍了Linux系统中init进程的作用及其启动过程,解释了运行级别的概念,并提供了调整服务启动顺序的具体步骤和实例。通过了解这些内容,用户可以更好地管理系统的启动流程和服务配置。 ... [详细]
  • 深入解析 Apache Shiro 安全框架架构
    本文详细介绍了 Apache Shiro,一个强大且灵活的开源安全框架。Shiro 专注于简化身份验证、授权、会话管理和加密等复杂的安全操作,使开发者能够更轻松地保护应用程序。其核心目标是提供易于使用和理解的API,同时确保高度的安全性和灵活性。 ... [详细]
  • 探讨了小型企业在构建安全网络和软件时所面临的挑战和机遇。本文介绍了如何通过合理的方法和工具,确保小型企业能够有效提升其软件的安全性,从而保护客户数据并增强市场竞争力。 ... [详细]
  • 本文详细介绍了如何准备和安装 Eclipse 开发环境及其相关插件,包括 JDK、Tomcat、Struts 等组件的安装步骤及配置方法。 ... [详细]
  • HBase运维工具全解析
    本文深入探讨了HBase常用的运维工具,详细介绍了每种工具的功能、使用场景及操作示例。对于HBase的开发人员和运维工程师来说,这些工具是日常管理和故障排查的重要手段。 ... [详细]
  • 在本周的白板演练中,Apache Flink 的 PMC 成员及数据工匠首席技术官 Stephan Ewen 深入探讨了如何利用保存点功能进行流处理中的数据重新处理、错误修复、系统升级和 A/B 测试。本文将详细解释保存点的工作原理及其应用场景。 ... [详细]
author-avatar
Happy_Kelly尊荣
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有