Hive 的窗口函数
在 SQL 中有一类函数叫做聚合函数，例如 sum()、avg()、max()、min() 等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是，有时候我们既要显示聚集前的数据，又要显示聚集后的数据，此时我们便引入了窗口函数。窗口函数主要用于 OLAP 数据分析。
在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by子句之前。
| 窗口函数 | 描述 |
|---|---|
| LAG() | LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回null。 |
| LEAD() | LEAD()窗口函数返回分区中当前行后面行（可以指定第几行）的值。 如果没有行，则返回null。 |
| FIRST_VALUE | FIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。 |
| LAST_VALUE | LAST_VALUE窗口函数返回相对于窗口中最后一行的指定列的值。 |
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 ] )
下面举个例子，数据集如下：
hive> select * from tmp_pv;
OK
0006D2BC-4DF9-4C0B-83AD-0183789E78D4 2019-02-10 1
0006D2BC-4DF9-4C0B-83AD-0183789E78D4 2019-02-11 5
0006D2BC-4DF9-4C0B-83AD-0183789E78D4 2019-02-12 7
0006D2BC-4DF9-4C0B-83AD-0183789E78D4 2019-02-13 3
0006D2BC-4DF9-4C0B-83AD-0183789E78D4 2019-02-14 2
0006D2BC-4DF9-4C0B-83AD-0183789E78D4 2019-02-15 4
0006D2BC-4DF9-4C0B-83AD-0183789E78D4 2019-02-16 4
993BD7AD-3B62-BA0C-15AE-A14B85921889 2019-02-10 2
993BD7AD-3B62-BA0C-15AE-A14B85921889 2019-02-11 9
993BD7AD-3B62-BA0C-15AE-A14B85921889 2019-02-12 3
993BD7AD-3B62-BA0C-15AE-A14B85921889 2019-02-13 10
993BD7AD-3B62-BA0C-15AE-A14B85921889 2019-02-14 1
993BD7AD-3B62-BA0C-15AE-A14B85921889 2019-02-15 8
993BD7AD-3B62-BA0C-15AE-A14B85921889 2019-02-16 2
Time taken: 0.102 seconds, Fetched: 14 row(s)
<a name="Y1tyI"></a>
LAG(col,n,default)
与 partitioned by 结合使用，返回当前分区中，当前行之前的第 n 行对应的值。如果没有则默认换回 null。第一个参数为列名，第二个参数为当前行之前第n行（可选，默认为1），第三个参数为缺失时默认值（当前行之前第n行为NULL没有时，返回该默认值，如不指定，则为NULL）。
为了比较每个用户浏览次数与前一天的浏览次数进行比较，查询返回当前浏览次数以及前一天的浏览数量。由于在2019-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)
<a name="V4ogf"></a>
LEAD(col,n,default)
与 LAG 函数相反。
<a name="CA4fi"></a>
FIRST_VALUE(col,布尔值)
第一个参数是需要第一个值的列，第二个（可选）参数必须是默认为false的布尔值。如果设置为true，则跳过空值。
hive> select gid,dt,pv,first_value(pv,true) over(partition by gid order by dt) as first_value from temp_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)
<a name="2f562"></a>
LAST_VALUE(col,布尔值)
与 FIRST_VALUE() 函数相反，这里就不进行演示了。
<a name="cDqav"></a>
over子句
官方 OVER子句 包括几个部分：
- 聚合函数（count, sum, min, max, avg）
- OVER 子句
- PARTITION BY 子句
- ORDER BY 子句
- WINDOW 子句
结合具体的业务场景，SQL 语句如下：<br />
---1)201504月份的销售额
select sum(amount) as total_amt
from order_window
where substr(order_date,1,7)='2015-04'
;
---2)201504月份的订单明细与销售额
select user_name, order_date, amount
,sum(amount) over() as total_amt
from order_window
where substr(order_date,1,7)='2015-04'
;
---3)客户的订单明细与月购买金额
select user_name, order_date, amount
,sum(amount) over (partition by month(order_date)) month_amt
from order_window
;
---4)客户的订单明细与累计购买金额
select user_name, order_date, amount
,sum(amount) over (partition by month(order_date) order by order_date) month_add_amt
from order_window
;
---5)不同窗口的销售额
select
user_name
,order_date
,amount
,sum(amount) over() as sample1 --所有行相加
,sum(amount) over(partition by user_name) as sample2 --按name分组，组内数据相加
,sum(amount) over(partition by user_name order by order_date) as sample3 --按name分组，组内数据累加
,sum(amount) over(partition by user_name order by order_date rows between UNBOUNDED PRECEDING and current row) as sample4 --和sample3一样,由起点到当前行的聚合
,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and current row) as sample5 --当前行和前面一行做聚合
,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and 1 FOLLOWING) as sample6 --当前行和前边一行及后面一行
,sum(amount) over(partition by user_name order by order_date rows between current row and UNBOUNDED FOLLOWING) as sample7 --当前行及后面所有行
from order_window
;
<a name="sWJSK"></a>
windows子句
带有窗口规范的OVER子句。窗口可以在WINDOW子句中单独定义。窗口规范支持如下格式：<br />
| 关键字 | 说明 |
|---|---|
| PRECEDING | 表示当前行之前的行 |
| UNBOUNDED PRECEDING | 表示当前行之前无边界行，即第一行 |
| num PRECEDING | 表示当前行之前第num行 |
| CURRENT ROW | 表示当前行 |
| FOLLOWING | 表示当前行后面的行 |
| UNBOUNDED FOLLOWING | 表示当前行后面无边界行，即最后一行 |
| num FOLLOWING | 表示当前行后面第num行 |
<br />当缺少WINDOW子句并指定使用ORDER BY时，窗口规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从第一行到当前行。<br />当缺少ORDER BY和WINDOW子句时，窗口规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即第一行到最后一行。<br />
<a name="fn1nr"></a>
网友评论