[Hive]窗口函数LEAD LAG FIRST_VALUE LAST_VALUE

标签: Hive 窗口函数 LEAD LAG FIRST_VALUE
1211人阅读 评论(0) 收藏 举报
分类:

窗口函数(window functions)对多行进行操作,并为查询中的每一行返回一个值。 OVER()子句能将窗口函数与其他分析函数(analytical functions)和报告函数(reporting functions)区分开来。

1. 常用窗口函数

下表列出了一些窗口函数以及描述信息:

窗口函数描述
LAG()LAG()窗口函数返回分区中当前行之前行(可以指定第几行)的值。 如果没有行,则返回null。
LEAD()LEAD()窗口函数返回分区中当前行后面行(可以指定第几行)的值。 如果没有行,则返回null。
FIRST_VALUEFIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。
LAST_VALUELAST_VALUE窗口函数返回相对于窗口中最后一行的指定列的值。

2. 语法

LAG 和 LEAD 语法

LAG | LEAD
( <col>, <line_num>, <DEFAULT> )
OVER ( [ PARTITION BY ] [ ORDER BY ] )

FIRST_VALUE | LAST_VALUE 语法

FIRST_VALUE | LAST_VALUE
( <col>,<ignore nulls as boolean> ) OVER
( [ PARTITION BY ] [ ORDER BY ][ window_clause ] )

3. Example

原数据:

hive> select * from tmp_pv;
OK
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2
Time taken: 0.102 seconds, Fetched: 14 row(s)

3.1 LAG()

LAG(col,n,DEFAULT)窗口函数返回分区中当前行之前第n行对应列的值。 如果没有相应的行,则返回NULL。 第一个参数为列名,第二个参数为当前行之前第n行(可选,默认为1),第三个参数为缺失时默认值(当前行之前第n行为NULL没有时,返回该默认值,如不指定,则为NULL)。

为了比较每个用户浏览次数与前一天的浏览次数进行比较,查询返回当前浏览次数以及前一天的浏览数量。由于在2017-02-10之前没有浏览行为,前一天的浏览次数设置为0(不设置默认为NULL)。

hive> select gid, dt, pv, lag(pv, 1, 0) over (partition by gid order by dt) as pre_pv from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	0
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	7
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	4
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	0
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	9
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	10
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	8
Time taken: 11.783 seconds, Fetched: 14 row(s)

3.2 LEAD()

LEAD(col,n,DEFAULT)窗口函数返回分区中当前行后面第n行对应列的值。 如果没有相应的行,则返回NULL。 第一个参数为列名,第二个参数为当前行后面第n行(可选,默认为1),第三个参数为缺失时默认值(当前行后面第n行为没有时,返回该默认值,如不指定,则为NULL)。

为了比较每个用户浏览次数与后一天的浏览次数进行比较,查询返回当前浏览次数以及后一天的浏览数量。由于在2017-02-16之后没有浏览行为,后一天的浏览次数显示为NULL(默认为NULL)

hive> select gid, dt, pv, lead(pv, 1) over (partition by gid order by dt) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	7
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	NULL
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	9
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	3
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	10
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	8
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	NULL
Time taken: 9.509 seconds, Fetched: 14 row(s)

3.3 FIRST_VALUE()

为了比较每个用户浏览次数与第一天浏览次数进行比较,查询返回当前浏览次数以及第一天浏览次数。第一个用户第一天浏览次数为1,第二个用户第一天浏览次数为2。

hive> select gid, dt, pv, first_value(pv) over (partition by gid order by dt) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	1
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	2
Time taken: 9.862 seconds, Fetched: 14 row(s)

备注

上面例子窗口为第一行到当前行(缺失window子句有order by ,默认为rows between unbounded preceding and current row)。所以,first_value返回窗口的第一行,即第一天浏览次数。

4.4 LAST_VALUE()

为了比较每个用户浏览次数与最新一天浏览次数进行比较,查询返回当前浏览次数以及最新一天浏览次数。第一个用户最新一天(2017-02-16)浏览次数为4,第二个用户最新一天(2017-02-16)浏览次数为2。

hive> select gid, dt, pv, last_value(pv) over (partition by gid order by dt rows between current row and unbounded following) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-10	1	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-11	5	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-12	7	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-13	3	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-14	2	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-15	4	4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4	2017-02-16	4	4
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-10	2	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-11	9	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-12	3	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-13	10	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-14	1	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-15	8	2
993BD7AD-3B62-BA0C-15AE-A14B85921889	2017-02-16	2	2
Time taken: 9.335 seconds, Fetched: 14 row(s)

备注

上面例子的窗口为当前行到最后一行(rows between current row and unbounded following)。last_value返回的是窗口最后一行,即最新一天的浏览次数。

查看评论

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

环境信息: Hive版本为apache-hive-0.14.0-bin Hadoop版本为hadoop-2.6.0 Tez版本为tez-0.7.0 创建表: create tablewindows...
  • jiangshouzhuang
  • jiangshouzhuang
  • 2016-04-04 18:02:37
  • 5495

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

1.LAG功能是什么? 2.LEAD与LAG功能有什么相似的地方那个? 3.FIRST_VALUE与LAST_VALUE分别完成什么功能? 继续学习这四个分析函数。 注意: 这...
  • vipyeshuai
  • vipyeshuai
  • 2016-05-10 15:23:48
  • 2037

hive OVER(PARTITION BY)函数用法

OVER(PARTITION BY)函数用法  2010年10月26日 OVER(PARTITION BY)函数介绍 开窗函数                Oracle从8.1....
  • sherri_du
  • sherri_du
  • 2016-11-23 21:19:50
  • 16048

Oracle返回指定列首行或末行值之FIRST_VALUE与LAST_VALUE

FIRST_VALUE与LAST_VALUE功能:返回指定列首行值和末行值 在语法上有9i和10g和区别: 9i: FIRST_VALUE ( expr ) OVER ( analytic_...
  • thinkscape
  • thinkscape
  • 2012-11-27 11:25:07
  • 4627

oracle 分析函数 FIRST_VALUE、LAST_VALUE

用SCOTT/TIGER登录。 FIRST_VALUE、LAST_VALUE是两个分析函数。返回结果集中排在第一位和最后一位的值。 使用FIRST_VALUE: SELECT DEPTNO,...
  • feier7501
  • feier7501
  • 2014-03-20 21:41:42
  • 1481

lag和lead 分析函数

oracle 的分析函数是非常好的一个功能,借助它们,我们可以很方便的实现一些特殊的语句需求,省去了自己实现的诸多麻烦。  今天用到了lag 和lead 这两个分析函数,稍稍整理一下。 ...
  • mazongqiang
  • mazongqiang
  • 2012-05-31 20:37:07
  • 25271

[Hive]窗口函数与分析函数

本文介绍了用于窗口函数和分析函数的Hive QL增强功能。所有窗口和分析函数操作都按照SQL标准。 当前版本支持以下窗口函数和分析函数:1 窗口函数LEAD 返回分区中当前行后面行(可以指定第几行)的...
  • SunnyYoona
  • SunnyYoona
  • 2017-02-22 14:24:52
  • 1428

【分析函数】使用分析函数LAST_VALUE或11g LAG实现缺失数据填充及其区别

转载自:http://blog.chinaunix.net/uid-7655508-id-3736949.html  在“使用Partitioned Outer Join实现稠化报表”这篇文章中...
  • l2tp1012
  • l2tp1012
  • 2014-06-13 20:24:58
  • 1611

hive--lag和lead 分析函数

http://blog.csdn.net/mazongqiang/article/details/7621330 oracle 的分析函数是非常好的一个功能,借助它们,我们可以很方便的实现一...
  • qq_34941023
  • qq_34941023
  • 2016-09-19 22:40:33
  • 3824

[Hive]窗口函数LEAD LAG FIRST_VALUE LAST_VALUE

窗口函数(window functions)对多行进行操作,并为查询中的每一行返回一个值。 OVER()子句能将窗口函数与其他分析函数(analytical functions)和报告函数(repor...
  • SunnyYoona
  • SunnyYoona
  • 2017-02-22 14:28:58
  • 1211
    个人资料
    专栏达人 持之以恒
    等级:
    访问量: 159万+
    积分: 2万+
    排名: 370
    博客专栏
    最新评论