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

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

算的行集組是窗口。在 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:

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

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

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

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

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

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

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

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

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

PARTITION BY 子句：

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

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

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

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

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

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

ORDER BY子句：

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

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

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

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

例子程序一：查詢從第一行到當(dāng)前行的工資總和：

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

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

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

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

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

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

區(qū)別：

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

下邊這的估計(jì)不常用：

例子程序三：

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

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

例子程序四：

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

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

例子程序五：算工資排名

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

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

不再用ROWNUM 了 省事了。這個(gè)over簡寫就會(huì)出錯(cuò)。

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

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

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

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

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

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

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

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

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

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

下面對(duì)這幾個(gè)函數(shù)進(jìn)行詳細(xì)介紹。

RANK()和DENSE_RANK()函數(shù)都可以用于計(jì)算一行的排名，不過對(duì)于并列排名的處理方式不同；ROW_NUMBER()函數(shù)計(jì)算一行在結(jié)果集中的行號(hào)，同樣可以將其當(dāng)成排名函數(shù)。這三個(gè)函數(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;

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 主要的功能是計(jì)算一組數(shù)值中的排序值。
【參數(shù)】dense_rank與rank()用法相當(dāng)，
【區(qū)別】dence_rank在并列關(guān)系是，相關(guān)等級(jí)不會(huì)跳過。rank則跳過
rank()是跳躍排序，有兩個(gè)第二名時(shí)接下來就是第四名（同樣是在各個(gè)分組內(nèi)）
dense_rank()l是連續(xù)排序，有兩個(gè)第二名時(shí)仍然跟著第三名。

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

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

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

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

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

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

每參與一次，就是一個(gè)訂單，表結(jié)構(gòu)：

比如要查詢期號(hào)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

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

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

到此這篇關(guān)于SQL開窗函數(shù)的具體實(shí)現(xiàn)詳解的文章就介紹到這了,更多相關(guān)SQL開窗函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！