在開(kāi)窗函數(shù)出現(xiàn)之前存在著很多用 SQL 語(yǔ)句很難解決的問(wèn)題，很多都要通過(guò)復(fù)雜的相關(guān)子查詢或者存儲(chǔ)過(guò)程來(lái)完成。為了解決這些問(wèn)題，在2003年ISO  SQL標(biāo)準(zhǔn)加入了開(kāi)窗函數(shù)，開(kāi)窗函數(shù)的使用使得這些經(jīng)典的難題可以被輕松的解決。

目前在 MSSQLServer、Oracle、DB2 等主流數(shù)據(jù)庫(kù)中都提供了對(duì)開(kāi)窗函數(shù)的支持，不過(guò)非常遺憾的是 MYSQL 暫時(shí)還未對(duì)開(kāi)窗函數(shù)給予支持。

為了更加清楚地理解，我們來(lái)建表并進(jìn)行相關(guān)的查詢（截圖為MSSQLServer中的結(jié)果）

        MYSQL,MSSQLServer,DB2:       

CREATE TABLE T_Person  
( 
  FName VARCHAR(20), 
  FCity VARCHAR(20),  
  FAge INT, 
  FSalary INT 
)  

        Oracle:

注：以下結(jié)果只在MSSQLServer中演示：

T_Person 表保存了人員信息，F(xiàn)Name 字段為人員姓名，F(xiàn)City 字段為人員所在的城市名，
FAge  字段為人員年齡，F(xiàn)Salary 字段為人員工資。

然后執(zhí)行下面的SQL語(yǔ)句向 T_Person表中插入一些演示數(shù)據(jù)：    

INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Tom','BeiJing',20,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Tim','ChengDu',21,4000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Jim','BeiJing',22,3500);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Lily','London',21,2000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('John','NewYork',22,1000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('YaoMing','BeiJing',20,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Swing','London',22,2000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Guo','NewYork',20,2800);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('YuQian','BeiJing',24,8000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Ketty','London',25,8500);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Kitty','ChengDu',25,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Merry','BeiJing',23,3500);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Smith','ChengDu',30,3000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Bill','BeiJing',25,2000);  
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Jerry','NewYork',24,3300);  

查看表中的內(nèi)容：

開(kāi)窗函數(shù)簡(jiǎn)介

  與 聚 合函數(shù)一樣，開(kāi)窗函數(shù)也是對(duì)行集組進(jìn)行聚合計(jì)算，但是它不像普通聚合函數(shù)那樣每組只返回一個(gè)值，開(kāi)窗函數(shù)可以為每組返回多個(gè)值，因?yàn)殚_(kāi)窗函數(shù)所執(zhí)行聚合計(jì)算的行集組是窗口。

在ISO SQL規(guī)定了這樣的函數(shù)為開(kāi)窗函數(shù)，在 Oracle中則被稱(chēng)為分析函數(shù)，而在DB2中則被稱(chēng)為OLAP函數(shù)。

要計(jì)算所有人員的總數(shù)，我們可以執(zhí)行下面的 SQL語(yǔ)句：

         除了這種較簡(jiǎn)單的使用方式，有時(shí)需要從不在聚合函數(shù)中的行中訪問(wèn)這些聚合計(jì)算的值。比如我們想查詢每個(gè)工資小于 5000元的員工信息（城市以及年齡） ，并且在每行中都顯示所有工資小于5000元的員工個(gè)數(shù)，嘗試編寫(xiě)下面的 SQL語(yǔ)句：

SELECT FCITY , FAGE , COUNT(*) 

FROM T_Person 

HERE FSALARY<5000 

  執(zhí)行上面的SQL以后我們會(huì)得到下面的錯(cuò)誤信息：

選擇列表中的列  'T_Person.FCity' 無(wú)效，因?yàn)樵摿袥](méi)有包含在聚合函數(shù)或 GROUP BY 子句中。

  這是因?yàn)樗胁话诰酆虾瘮?shù)中的列必須聲明在GROUP BY 子句中，
可以進(jìn)行如下修改：

SELECT FCITY, FAGE, COUNT(*) 

FROM T_Person 

WHERE FSALARY<5000 

GROUP BY FCITY , FAGE 

  執(zhí)行完畢我們就能在輸出結(jié)果中看到下面的執(zhí)行結(jié)果：       

     這個(gè)執(zhí)行結(jié)果與我們想像的是完全不同的，這是因?yàn)镚ROUP  BY子句對(duì)結(jié)果集進(jìn)行了分組，所以聚合函數(shù)進(jìn)行計(jì)算的對(duì)象不再是所有的結(jié)果集，而是每一個(gè)分組。

可以通過(guò)子查詢來(lái)解決這個(gè)問(wèn)題，SQL如下：

SELECT FCITY , FAGE , 
( 
 SELECT COUNT(* ) FROM T_Person 
 WHERE FSALARY<5000 
) 
FROM T_Person 
WHERE FSALARY<5000

  執(zhí)行完畢我們就能在輸出結(jié)果中看到下面的執(zhí)行結(jié)果：

  雖然使用子查詢能夠解決這個(gè)問(wèn)題，但是子查詢的使用非常麻煩，使用開(kāi)窗函數(shù)則可以大大簡(jiǎn)化實(shí)現(xiàn)，下面的SQL語(yǔ)句展示了如果使用開(kāi)窗函數(shù)來(lái)實(shí)現(xiàn)同樣的效果：

SELECT FCITY , FAGE , COUNT(*) OVER() 
FROM T_Person 
WHERE FSALARY<5000 

 執(zhí)行完畢我們就能在輸出結(jié)果中看到下面的執(zhí)行結(jié)果：

可以看到與聚合函數(shù)不同的是，開(kāi)窗函數(shù)在聚合函數(shù)后增加了一個(gè)OVER 關(guān)鍵字。

開(kāi)窗函數(shù)的調(diào)用格式為：

函數(shù)名(列) OVER(選項(xiàng))

    OVER   關(guān)鍵字表示把函數(shù)當(dāng)成開(kāi)窗函數(shù)而不是聚合函數(shù)。SQL  標(biāo)準(zhǔn)允許將所有聚合函數(shù)用做開(kāi)窗函數(shù)，使用OVER 關(guān)鍵字來(lái)區(qū)分這兩種用法。

    在上邊的例子中，開(kāi)窗函數(shù)COUNT(*) OVER()對(duì)于查詢結(jié)果的每一行都返回所有符合條件的行的條數(shù)。OVER關(guān)鍵字后的括號(hào)中還經(jīng)常添加選項(xiàng)用以改變進(jìn)行聚合運(yùn)算的窗口范圍。

如果OVER關(guān)鍵字后的括號(hào)中的選項(xiàng)為空，則開(kāi)窗函數(shù)會(huì)對(duì)結(jié)果集中的所有行進(jìn)行聚合運(yùn)算。   

總結(jié)：上述講述的是開(kāi)窗函數(shù)的基本用法，希望對(duì)大家有所幫助！

最新評(píng)論