Hive 的窗口函数

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

在 SQL 中有一类函数叫做聚合函数，例如 sum()、avg()、max()、min() 等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是，有时候我们既要显示聚集前的数据，又要显示聚集后的数据，此时我们便引入了窗口函数。窗口函数主要用于 OLAP 数据分析。

在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by子句之前。

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子句中单独定义。窗口规范支持如下格式：

当缺少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的窗口函数

订阅