開窗函數有淺入深詳解（一），函數詳解

開窗函數有淺入深詳解（一），函數詳解

在開窗函數出現之前存在著很多用 SQL 語句很難解決的問題，很多都要通過復雜的相關子查詢或者存儲過程來完成。為了解決這些問題，在2003年ISO  SQL標准加入了開窗函數，開窗函數的使用使得這些經典的難題可以被輕松的解決。

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

為了更加清楚地理解，我們來建表並進行相關的查詢（截圖為MSSQLServer中的結果）

        MYSQL,MSSQLServer,DB2:       

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

        Oracle:

      復制代碼 代碼如下:
 CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20), FAge INT,FSalary INT)

注：以下結果只在MSSQLServer中演示：

T_Person 表保存了人員信息，FName 字段為人員姓名，FCity 字段為人員所在的城市名，
FAge  字段為人員年齡，FSalary 字段為人員工資。

然後執行下面的SQL語句向 T_Person表中插入一些演示數據：    

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);  

查看表中的內容：

復制代碼 代碼如下:
select * from T_Person

開窗函數簡介

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

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

要計算所有人員的總數，我們可以執行下面的 SQL語句：

復制代碼 代碼如下:
SELECT COUNT(*) FROM T_Person

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

SELECT FCITY , FAGE , COUNT(*) 

FROM T_Person 

HERE FSALARY<5000 

  執行上面的SQL以後我們會得到下面的錯誤信息：

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

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

SELECT FCITY, FAGE, COUNT(*) 

FROM T_Person 

WHERE FSALARY<5000 

GROUP BY FCITY , FAGE 

  執行完畢我們就能在輸出結果中看到下面的執行結果：       

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

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

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

  執行完畢我們就能在輸出結果中看到下面的執行結果：

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

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

 執行完畢我們就能在輸出結果中看到下面的執行結果：

可以看到與聚合函數不同的是，開窗函數在聚合函數後增加了一個OVER 關鍵字。

開窗函數的調用格式為：

函數名(列) OVER(選項)

    OVER   關鍵字表示把函數當成開窗函數而不是聚合函數。SQL  標准允許將所有聚合函數用做開窗函數，使用OVER 關鍵字來區分這兩種用法。

    在上邊的例子中，開窗函數COUNT(*) OVER()對於查詢結果的每一行都返回所有符合條件的行的條數。OVER關鍵字後的括號中還經常添加選項用以改變進行聚合運算的窗口范圍。

如果OVER關鍵字後的括號中的選項為空，則開窗函數會對結果集中的所有行進行聚合運算。   

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