Hive SQL 窗口函数（hive sql lag）

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

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

窗口函数描述LAG()LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回null。LEAD()LEAD()窗口函数返回分区中当前行后面行（可以指定第几行）的值。 如果没有行，则返回null。FIRST_VALUEFIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。LAST_VALUELAST_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的窗口函数