SQL中的開窗函數(窗口函數)

Posted on by

窗口函數

  • 簡單理解，就是對查詢的結果多出一列，這一列可以是聚合值，也可以是排序值。
  • 開窗函數一般就是說的是over（）函數，其窗口是由一個 OVER 子句 定義的多行記錄
  • 開窗函數一般分為兩類,聚合開窗函數和排序開窗函數。

簡單來說，窗口函數有以下功能：

1）同時具有分組和排序的功能

2）不減少原表的行數

3）語法如下：

<窗口函數> over (partition by <用於分組的列名>
                order by <用於排序的列名> [rows between ?? and ???])

<窗口函數>的位置，可以放以下兩種函數：

1） 專用窗口函數，包括後面要講到的rank, dense_rank, row_number等專用窗口函數。

2） 聚合函數，如sum(). avg(), count(), max(), min()等,rows between…and…

因為窗口函數是對where或者group by子句處理後的結果進行操作，所以窗口函數原則上隻能寫在select子句中。

3）業務需求“在每組內排名”，比如：

  • 排名問題：每個部門按業績來排名
  • topN問題：找出每個部門排名前N的員工進行獎勵

1.1 排序窗口函數rank

-- 如果我們想在每個班級內按成績排名，得到下面的結果。
select *,
   rank() over (partition by 班級
                 order by 成績 desc) as ranking
from 班級表;

我們來解釋下這個sql語句裡的select子句。rank是排序的函數。要求是“每個班級內按成績排名”，這句話可以分為兩部分：

1）每個班級內：按班級分組

  • partition by用來對表分組。在這個例子中，所以我們指定瞭按“班級”分組（partition by 班級）

2）按成績排名

  • order by子句的功能是對分組後的結果進行排序，默認是按照升序（asc）排列。在本例中（order by 成績 desc）是按成績這一列排序，加瞭desc關鍵詞表示降序排列。

通過下圖，我們就可以理解partiition by（分組）和order by（在組內排序)的作用瞭。 

img

group by分組匯總後改變瞭表的行數，一行隻有一個類別。而partiition by和rank函數不會減少原表中的行數。

preview

註意事項：

  • partition子句可是省略，省略就是不指定分組，隻是按成績由高到低進行瞭排序。但是，這就失去瞭窗口函數的功能，所以一般不要這麼使用。
  • 窗口函數原則上隻能寫在select子句中

1.2 rank(), dense_rank(), row_number()區別

select *,
   rank() over (order by 成績 desc) as ranking,
   dense_rank() over (order by 成績 desc) as dese_rank,
   row_number() over (order by 成績 desc) as row_num
from 班級表

得到結果：

img

從上面的結果可以看出：

  • rank函數：這個例子中是5位，5位，5位，8位，也就是如果有並列名次的行，會占用下一名次的位置。比如正常排名是1，2，3，4，但是現在前3名是並列的名次，結果是：1，1，1，4。
  • dense_rank函數：這個例子中是5位，5位，5位，6位，也就是如果有並列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是現在前3名是並列的名次，結果是：1，1，1，2。
  • row_number函數：這個例子中是5位，6位，7位，8位，也就是不考慮並列名次的情況。比如前3名是並列的名次，排名是正常的1，2，3，4。

1.3 排序截取數據lag(),lead(),ntile(),cume_dist()

  • LAG(col,n,default_val)：獲取往前第n行數據，col是列名，n是往上的行數，當第n行為null的時候取default_val
  • LEAD(col,n, default_val)：往後第n行數據，col是列名，n是往下的行數，當第n行為null的時候取default_val
  • NTILE(n)：把有序分區中的行分發到指定數據的組中，各個組有編號，編號從1開始，對於每一行，NTILE返回此行所屬的組的編號。
  • cume_dist（），計算某個窗口或分區中某個值的累積分佈。假定升序排序，則使用以下公式確定累積分佈：

小於等於當前值x的行數 / 窗口或partition分區內的總行數。其中，x 等於 order by 子句中指定的列的當前行中的值。

1.4 聚合函數作為窗口函數

聚和窗口函數和上面提到的專用窗口函數用法完全相同，隻需要把聚合函數寫在窗口函數的位置即可，但是函數後面括號裡面不能為空，需要指定聚合的列名。

我們來看一下窗口函數是聚合函數時，會出來什麼結果：

select *,
   sum(成績) over (order by 學號) as current_sum,
   avg(成績) over (order by 學號) as current_avg,
   count(成績) over (order by 學號) as current_count,
   max(成績) over (order by 學號) as current_max,
   min(成績) over (order by 學號) as current_min
from 班級表

img

如上圖，聚合函數sum在窗口函數中，是對自身記錄、及位於自身記錄以上的數據進行求和的結果。比如0004號，在使用sum窗口函數後的結果，是對0001，0002，0003，0004號的成績求和，若是0005號，則結果是0001號~0005號成績的求和，以此類推。

不僅是sum求和，平均、計數、最大最小值，也是同理，都是針對自身記錄、以及自身記錄之上的所有數據進行計算，

這樣使用窗口函數有什麼用呢？

聚合函數作為窗口函數，可以在每一行的數據裡直觀的看到，截止到本行數據，統計數據是多少（最大值、最小值等）。同時可以看出每一行數據，對整體統計數據的影響。

1.5 over(- – rows between and )

sum()/... over ([partition by 列名] [order by 列名] [rows between ... and ...] )
-- 從起點到當前行數據聚合
between unbounded preceding and current row 
-- 往前2行到往後1行的數據聚合
between 2 preceding and 1 following 

rows必須跟在Order by 子句之後，對排序的結果進行限制，使用固定的行數來限制分區中的數據行數量。

  • OVER()：指定分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變而變化。
  • CURRENT ROW：當前行
  • n PRECEDING：往前n行數據
  • n FOLLOWING：往後n行數據
  • UNBOUNDED：起點，unbounded preceding 表示從表數據的起點， unbounded following表示到後面的終點
select name,subject,score,
sum(score) over() as sum1,
sum(score) over(partition by subject) as sum2,
sum(score) over(partition by subject order by score) as sum3, 
-- 由起點到當前行的窗口聚合，和sum3一樣
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4, 
-- 當前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
-- 當前行的前面一行和後面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding AND 1 following) as sum6,
-- 當前和後面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name  | subject  | score  | sum1  | sum2  | sum3  | sum4  | sum5  | sum6  | sum7  |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孫悟空   | 數學       | 12     | 359   | 185   | 12    | 12    | 12    | 31    | 185   |
| 沙悟凈   | 數學       | 19     | 359   | 185   | 31    | 31    | 31    | 104   | 173   |
| 豬八戒   | 數學       | 73     | 359   | 185   | 104   | 104   | 92    | 173   | 154   |
| 唐玄奘   | 數學       | 81     | 359   | 185   | 185   | 185   | 154   | 154   | 81    |
| 豬八戒   | 英語       | 11     | 359   | 80    | 11    | 11    | 11    | 26    | 80    |
| 孫悟空   | 英語       | 15     | 359   | 80    | 26    | 26    | 26    | 49    | 69    |
| 唐玄奘   | 英語       | 23     | 359   | 80    | 49    | 49    | 38    | 69    | 54    |
| 沙悟凈   | 英語       | 31     | 359   | 80    | 80    | 80    | 54    | 54    | 31    |
| 孫悟空   | 語文       | 10     | 359   | 94    | 10    | 10    | 10    | 31    | 94    |
| 唐玄奘   | 語文       | 21     | 359   | 94    | 31    | 31    | 31    | 53    | 84    |
| 沙悟凈   | 語文       | 22     | 359   | 94    | 53    | 53    | 43    | 84    | 63    |
| 豬八戒   | 語文       | 41     | 359   | 94    | 94    | 94    | 63    | 63    | 41    |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：