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

開窗函數有淺入深詳解（一）。本站提示廣大學習愛好者：（開窗函數有淺入深詳解（一））文章只能為提供參考，不一定能成為您想要的結果。以下是開窗函數有淺入深詳解（一）正文

在開窗函數湧現之前存在著許多用 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症結字後的括號中的選項為空，則開窗函數會對成果集中的一切行停止聚合運算。   

總結：上述講述的是開窗函數的根本用法，願望對年夜家有所贊助！