首頁 > 資料庫 > SQL > 主體

一文搞懂SQL中的開窗函數

WBOY
發布: 2022-09-02 16:55:48
轉載
3766 人瀏覽過

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

一文搞懂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中文網其他相關文章!

相關標籤:
sql
來源:jb51.net
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板