[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_VALUE | FIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。 |
| LAST_VALUE | LAST_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返回的是窗口最后一行，即最新一天的浏览次数。
