一文搞懂SQL中的開窗函數

這篇文章為大家帶來了關於SQL server的相關知識，開窗函數也叫分析函數有兩類,一類是聚合開窗函數,一類是排序開窗函數,下面這篇文章主要給大家介紹了關於SQL中開窗函數的相關資料,文中透過實例程式碼介紹的非常詳細,需要的朋友可以參考下。

推薦學習：《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;

结果如下：

就是将查询出来的记录根据NTILE函数里的参数进行平分分区。

总结

OVER开窗函数是我们工作中经常要使用到的，特别是在做数据分析计算的时候，经常要对数据进行分组排序。上面我们额外介绍了聚合函数和排序函数的与OVER结合的使用方法，此外还有很多与OVER一起使用的函数，比如LEAD函数，LAG函数，STRING_AGG函数等等都会使用到开窗函数OVER，其使用方法也要务必掌握。

推荐学习：《SQL教程》

以上是一文搞懂SQL中的開窗函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！