在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行）；
rows 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 ...
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/29428.html
標籤：MySQL