Hive分析窗口函数(三) LAG,LEAD,FIRST_VALUE,LAST_VALUE_小江_xiaojiang的博客-程序员宅基地

技术标签： Hive  

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

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

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


版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jiangsanfeng1111/article/details/77097484

智能推荐

DVB标准大全_dvb-rcs标准-程序员宅基地

转载自：https://www.cnblogs.com/allengaller/articles/1656897.htmlDVB标准大全DVB组织关系与文档类别：DVB的所有标准和蓝皮书都是有欧洲的一个法定标准化组织：欧洲电信标准协会（ETSI）颁布。ETSI联合欧洲电工技术标准委员会（CENELEC），欧洲广播联合（EBU）一起建立一个简称JTC的联合技术委员会专门处理..._dvb-rcs标准

GF-3号SAR卫星遥感围填海监测_毛毛真nice的博客-程序员宅基地

高分三号(GF-3)卫星是我国首颗C波段全极化合成孔径雷达(synthenic aperture rader,SAR)成像卫星，已于2016年8月10日在太原卫星发射中心成功发射升空，可以全天时全天候实现海洋和陆地信息的监测，具有重要的研究意义。SAR是主动式微波高分辨率影像雷达，通过脉冲压缩和合成孔径技术分别有效提高距离向和方位向分辨率。SAR卫星遥感影像不受天气条件的影响，可解决可见光遥感影像无法每年完全覆盖海岸线的难题，在自然环境恶劣条件下优势明显。实时科学管理围填海有利于可持续开发利用海岸带资源，

linux内核cdev_init系列函数（字符设备的注册）_ONIM的博客-程序员宅基地

内核中每个字符设备都对应一个 cdev 结构的变量，下面是它的定义：linux-2.6.22/include/linux/cdev.hstruct cdev { struct kobject kobj; // 每个 cdev 都是一个 kobject struct module *owner; // 指向实现驱动的模块 const

免费的图书管图书借阅管理系统_btloveet的博客-程序员宅基地

百宝云图书借阅系统基于百宝云在线表单设计的图书借阅系统，实现了三个基本功能。主要运用到了百宝云表单系统的数据联动和计算公式。这样借书还书只要扫一个条形码，图书资料会自动显示。本套系统免费使用，适合中小型图书借阅机构和简单图书管理。下载使用http://www.baibaoyun.com/一、 图书资料的录入和查询管理，由“图书信息录入”和“地区图书信息”这两个表组成。完整的记录了_图书借阅管理系统

使用Jenkins部署K8s项目_jenkins如何调用k8s_芸灵fly的博客-程序员宅基地

说明Continuous Integration(CI)和持续交付Continuous Delivery(CD)在项目中也算是比较重要的一部分，在我进行项目容器化的过程中，发现不断的自己去手工操作kubectl实在是太麻烦了，也不容易形成记录，在查找了一下资料后，选用了Jenkins进行CI/CD，下面通过一个具体的实例来记录我使用Jenkins的过程，有问题欢迎讨论(￣ェ￣;)。我的K8s是单Master多worker，多Master的话可能有不一样的地方，请酌情查看。快速开始Jenkins的_jenkins如何调用k8s

0-1背包-uva562-Dividing coins_lele_pipi的博客-程序员宅基地

Dividing coins

随便推点

ue4 调用windows库函数报错 error C2872: error C2872: “DWORD”: 不明确的符号_大禾的博客-程序员宅基地

当在虚幻引擎（ue4）中调用 windows api 是 会出现一下类似的错误时：1>C:\Program Files (x86)\Windows Kits\8.1\include\shared\rpcasync.h(114): error C2872: “DWORD”: 不明确的符号1> C:\Program Files (x86)\Windows Kits\8.1\incl...

conda & pip 安装NumPy速度不佳解决方案_pip numpy 很慢_chenz1hao的博客-程序员宅基地

NumPy本身下载包不大，但源地址位于国外，下载速度很慢且不稳定浪费了很多时间，切换到国内镜像源后即可：pip：pip install -i https://pypi.tuna.tsinghua.edu.cn/simple numpyconda:先切换到国内清华大学镜像conda config --add channels https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/free/conda config --add c._pip numpy 很慢

ABP VNext系列(二)-详解ABP的依赖注入_abp vnext 注入方法_小丑dd的博客-程序员宅基地

ABP VNext系列(二)-详解ABP的依赖注入上一篇 : ABP VNext系列(一)-第一个ABP VNext目录ABP VNext系列(二)-详解ABP的依赖注入一、什么是依赖注入二、生命周期管理三、ABP中怎么使用四、总结一、什么是依赖注入1. 二、生命周期管理三、ABP中怎么使用四、总结..._abp vnext 注入方法

I2S音频总线_i2s引脚_406不速之客的博客-程序员宅基地

I2S音频总线学习（一）数字音频技术一、声音的基本概念 声音是通过一定介质传播的连续的波。 图1 声波 重要指标： 振幅：音量的大小 周期：重复出现的时间间隔 频率：指信号每秒钟变化的次数 声音按频率分类： 图2 声音的频率(语音信号频率范围：300Hz-3kHz) 声音的传播携带了信息，它是人类传播信息的一种主要媒体。 声音的三种类型： 波形声音：包含了所有声音形式 语音：不仅是波形声音，而且还有丰富的语言内涵(抽象→提..._i2s引脚

xxl-job系列（一）--------- 搭建调度中心_xxl-job 能自定义控制中心吗_请叫我猿叔叔的博客-程序员宅基地

一、简介 之前用的schedule,发现无法监控任务状态，后来试着了解es-job，发现用起来不方便，没人维护状态，又不集成springboot.果断转战xxl-job. 刚开始看，搭建方便，页面贼骚气。版本好像是2.1.0master分支吧二、搭建步骤2.1 搭建调度中心控制台2.1.1 克隆项目项目git地址为：https://github.com..._xxl-job 能自定义控制中心吗

selenium入门安装及环境搭建_是祀梦啊的博客-程序员宅基地

selenium入门安装及环境搭建

推荐文章

热门文章

相关标签