SQL中的開窗函數是什么

165次閱讀

沒有評論

共計 3433 個字符，預計需要花費 9 分鐘才能閱讀完成。

本篇內容主要講解“SQL 中的開窗函數是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“SQL 中的開窗函數是什么”吧!

OVER 的定義

OVER 用于為行定義一個窗口，它對一組值進行操作，不需要使用 GROUP BY 子句對數據進行分組，能夠在同一行中同時返回基礎行的列和聚合列。

OVER 的語法

OVER ([ PARTITION BY column] [ORDER BY culumn] )

PARTITION BY 子句進行分組；

ORDER BY 子句進行排序。

窗口函數 OVER() 指定一組行，開窗函數計算從窗口函數輸出的結果集中各行的值。

開窗函數不需要使用 GROUP BY 就可以對數據進行分組，還可以同時返回基礎行的列和聚合列。

OVER 的用法

OVER 開窗函數必須與聚合函數或排序函數一起使用，聚合函數一般指 SUM(),MAX(),MIN,COUNT(),AVG() 等常見函數。排序函數一般指 RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE() 等。

OVER 在聚合函數中使用的示例

我們以 SUM 和 COUNT 函數作為示例來給大家演示。

-- 建立測試表和測試數據
CREATE TABLE Employee
ID INT PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
INSERT INTO Employee
VALUES(1, 小明 , 開發部 ,8000),
 (4, 小張 , 開發部 ,7600),
 (5, 小白 , 開發部 ,7000),
 (8, 小王 , 財務部 ,5000),
 (9, null, 財務部 ,NULL),
 (15, 小劉 , 財務部 ,6000),
 (16, 小高 , 行政部 ,4500),
 (18, 小王 , 行政部 ,4000),
 (23, 小李 , 行政部 ,4500),
 (29, 小吳 , 行政部 ,4700);

SUM 后的開窗函數

SELECT *,
 SUM(Salary) OVER(PARTITION BY Groupname)  每個組的總工資,
 SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID)  每個組的累計總工資,
 SUM(Salary) OVER(ORDER BY ID)  累計工資,
 SUM(Salary) OVER()  總工資
from Employee

(提示：可以左右滑動代碼)

結果如下：

其中開窗函數的每個含義不同，我們來具體解讀一下：

SUM(Salary) OVER (PARTITION BY Groupname)

只對 PARTITION BY 后面的列 Groupname 進行分組，分組后求解 Salary 的和。

SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

對 PARTITION BY 后面的列 Groupname 進行分組，然后按 ORDER BY 后的 ID 進行排序，然后在組內對 Salary 進行累加處理。

SUM(Salary) OVER (ORDER BY ID)

只對 ORDER BY 后的 ID 內容進行排序，對排完序后的 Salary 進行累加處理。

SUM(Salary) OVER ()

對 Salary 進行匯總處理

COUNT 后的開窗函數

SELECT *,
 COUNT(*) OVER(PARTITION BY Groupname )  每個組的個數,
 COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID)  每個組的累積個數,
 COUNT(*) OVER(ORDER BY ID)  累積個數  ,
 COUNT(*) OVER()  總個數
from Employee

返回的結果如下圖：

后面的每個開窗函數就不再一一解讀了，可以對照上面 SUM 后的開窗函數進行一一對照。

OVER 在排序函數中使用的示例

我們對 4 個排序函數一一演示

-- 先建立測試表和測試數據
WITH t AS
(SELECT 1 StuID, 一班  ClassName,70 Score
UNION ALL
SELECT 2, 一班 ,85
UNION ALL
SELECT 3, 一班 ,85
UNION ALL
SELECT 4, 二班 ,80
UNION ALL
SELECT 5, 二班 ,74
UNION ALL
SELECT 6, 二班 ,80
SELECT * INTO Scores FROM t;
SELECT * FROM Scores

ROW_NUMBER()

定義：ROW_NUMBER() 函數作用就是將 SELECT 查詢到的數據進行排序，每一條數據加一個序號，他不能用做于學生成績的排名，一般多用于分頁查詢，比如查詢前 10 個查詢 10-100 個學生。ROW_NUMBER() 必須與 ORDER BY 一起使用，否則會報錯。

對學生成績排序

SELECT *,
ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC)  班內排序,
ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS  總排序
FROM Scores;

結果如下：

這里的 PARTITION BY 和 ORDER BY 的作用與我們在上面看到的聚合函數的作用一樣，都是用來進行分組和排序使用的。

此外 ROW_NUMBER() 函數還可以取指定順序的數據。

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS  總排序
FROM Scores
) t WHERE t. 總排序 =2;

結果如下：

RANK()

定義：RANK() 函數，顧名思義排名函數，可以對某一個字段進行排名，這里和 ROW_NUMBER() 有什么不一樣呢？ROW_NUMBER() 是排序，當存在相同成績的學生時，ROW_NUMBER() 會依次進行排序，他們序號不相同，而 Rank() 則不一樣。如果出現相同的，他們的排名是一樣的。下面看例子:

示例

SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
 
SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

結果：

其中上圖是 ROW_NUMBER() 的結果，下圖是 RANK() 的結果。當出現兩個學生成績相同是里面出現變化。RANK() 是 1 -1-3-3-5-6，而 ROW_NUMBER() 則還是 1 -2-3-4-5-6，這就是 RANK() 和 ROW_NUMBER() 的區別了。

DENSE_RANK()

定義：DENSE_RANK() 函數也是排名函數，和 RANK() 功能相似，也是對字段進行排名，那它和 RANK() 到底有什么不同那？特別是對于有成績相同的情況，DENSE_RANK() 排名是連續的，RANK() 是跳躍的排名，一般情況下用的排名函數就是 RANK() 我們看例子：

示例

SELECT 
RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
 
SELECT 
DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

結果如下：

上面是 RANK() 的結果，下面是 DENSE_RANK() 的結果

NTILE()

定義：NTILE() 函數是將有序分區中的行分發到指定數目的組中，各個組有編號，編號從 1 開始，就像我們說的分區一樣，分為幾個區，一個區會有多少個。

SELECT *,NTILE(1) OVER (ORDER BY SCORE DESC) AS  分區后排序  FROM Scores;
SELECT *,NTILE(2) OVER (ORDER BY SCORE DESC) AS  分區后排序  FROM Scores;
SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS  分區后排序  FROM Scores;

結果如下：