溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

發(fā)布時間：2020-07-22 13:56:57 來源：億速云 閱讀：236 作者：小豬 欄目：開發(fā)技術

這篇文章主要講解了Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)，內容清晰明了，對此有興趣的小伙伴可以學習一下，相信大家閱讀完之后會有幫助。

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

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

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

數(shù)據(jù)表（Oracle）：T_Person 表保存了人員信息，F(xiàn)Name 字段為人員姓名，F(xiàn)City 字段為人員所在的城市名，F(xiàn)Age 字段為人員年齡，F(xiàn)Salary 字段為人員工資

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

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

select * from t_person:

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

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

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

OVER 關鍵字表示把函數(shù)當成開窗函數(shù)而不是聚合函數(shù)。SQL 標準允許將所有聚合函數(shù)用做開窗函數(shù)，使用 OVER 關鍵字來區(qū)分這兩種用法。
在上邊的例子中，開窗函數(shù) COUNT(*) OVER()對于查詢結果的每一行都返回所有符合條件的行的條數(shù)。OVER 關鍵字后的括號中還經(jīng)常添加選項用以改變進行聚合運算的窗口范圍。如果 OVER 關鍵字后的括號中的選項為空，則開窗函數(shù)會對結果集中的所有行進行聚合運算。

PARTITION BY 子句：

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

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

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

ORDER BY子句：

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

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

這里的開窗函數(shù)“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”是開窗函數(shù)中最常使用的定位框架，為了簡化使用，如果使用的是這種定位框架，則可以省略定位框架聲明部分，
也就是說上邊的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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

區(qū)別：

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

下邊這的估計不常用：

例子程序三：

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

例子程序四：

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

例子程序五：算工資排名

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

這里的開窗函數(shù)“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;

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

高級開窗函數(shù)/ 排名的實現(xiàn)ROW_NUMBER();rank() ,dense_rank()

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

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

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;

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

rank(),dense_rank()語法：

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

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

row_number() 函數(shù)語法：

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

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

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

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

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

使用了開窗函數(shù)后就可以簡化：

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

Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)

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

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

看完上述內容，是不是對Oracle數(shù)據(jù)庫中如何使用SQL開窗函數(shù)有進一步的了解，如果還想學習更多內容，歡迎關注億速云行業(yè)資訊頻道。

向AI問一下細節(jié)

免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權內容。

AI
助
手