本篇內(nèi)容主要講解“SQL中的開窗函數(shù)是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“SQL中的開窗函數(shù)是什么”吧!

成都創(chuàng)新互聯(lián)公司堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的鹽湖網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

OVER的定義

OVER用于為行定義一個(gè)窗口，它對(duì)一組值進(jìn)行操作，不需要使用GROUP BY子句對(duì)數(shù)據(jù)進(jìn)行分組，能夠在同一行中同時(shí)返回基礎(chǔ)行的列和聚合列。

OVER的語(yǔ)法

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

PARTITION BY 子句進(jìn)行分組；

ORDER BY 子句進(jìn)行排序。

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

開窗函數(shù)不需要使用GROUP BY就可以對(duì)數(shù)據(jù)進(jìn)行分組，還可以同時(shí)返回基礎(chǔ)行的列和聚合列。

OVER的用法

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

OVER在聚合函數(shù)中使用的示例

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

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

SUM后的開窗函數(shù)

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

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

結(jié)果如下：

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

SUM(Salary) OVER (PARTITION BY Groupname)

只對(duì)PARTITION BY后面的列Groupname進(jìn)行分組，分組后求解Salary的和。

SUM(Salary)OVER (PARTITION BY GroupnameORDER BY ID)

對(duì)PARTITION BY后面的列Groupname進(jìn)行分組，然后按ORDER BY 后的ID進(jìn)行排序，然后在組內(nèi)對(duì)Salary進(jìn)行累加處理。

SUM(Salary)OVER (ORDER BY ID)

只對(duì)ORDER BY 后的ID內(nèi)容進(jìn)行排序，對(duì)排完序后的Salary進(jìn)行累加處理。

SUM(Salary)OVER ()

對(duì)Salary進(jìn)行匯總處理

COUNT后的開窗函數(shù)

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

返回的結(jié)果如下圖：

后面的每個(gè)開窗函數(shù)就不再一一解讀了，可以對(duì)照上面SUM后的開窗函數(shù)進(jìn)行一一對(duì)照。

OVER在排序函數(shù)中使用的示例

我們對(duì)4個(gè)排序函數(shù)一一演示

--先建立測(cè)試表和測(cè)試數(shù)據(jù)
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()函數(shù)作用就是將SELECT查詢到的數(shù)據(jù)進(jìn)行排序，每一條數(shù)據(jù)加一個(gè)序號(hào)，他不能用做于學(xué)生成績(jī)的排名，一般多用于分頁(yè)查詢，比如查詢前10個(gè) 查詢10-100個(gè)學(xué)生。ROW_NUMBER()必須與ORDER BY一起使用，否則會(huì)報(bào)錯(cuò)。

對(duì)學(xué)生成績(jī)排序

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

結(jié)果如下：

這里的PARTITION BY和ORDER BY的作用與我們?cè)谏厦婵吹降木酆虾瘮?shù)的作用一樣，都是用來進(jìn)行分組和排序使用的。

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

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

結(jié)果如下：

RANK()

定義：RANK()函數(shù)，顧名思義排名函數(shù)，可以對(duì)某一個(gè)字段進(jìn)行排名，這里和ROW_NUMBER()有什么不一樣呢？ROW_NUMBER()是排序，當(dāng)存在相同成績(jī)的學(xué)生時(shí)，ROW_NUMBER()會(huì)依次進(jìn)行排序，他們序號(hào)不相同，而Rank()則不一樣。如果出現(xiàn)相同的，他們的排名是一樣的。下面看例子:

示例

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

結(jié)果：

其中上圖是ROW_NUMBER()的結(jié)果，下圖是RANK()的結(jié)果。當(dāng)出現(xiàn)兩個(gè)學(xué)生成績(jī)相同是里面出現(xiàn)變化。RANK()是1-1-3-3-5-6，而ROW_NUMBER()則還是1-2-3-4-5-6，這就是RANK()和ROW_NUMBER()的區(qū)別了。

DENSE_RANK()

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

示例

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

結(jié)果如下：

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

NTILE()

定義：NTILE()函數(shù)是將有序分區(qū)中的行分發(fā)到指定數(shù)目的組中，各個(gè)組有編號(hào)，編號(hào)從1開始，就像我們說的'分區(qū)'一樣 ，分為幾個(gè)區(qū)，一個(gè)區(qū)會(huì)有多少個(gè)。  

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

結(jié)果如下：

就是將查詢出來的記錄根據(jù)NTILE函數(shù)里的參數(shù)進(jìn)行平分分區(qū)。

到此，相信大家對(duì)“SQL中的開窗函數(shù)是什么”有了更深的了解，不妨來實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！