[Hive]窗口函数LEAD LAG FIRST_VALUE LAST_VALUE

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/SunnyYoona/article/details/56484919

窗口函数(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返回的是窗口最后一行,即最新一天的浏览次数。

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页