Hive 的窗口函数

本文首发于：微信公众号【大数据每日哔哔，文章：Hive SQL 窗口函数

在 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)

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)

LEAD(col,n,default)

与 LAG 函数相反。

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)

LAST_VALUE(col,布尔值)

与 FIRST_VALUE() 函数相反，这里就不进行演示了。

over子句

官方 OVER子句 包括几个部分：

• 聚合函数（count, sum, min, max, avg）
• OVER 子句
• PARTITION BY 子句
• ORDER BY 子句
• WINDOW 子句

结合具体的业务场景，SQL 语句如下：

---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
;

windows子句

带有窗口规范的OVER子句。窗口可以在WINDOW子句中单独定义。窗口规范支持如下格式：

关键字	说明
PRECEDING	表示当前行之前的行
UNBOUNDED PRECEDING	表示当前行之前无边界行，即第一行
num PRECEDING	表示当前行之前第num行
CURRENT ROW	表示当前行
FOLLOWING	表示当前行后面的行
UNBOUNDED FOLLOWING	表示当前行后面无边界行，即最后一行
num FOLLOWING	表示当前行后面第num行

当缺少WINDOW子句并指定使用ORDER BY时，窗口规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从第一行到当前行。
当缺少ORDER BY和WINDOW子句时，窗口规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即第一行到最后一行。

参考

• Windowing and Analytics Functions
• HiveQL的窗口函数

订阅