窗口分析函数lead，lag，first_value, last_value

最新推荐文章于 2022-09-09 10:44:51 发布
最新推荐文章于 2022-09-09 10:44:51 发布
阅读量112 收藏 1
点赞数
分类专栏： SQL 文章标签： sql mysql
原文链接：http://lxw1234.com/archives/2015/04/190.htm#comments
版权

本文转载自：lxw的大数据田地 » Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

学习这四个分析函数。

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

weixin_46762511
关注
专栏目录
Hive 窗口函数LEAD LAG FIRST_VALUE LAST_VALUE
SmartSi
02-22 8611
窗口函数（window functions）对多行进行操作，并为查询中的每一行返回一个值。 OVER()子句能将窗口函数与其他分析函数（analytical functions）和报告函数（reporting functions）区分开来。1. 常用窗口函数下表列出了一些窗口函数以及描述信息：窗口函数描述LAG()LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回
窗口函数之LEAD,LAG, FIRST_VALUE, LAST_VALUE
蕾欧娜与26的博客
09-20 1241
这篇博客给大家介绍 Hive 中最常用的四个窗口函数，分别是：LEAD, LAG, FIRST_VALUE 以及 LAST_VALUE。 FIRST_VALUE：取分组内排序后，截止到当前行，第一个值。 LAST_VALUE：取分组内排序后，截止到当前行，最后一个值。 LEAD (col, n, default)：用于统计窗口内往下滴 n 行值。第一个参数为列名，第二个参数为往下滴 n 行（默认为 1 ），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL） LAG (col
Hive -- 窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
认真学代码的shell
09-28 206
转载自：http://lxw1234.com/archives/2015/04/190.htm 注意： 这几个函数不支持WINDOW子句。（什么是WINDOW子句，点此查看前面的文章 ） Hive版本为 apache-hive-0.13.1 0 数据准备： 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
Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
阿尼古
04-16 599
问题导读1.LAG功能是什么？2.LEAD与LAG功能有什么相似的地方那个？3.FIRST_VALUE与LAST_VALUE分别完成什么功能？ 继续学习这四个分析函数（LAG,LEAD,FIRST_VALUE,LAST_VALUE）。注意： 这几个函数不支持WINDOW子句。 Hive版本为 apache-hive-0.13.1 数据准备： cookie1,2019-04-1...
分析函数--FIRST_VALUE，LAST_VALUE，LAG，LEAD，ROW_NUMBER
yjyiuje的博客
10-25 900
一 ，FIRST_VALUE,LAST_VALUER的使用 1，FIRST_VALUE 是指返回组中数据窗口的第一个值 2，LAST_VALUE 是指返回组中数据窗口的最后一个值（注意点：不受排序的影响） 例子： SELECT DEPARTMENT_ID, FIRST_NAME || ' ' || LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALAR
窗口分析函数LAST_VALUE，FIRST_VALUE，lag，lead
wdyliuxingfeiyang的博客
12-30 972
with abc as ( select 1 as id union all select 2 union all select 3 union all select 4 ) select id, FIRST_VALUE(id) over(order by id ) as firstid, LAST_VALUE(id) over(order by id) as lastid from abc FIRST_VALUE 一看就明白了。但last_value 为什么就是当前行的值呢？明明该是4才对啊.
Hive分析窗口函数: LAG、LEAD、FIRST_VALUE、LAST_VALUE
菜如张学清的博客
07-17 2万+
目录 1.数据准备 1.1数据格式 1.2创建表 2.LAG 2.1函数说明 2.2函数使用 2.3 查询结果 2.4 结果说明 3. LEAD 3.1 函数说明 3.2 函数使用 3.3 查询结果 4.FIRST_VALUE 4.1 函数说明 4.2 函数使用 5.LAST_VALUE 5.1 函数说明 5.2 函数使用 5.3 查询结果 5.4 取...
小姐姐在工作中窗口函数LEAD LAG FIRST_VALUE LAST_VALUE用的那么溜
zwmonk的博客
08-01 529
小姐姐，工作中窗口用的那么溜，毕竟我也是一个爱好学习的人，怎么也得研究一把，说干咱就干 准备数据 1、 创建库：create database if not exists bicoredata; 2、 查看库：show databases; 3、 切换数据库：use bicoredata; 4、 建表 CREATE EXTERNAL TABLE IF NOT EXISTS bicoredata.dwd_user_page_view_dm( user_id VARCHAR(32) COMMENT ‘用户ID
Hive分析窗口函数(四) LAG、LEAD、FIRST_VALUE和LAST_VALUE
weixin_42177380的博客
06-04 688
数据 cookie4.txt 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 ...
Hive--开窗函数--窗口位置函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE
qq_46893497的博客
11-22 1万+
窗口函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE 总结 FIRST_VALUE 功能：取每个分区内某列的第一个值 语法：FIRST_VALUE(col) over (partition by col1 order by col2) LAST_VALUE 功能：取每个分区内某列的最后一个值 语法：LAST_VALUE(col) over (partition by col1 order by col2) 注意：默认窗口是从第一条到当前条 LAG 功能：取每个分区内某列的
开窗函数-first_value/last_value
yuhui666666的博客
09-09 6750
开窗函数用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。可以看到使用last_value函数求每个人最后一个日期，结果并不是想要的。last_value()默认是升序，如果限制了是降序，则等同于first_value()升序。开窗函数的功能本质是聚合，但是相比聚合，开窗函数可以提供的信息更多。3、first_value/last_value函数。1、开窗函数是什么？
Hive分析窗口函数(三) LAG,LEAD,FIRST_VALUE,LAST_VALUE
XW-5的博客
02-26 956
LAG,LEAD,FIRST_VALUE,LAST_VALUE  注意： 这几个函数不支持WINDOW子句 数据准备： CREATE EXTERNAL TABLE lxw1234 ( cookieid string, createtime string,  --页面访问时间 url STRING       --被访问页面 ) ROW FORMAT DELIMITED  FIELDS TE...
Oracle 高级查询之二 分析函数部分--first_value,last_value
congjiong5980的博客
01-21 708
ORACLE函数：LAST_VALUE,FIRST_VALUE的用法： 1、初始化原始数据：create table test (id number(2), name varchar2(10), salary number(6...
first_value和last_value
weixin_48109576的博客
09-07 3185
SQL中分析函数first_value(),last_value，sum() over(partition by…)详解 首先，生成有一张原始表score，s_id表示学生id，c_id表示课程id，s_core表示最后课程的成绩，表结构数据如下所示： 首先，按照课程c_id进行分组，按照成绩s_core进行排序 select * ,row_number() over (partition by c_id order by s_score)rank_showfrom score 1 运行结果：
SQL LAST_VALUE使用问题记录
apollokk的专栏
04-07 9730
自SQL Server2012版本开始提供的分析函数LAST_VALUE，用于返回序列中最后一个值。参数：LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause ) 在使用时发现取的值不是分组中最后一个的值，因为默认是取到分组（可选）第一项到当...
oracle分析函数 first,oracle 分析函數 FIRST_VALUE、LAST_VALUE
weixin_34649105的博客
04-12 345
用SCOTT/TIGER登錄。FIRST_VALUE、LAST_VALUE是兩個分析函數。返回結果集中排在第一位和最后一位的值。使用FIRST_VALUE：SELECT DEPTNO, JOB, SUM(SAL), FIRST_VALUE(SUM(SAL)) OVER (PARTITION BY DEPTNO ORDER BY SUM(SAL))FROM EMP GROUP BY DEPTNO, ...
Oracle分析函数-first_value()和last_value()
极客神殿
09-24 9172
first_value()和last_value()字面意思已经很直观了，取首尾记录值。 例:查询部门最早发生销售记录日期和最近发生的销售记录日期 复制代码 select dept_id ,sale_date ,goods_type ,sale_cnt ,first_value(sale_date) over (partition by dept_id order by sale_date) fi...
数仓工具—Hive语法之窗口函数first_value和last_value(12)
热门推荐
01-06 6万+
1. first_value和last_value 见名知意,就是计算当前窗口截止当前行的第一个值和最后一个值(或者是排名的第一名个最后一名) 2. 其实我们可以理解为取当前窗口截止当前行的最值(最大值和最小值) 3. 这两个窗口函数其实用的不多，也没什么常见的场景
Hive 窗口函数lead、lag
02-12 3819
Hive的分析函数又叫窗口函数，在oracle中就有这样的分析函数，主要用来做数据统计分析的。 Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。 这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率，其中over()表示当前查询的结果集对象，括号里面的语句则表示对这个结果集进行处理。 函数介绍 LAG LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 参数1为列名，参数2为往上第n行（可选，默认为1），参数3
Oracle分析函数详解：从rollup到last_value
6. `FIRST_VALUE()`, `LAST_VALUE()` 取基数的分析函数 - `FIRST_VALUE()` 返回分组中指定列的第一个值，无论排序如何。 - `LAST_VALUE()` 返回分组中指定列的最后一个值，这在处理时间序列数据时特别有用。 示例...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值