開窗函數(shù)有淺入深詳解（一）

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

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

為了更加清楚地理解，我們來建表并進(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語句向 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)容：

開窗函數(shù)簡介

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

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

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

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

SELECT FCITY , FAGE , COUNT(*) 

FROM T_Person 

HERE FSALARY5000 

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

選擇列表中的列  'T_Person.FCity' 無效，因為該列沒有包含在聚合函數(shù)或 GROUP BY 子句中。

  這是因為所有不包含在聚合函數(shù)中的列必須聲明在GROUP BY 子句中，
可以進(jìn)行如下修改：

SELECT FCITY, FAGE, COUNT(*) 

FROM T_Person 

WHERE FSALARY5000 

GROUP BY FCITY , FAGE 

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

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

可以通過子查詢來解決這個問題，SQL如下：

SELECT FCITY , FAGE , 
( 
 SELECT COUNT(* ) FROM T_Person 
 WHERE FSALARY5000 
) 
FROM T_Person 
WHERE FSALARY5000

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

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

SELECT FCITY , FAGE , COUNT(*) OVER() 
FROM T_Person 
WHERE FSALARY5000 

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

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

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

函數(shù)名(列) OVER(選項)

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

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

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

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