在 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的窗口函數