SQL開窗函數是什么

這篇文章主要介紹SQL開窗函數是什么，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

開窗函數：在開窗函數出現之前存在著很多用 SQL 語句很難解決的問題，很多都要通過復雜的相關子查詢或者存儲過程來完成。為了解決這些問題，在 2003 年 ISO SQL 標準加入了開窗函數，開窗函數的使用使得這些經典的難題可以被輕松的解決。目前在 MSSQLServer、Oracle、DB2 等主流數據庫中都提供了對開窗函數的支持，不過非常遺憾的是 MYSQL 暫時還未對開窗函數給予支持。

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

算的行集組是窗口。在 ISO SQL 規定了這樣的函數為開窗函數，在 Oracle 中則被稱為分析函數。

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

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

向 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:

要計算所有人員的總數，我們可以執行下面的 SQL 語句：SELECT COUNT(*) FROM T_Person

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

select fname,
  fcity,
  fsalary,
  (select count(*) from t_person where fsalary < 5000) 工資少于5000員工總數
 from t_person
 where fsalary < 5000

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

select fname, fcity, fsalary, count(*) over() 工資小于5000員工數
 from t_person
 where fsalary < 5000

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

開窗函數格式： 函數名(列) OVER(選項)

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

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

PARTITION BY 子句：

開窗函數的 OVER 關鍵字后括號中的可以使用 PARTITION BY 子句來定義行的分區來供進行聚合計算。與 GROUP BY 子句不同，PARTITION BY 子句創建的分區是獨
立于結果集的，創建的分區只是供進行聚合計算的，而且不同的開窗函數所創建的分區也不互相影響。下面的 SQL 語句用于顯示每一個人員的信息以及所屬城市的人員數：

select fname,fcity,fage,fsalary,count(*) over(partition by fcity) 所在城市人數 from t_person

COUNT(*) OVER(PARTITION BY FCITY)表示對結果集按照FCITY進行分區，并且計算當前行所屬的組的聚合計算結果。比如對于FName等于 Tom的行，它所屬的城市是BeiJing，同
屬于BeiJing的人員一共有6個，所以對于這一列的顯示結果為6。

這就不需要先對fcity分組求和，然后再和t_person表連接查詢了，省事兒。

在同一個SELECT語句中可以同時使用多個開窗函數，而且這些開窗函數并不會相互干
擾。比如下面的SQL語句用于顯示每一個人員的信息、所屬城市的人員數以及同齡人的人數：

--顯示每一個人員的信息、所屬城市的人員數以及同齡人的人數：
select fname,
  fcity,
  fage,
  fsalary,
  count(*) over(partition by fcity) 所屬城市的人個數,
  count(*) over(partition by fage) 同齡人個數
 from t_person

ORDER BY子句：

開窗函數中可以在OVER關鍵字后的選項中使用ORDER BY子句來指定排序規則，而且有的開窗函數還要求必須指定排序規則。使用ORDER BY子句可以對結果集按
照指定的排序規則進行排序，并且在一個指定的范圍內進行聚合運算。ORDER BY子句的語法為：

ORDER BY 字段名 RANGE|ROWS BETWEEN 邊界規則1 AND 邊界規則2

RANGE表示按照值的范圍進行范圍的定義，而ROWS表示按照行的范圍進行范圍的定義；邊界規則的可取值見下表：

“RANGE|ROWS BETWEEN 邊界規則1 AND 邊界規則2”部分用來定位聚合計算范圍，這個子句又被稱為定位框架。

例子程序一：查詢從第一行到當前行的工資總和：

select fname,
  fcity,
  fage,
  fsalary,
  sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) 到當前行工資求和
 from t_person

這里的開窗函數“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary進行排序，然后計算從第
一行（UNBOUNDED PRECEDING）到當前行（CURRENT ROW）的和，這樣的計算結果就是按照
工資進行排序的工資值的累積和。

“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是開窗函數中最常使用的定位框架，為了簡化使用，如果使用的是這種定位框架，則可以省略定位框架聲明部分，
也就是說上邊的sql可以簡化成：

select fname,
  fcity,
  fage,
  fsalary,
  sum(fsalary) over(order by fsalary) 到當前行工資求和
 from t_person

例子程序二：把例子程序一的row換成了range，是按照范圍進行定位的

select fname,
  fcity,
  fage,
  fsalary,
  sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到當前行工資求和
 from t_person

區別：

這個SQL語句與例1中的SQL語句唯一不同的就是“ROWS”被替換成了“RANGE”。“ROWS”是按照行數進行范圍定位的，而“RANGE”則是按照值范圍進行定位的，這兩個不同的定位方式主要用來處理并列排序的情況。比如 Lily、Swing、Bill這三個人的工資都是2000元，如果按照“ROWS”進行范圍定位，則計算從第一條到當前行的累積和，而如果 如果按照 “RANGE”進行范圍定位，則仍然計算從第一條到當前行的累積和，不過由于等于2000元的工資有三個人，所以計算的累積和為從第一條到2000元工資的人員結，所以對 Lily、Swing、Bill這三個人進行開窗函數聚合計算的時候得到的都是7000（ “ 1000+2000+2000+2000 ”）。

下邊這的估計不常用：

例子程序三：

SELECT FName,
  FSalary,
  SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) 前二后二和
 FROM T_Person;

這里的開窗函數“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)”表示按照FSalary進行排序，然后計算從當前行前兩行（2PRECEDING）到當前行后兩行（2 FOLLOWING）的工資和，注意對于第一條和第二條而言它們的“前兩行”是不存在或者不完整的，因此計算的時候也是要按照前兩行是不存在或者不完整進行計算，同樣對于最后兩行數據而言它們的“后兩行”也不存在或者不完整的，同樣要進行類似的處理。

例子程序四：

SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) 后面一到三之和
FROM T_Person;

這里的開窗函數“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)”表示按照FSalary進行排序，然后計算從當前行后一行（1 FOLLOWING）到后三行（3 FOLLOWING）的工資和。注意最后一行沒有后續行，其計算結果為空值NULL而非0。

例子程序五：算工資排名

SELECT FName, FSalary,
COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)
FROM T_Person;

這里的開窗函數“COUNT(*) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary進行排序，然后計算從第一行（UNBOUNDED PRECEDING）到當前行（CURRENT ROW）的人員的個數，這個可以看作是計算人員的工資水平排名。

不再用ROWNUM 了 省事了。這個over簡寫就會出錯。

例子程序6：結合max求到目前行的最大值

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(ORDER BY FAge) 此行之前最大值
FROM T_Person;

這里的開窗函數“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary) OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”的簡化寫法，它表示按照FSalary進行排序，然后計算從第一行（UNBOUNDED PRECEDING）到當前行（CURRENT ROW）的人員的最大工資值。

例子程序6：over(partition by XX order by XX) partition by和order by 結合

員工信息+同齡人最高工資，按工資排序

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(PARTITION BY FAge order by fsalary) 同齡人最高工資
FROM T_Person;

PARTITION BY子句和ORDER BY 可以 共 同 使用，從 而 可以 實現 更 加復 雜 的 功能

==================================================================================

高級開窗函數/ 排名的實現ROW_NUMBER();rank() ,dense_rank()

除了可以在開窗函數中使用COUNT()、SUM()、MIN()、MAX()、AVG()等這些聚合函數，還可以在開窗函數中使用一些高級的函數，有些函數同時被DB2和Oracle同時支持，比如RANK()、DENSE_RANK()、ROW_NUMBER()，而有些函數只被Oracle支持，比如RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。

下面對這幾個函數進行詳細介紹。

RANK()和DENSE_RANK()函數都可以用于計算一行的排名，不過對于并列排名的處理方式不同；ROW_NUMBER()函數計算一行在結果集中的行號，同樣可以將其當成排名函數。這三個函數的功能存在一定的差異，舉例如下：工資從高到低排名：

SELECT FName, FSalary,FAge,
RANK() OVER(ORDER BY fsalary desc) f_RANK,
DENSE_RANK() OVER(ORDER BY fsalary desc) f_DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY fsalary desc) f_ROW_NUMBER
FROM T_Person;

rank(),dense_rank()語法：

RANK()
dense_rank()
【語法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【功能】聚合函數RANK 和 dense_rank 主要的功能是計算一組數值中的排序值。
【參數】dense_rank與rank()用法相當，
【區別】dence_rank在并列關系是，相關等級不會跳過。rank則跳過
rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）
dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名。

row_number() 函數語法：

ROW_NUMBER()
【語法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根據COL1分組，在分組內部根據 COL2排序，而這個值就表示每組內部排序后的順序編號（組內連續的唯一的）
row_number() 返回的主要是“行”的信息，并沒有排名
【參數】
【說明】Oracle分析函數

主要功能：用于取前幾名，或者最后幾名等

===================================================================

排序函數實際場景使用：計算排行榜，排名

微信活動，每天參與，有得分，活動結束后選出排名靠前的發獎。

每參與一次，就是一個訂單，表結構：

比如要查詢期號issue為20170410期的排行榜，按得分倒敘排序，得分一樣按訂單創建先后，算排行，sql需要這么寫：

select ROWNUM rank, t.*
 from (select *
   from t_zhcw_order
   where issue = '20170410'
   order by integral desc, create_date asc) t

使用了開窗函數后就可以簡化：

select t.*,
    row_number() over(order by t.integral desc, t.create_date asc) 排名
   from t_zhcw_order t
   where issue = '20170410'

如果想只要排名范圍，可以在外邊再包一層，這也是高效分頁的一種方式：

select tt.* from (
select t.id,
  t.integral,
  t.cell,
  t.create_date,
  row_number() over(order by t.integral desc, t.create_date asc) rankNum
 from t_zhcw_order t
 where t.issue = 20170331
)tt where tt.rankNum<=50

以上是SQL開窗函數是什么的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！