本文首發(fā)：大數(shù)據(jù)每日嗶嗶-Hive SQL 窗口函數(shù)

Hive 的窗口函數(shù)

在 SQL 中有一類函數(shù)叫做聚合函數(shù)，例如 sum()、avg()、max()、min() 等等，這類函數(shù)可以將多行數(shù)據(jù)按照規(guī)則聚集為一行，一般來講聚集后的行數(shù)是要少于聚集前的行數(shù)的。但是，有時候我們既要顯示聚集前的數(shù)據(jù)，又要顯示聚集后的數(shù)據(jù)，此時我們便引入了窗口函數(shù)。窗口函數(shù)主要用于 OLAP 數(shù)據(jù)分析。

在深入研究Over字句之前，一定要注意：在SQL處理中，窗口函數(shù)都是最后一步執(zhí)行，而且僅位于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 ] )

下面舉個例子，數(shù)據(jù)集如下：

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 結(jié)合使用，返回當(dāng)前分區(qū)中，當(dāng)前行之前的第 n 行對應(yīng)的值。如果沒有則默認換回 null。第一個參數(shù)為列名，第二個參數(shù)為當(dāng)前行之前第n行（可選，默認為1），第三個參數(shù)為缺失時默認值（當(dāng)前行之前第n行為NULL沒有時，返回該默認值，如不指定，則為NULL）。

為了比較每個用戶瀏覽次數(shù)與前一天的瀏覽次數(shù)進行比較，查詢返回當(dāng)前瀏覽次數(shù)以及前一天的瀏覽數(shù)量。由于在2019-02-10之前沒有瀏覽行為，前一天的瀏覽次數(shù)設(shè)置為0（不設(shè)置默認為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 函數(shù)相反。

<a name="CA4fi"></a>

FIRST_VALUE(col,布爾值)

第一個參數(shù)是需要第一個值的列，第二個（可選）參數(shù)必須是默認為false的布爾值。如果設(shè)置為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() 函數(shù)相反，這里就不進行演示了。

<a name="cDqav"></a>

over子句

官方 OVER子句 包括幾個部分：

• 聚合函數(shù)（count, sum, min, max, avg）
• OVER 子句
• PARTITION BY 子句
• ORDER BY 子句
• WINDOW 子句

結(jié)合具體的業(yè)務(wù)場景，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分組，組內(nèi)數(shù)據(jù)相加
    ,sum(amount) over(partition by user_name order by order_date) as sample3 --按name分組，組內(nèi)數(shù)據(jù)累加
    ,sum(amount) over(partition by user_name order by order_date rows between UNBOUNDED PRECEDING and current row) as sample4 --和sample3一樣,由起點到當(dāng)前行的聚合
    ,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and current row) as sample5 --當(dāng)前行和前面一行做聚合
    ,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and 1 FOLLOWING) as sample6 --當(dāng)前行和前邊一行及后面一行
    ,sum(amount) over(partition by user_name order by order_date rows between current row and UNBOUNDED FOLLOWING) as sample7 --當(dāng)前行及后面所有行
from order_window
;

<a name="sWJSK"></a>

windows子句

帶有窗口規(guī)范的OVER子句。窗口可以在WINDOW子句中單獨定義。窗口規(guī)范支持如下格式：<br />

<br />當(dāng)缺少WINDOW子句并指定使用ORDER BY時，窗口規(guī)范默認為RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即從第一行到當(dāng)前行。<br />當(dāng)缺少ORDER BY和WINDOW子句時，窗口規(guī)范默認為ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即第一行到最后一行。<br />

<a name="fn1nr"></a>

參考

• Windowing and Analytics Functions
• HiveQL的窗口函數(shù)