Hive窗口函数04-LAG、LEAD、FIRST_VALUE、LAST_VALUE

Hive窗口函数LAG、LEAD、FIRST_VALUE、LAST_VALUE入门

1. 数据说明

现有 hive 表 cookie4, 内容如下:

hive> select * from cookie4;

cookie4.cookieid    cookie4.createtime          cookie4.url
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
  • 其中字段意义: cookieid(string), createtime(string), url(int)
  • 分别代表: cookieid, 创建时间, 访问的url

2. lag()操作

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

--(1)
hive> SELECT cookieid, createtime, url,
    > LAG(createtime, 2) OVER (PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
    > FROM cookie4;
    
结果:因为没有设置默认值,当没有上两行时显示为NULL
cookieid    createtime          url     last_2_time
cookie1     2015-04-10 10:00:00 url1    NULL 
cookie1     2015-04-10 10:00:02 url2    NULL
cookie1     2015-04-10 10:03:04 1url3   2015-04-10 10:00:00
cookie1     2015-04-10 10:10:00 url4    2015-04-10 10:00:02
cookie1     2015-04-10 10:50:01 url5    2015-04-10 10:03:04
cookie1     2015-04-10 10:50:05 url6    2015-04-10 10:10:00
cookie1     2015-04-10 11:00:00 url7    2015-04-10 10:50:01
cookie2     2015-04-10 10:00:00 url11   NULL
cookie2     2015-04-10 10:00:02 url22   NULL
cookie2     2015-04-10 10:03:04 1url33  2015-04-10 10:00:00
cookie2     2015-04-10 10:10:00 url44   2015-04-10 10:00:02
cookie2     2015-04-10 10:50:01 url55   2015-04-10 10:03:04
cookie2     2015-04-10 10:50:05 url66   2015-04-10 10:10:00
cookie2     2015-04-10 11:00:00 url77   2015-04-10 10:50:01

--(2)
hive> SELECT cookieid, createtime, url,
    > LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time 
    > FROM cookie4;
    
结果:
cookieid    createtime          url     last_1_time
cookie1     2015-04-10 10:00:00 url1    1970-01-01 00:00:00 (显示默认值)
cookie1     2015-04-10 10:00:02 url2    2015-04-10 10:00:00
cookie1     2015-04-10 10:03:04 1url3   2015-04-10 10:00:02
cookie1     2015-04-10 10:10:00 url4    2015-04-10 10:03:04
cookie1     2015-04-10 10:50:01 url5    2015-04-10 10:10:00
cookie1     2015-04-10 10:50:05 url6    2015-04-10 10:50:01
cookie1     2015-04-10 11:00:00 url7    2015-04-10 10:50:05
cookie2     2015-04-10 10:00:00 url11   1970-01-01 00:00:00 (显示默认值)
cookie2     2015-04-10 10:00:02 url22   2015-04-10 10:00:00
cookie2     2015-04-10 10:03:04 1url33  2015-04-10 10:00:02
cookie2     2015-04-10 10:10:00 url44   2015-04-10 10:03:04
cookie2     2015-04-10 10:50:01 url55   2015-04-10 10:10:00
cookie2     2015-04-10 10:50:05 url66   2015-04-10 10:50:01
cookie2     2015-04-10 11:00:00 url77   2015-04-10 10:50:05

3. lead()操作

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

hive> SELECT cookieid, createtime, url,
    > LEAD(createtime, 2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time,
    > LEAD(createtime, 1, '1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time
    > FROM cookie4;
    
结果:
cookieid    createtime          url     next_2_time         next_1_time
cookie1     2015-04-10 10:00:00 url1    2015-04-10 10:03:04 2015-04-10 10:00:02
cookie1     2015-04-10 10:00:02 url2    2015-04-10 10:10:00 2015-04-10 10:03:04
cookie1     2015-04-10 10:03:04 1url3   2015-04-10 10:50:01 2015-04-10 10:10:00
cookie1     2015-04-10 10:10:00 url4    2015-04-10 10:50:05 2015-04-10 10:50:01
cookie1     2015-04-10 10:50:01 url5    2015-04-10 11:00:00 2015-04-10 10:50:05
cookie1     2015-04-10 10:50:05 url6    NULL                2015-04-10 11:00:00
cookie1     2015-04-10 11:00:00 url7    NULL                1970-01-01 00:00:00
cookie2     2015-04-10 10:00:00 url11   2015-04-10 10:03:04 2015-04-10 10:00:02
cookie2     2015-04-10 10:00:02 url22   2015-04-10 10:10:00 2015-04-10 10:03:04
cookie2     2015-04-10 10:03:04 1url33  2015-04-10 10:50:01 2015-04-10 10:10:00
cookie2     2015-04-10 10:10:00 url44   2015-04-10 10:50:05 2015-04-10 10:50:01
cookie2     2015-04-10 10:50:01 url55   2015-04-10 11:00:00 2015-04-10 10:50:05
cookie2     2015-04-10 10:50:05 url66   NULL                2015-04-10 11:00:00
cookie2     2015-04-10 11:00:00 url77   NULL                1970-01-01 00:00:00

4.FIRST_VALUE()操作

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

hive> SELECT cookieid, createtime, url,
    > FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first 
    > FROM cookie4;
    
结果:
cookieid    createtime          url     first
cookie1     2015-04-10 10:00:00 url1    url1
cookie1     2015-04-10 10:00:02 url2    url1
cookie1     2015-04-10 10:03:04 1url3   url1
cookie1     2015-04-10 10:10:00 url4    url1
cookie1     2015-04-10 10:50:01 url5    url1
cookie1     2015-04-10 10:50:05 url6    url1
cookie1     2015-04-10 11:00:00 url7    url1
cookie2     2015-04-10 10:00:00 url11   url11
cookie2     2015-04-10 10:00:02 url22   url11
cookie2     2015-04-10 10:03:04 1url33  url11
cookie2     2015-04-10 10:10:00 url44   url11
cookie2     2015-04-10 10:50:01 url55   url11
cookie2     2015-04-10 10:50:05 url66   url11
cookie2     2015-04-10 11:00:00 url77   url11

5.LAST_VALUE()操作

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

hive> SELECT cookieid, createtime, url,
    > LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last
    > FROM cookie4;
    
结果:注意,是截止到当前行的最后一个值,其实就是它本身
cookieid    createtime          url     last
cookie1     2015-04-10 10:00:00 url1    url1
cookie1     2015-04-10 10:00:02 url2    url2
cookie1     2015-04-10 10:03:04 1url3   1url3
cookie1     2015-04-10 10:10:00 url4    url4
cookie1     2015-04-10 10:50:01 url5    url5
cookie1     2015-04-10 10:50:05 url6    url6
cookie1     2015-04-10 11:00:00 url7    url7
cookie2     2015-04-10 10:00:00 url11   url11
cookie2     2015-04-10 10:00:02 url22   url22
cookie2     2015-04-10 10:03:04 1url33  1url33
cookie2     2015-04-10 10:10:00 url44   url44
cookie2     2015-04-10 10:50:01 url55   url55
cookie2     2015-04-10 10:50:05 url66   url66
cookie2     2015-04-10 11:00:00 url77   url77

参考文章:Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 测试常用表BIG_TABLE

创建测试用表,DBA经常用到,通常都是基于dba_objects来创建的比较多。本文根据Tom大师的big_table进行了整理,供大家参考。

16820
来自专栏Linyb极客之路

深入理解MySQL索引原理和实现——为什么索引可以加速查询?

说到索引,很多人都知道“索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询的速度,这...

19530
来自专栏Spark学习技巧

重磅:关于hive的join使用必须了解的事情

Hive支持连接表的以下语法: ? 本文主要讲hive的join 编写连接查询时要考虑的一些要点如下,不同版本支持的情况可能会有些许不同: 1,可以编写复杂的...

825110
来自专栏文渊之博

优化SQLServer——表和分区索引(二)

简介     之前一篇简单的介绍了语法和一些基本的概念,隔了一段时间,觉得有必要细致的通过实例来总结一下这部分内容。如之前所说,分区就是讲大型的对象(表)分成更...

23560
来自专栏数据库

数据库的完整性

导读: 本文深入的介绍了数据库的完整性. O、 数据库的完整性(总体概述) 一、实体完整性 二、参照完整性 三、用户定义完整性 O、数据库的完整性 数据的正确性...

22990
来自专栏互联网开发者交流社区

数据定义: CREATE、DROP、ALTER

15220
来自专栏撸码那些事

MySQL——索引实现原理

在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

9520
来自专栏lgp20151222

技术无关 自己写的项目 一些数据库的想法

+----------+--------------+------+-----+---------+----------------+ | Field    |...

8730
来自专栏极客慕白的成长之路

SQL Sever基本知识

create table tablename(col_name1 type,col_name2 type,...) 在数据库school下创建一个student...

5920
来自专栏我的博客

Python操作MySQL数据库

数据库python-mysql安装参考:http://blog.phpfs.com/archives/2343.html # -*- coding: UTF-8...

36990

扫码关注云+社区

领取腾讯云代金券