原文位址：https://www.cnblogs.com/lihaoyang/p/6756956.html
開窗函數出現之前存在着很多用 SQL 語句很難解決的問題，很多都要通過複雜的相關子查詢或者存儲過程來完成。為了解決這些問題，在 2003 年 ISO SQL 标準加入了開窗函數，開窗函數的使用使得這些經典的難題可以被輕松的解決。目前在 MSSQLServer、Oracle、DB2 等主流資料庫中都提供了對開窗函數的支援，不過非常遺憾的是 MYSQL 暫時還未對開窗函數給予支援(從8.0版本開始，MySQL已支援開窗函數，可參考：https://blog.csdn.net/qq_41080850/article/category/8607891)。
開窗函數簡介:與聚合函數一樣，開窗函數也是對行集組進行聚合計算，但是它不像普通聚合函數那樣每組隻傳回一個值，開窗函數可以為每組傳回多個值，因為開窗函數所執行聚合計算的行集組是視窗。在 ISO SQL中 規定了這樣的函數為開窗函數，在 Oracle 中則被稱為分析函數。
資料表（Oracle）：T_Person 表儲存了人員資訊，FName 字段為人員姓名，FCity 字段為人員所在的城市名，FAge 字段為人員年齡，FSalary 字段為人員工資
建立測試表T_Person
CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20),FAge INT,FSalary INT)
向 T_Person 表中插入一些示範資料：
NSERT 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進行排序，然後計算從目前行前兩行（2
PRECEDING）到目前行後兩行（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