Hive-視窗函數

在Hive中支持視窗函數，Mysql在8.0版本後也支持使用，用好之後猶如開掛！

Window Function又稱為視窗函數、分析函數。聚合函數可以將多行數據按照規定聚合為一行，一般來講聚集後的行數要少於聚集前的行數。但是有時我們想要既顯示聚集前的數據，又要顯示聚集後的數據，這時便引入了視窗函數。

運行順序：視窗函數是在select時執行的，位於order by之前。

1. 累計計算視窗函數

語法總結：

sum(A) over(partition by B order by C rows between D1 and D2)

avg(A) over(partition by B order by C rows between D1 and D2)

max(A) over(partition by B order by C rows between D1 and D2)

min(A) over(partition by B order by C rows between D1 and D2)

count(A) over(partition by B order by C rows between D1 and D2)

partition by:相當於分組

order by：按照什麼順序進行累加等。預設升序asc, 降序為desc。

A:需要被加工的欄位名稱，對指定欄位計算

B：分組的欄位名稱

C:排序的欄位名稱

D1,D2:計算的行數範圍

rows between：也叫window子句

unbounded:無界的

preceding:之前

following:之後

current row:當前行

unbounded preceding:前面的起點

unbounded following:後面的終點

rows between unbounded preceding and current row:之前所有行和本行，不寫rows between為預設起點到當前行；

rows between 3 preceding and current row：前3行和本行（共4行）；

range between current row and unbounded following：本行和之後的所有行；

rows between current row and 3 following：本行和後面3行（共4行）；

rows between 3 preceding and 1 following: 從前3行到下一行（共4行）。

2. 分區排序視窗函數

1. row_number() over(partition by A order by B)

2.            rank() over(partition by A order by B)

3.   desc_rank() over(partition by A order by B)

返回相應規則的排序序號

1. 生成1 2 3 4 5 6 7 8 9 ...  : row_number() 查詢出來的每一行生成一個序號，依次排序，且不重覆

2 .生成1 1 1 4 5 6 7 7 9 ...   : rank() 生成的序號相同時，下一個不同的會跳躍，跳躍排序

3. 生成1 1 1 2 3 3 4 5 6...   ：desc_rank()生成的序號相同時，下一個不同的不會跳躍，是連續排序

3. 切片：分組排序視窗函數

ntile(n) over(partition by A order by B)

n：切分的片數

A：分組的欄位名稱

B：排序的欄位名稱

ntile(n)：用於將分組數據按照順序切分成n片，返回切片值

不支持 rows between...

如果切片不均勻，預設從第一個開始均分，如 5 5 4 4

4. 偏移分析視窗函數

lag：向上偏移  ---比如向上偏移2行 實則新行數據由原來的行整體下移兩行，前兩行出現空值可由預設值填充

lead：向下偏移 --同理

lag(A,offset,defval) over(partition by ... order by ...)

A:欄位名稱

offset：

• 偏移量，即是向上偏移一個或n個的值，假設當前行為第5行，offset為3，則表示要找的數據為數據行的第2行（5-3=2）
• 預設值為1

defavl：

• 指定預設值：當取得值超出表的範圍，則將defavl指定的值作為預設值
• 沒指定預設值則返回null

例如 : lag(A,1,A) over(partition by USER_NAME order by A)

　　  lag(A) over(partition by USER_NAME order by A)

　　  lag(A,2) over(partition by USER_NAME order by A)

lag(A,offset,defval) over(partition by ... order by ...)

註意：一定要習慣取別名

lag( ... ) over(...) as ...

lead( ... ) over(...) as ...