SQL開窗函數

概述

需求背景：

有時一組資料隻傳回一組值不能滿足需求，如經常想知道各個地區的前幾名、各個班或各個學科的前幾名。這時候需要每一組傳回多個值。

2003 年 ISO SQL 标準加入開窗函數，目前在 MS SQLServer、Oracle、DB2等主流資料庫中都提供對開窗函數的支援，不過MySQL暫時還未對開窗函數給予支援。

簡介：與聚合函數一樣，開窗函數也是對行集組進行聚合計算，但是它不像普通聚合函數那樣每組隻傳回一個值，開窗函數可以為每組傳回多個值，因為開窗函數所執行聚合計算的行集組是視窗。在 ISO SQL 規定這樣的函數為開窗函數，在 Oracle 中則被稱為分析函數。

開窗函數有兩類：一類是聚合開窗函數，一類是排序開窗函數

窗函數和聚合函數的差別：

1. SQL 标準允許将所有聚合函數用作開窗函數，用OVER 關鍵字區分開窗函數和聚合函數。
2. 聚合函數每組隻傳回一個值，開窗函數每組可傳回多個值。

開窗函數在聚合函數後增加一個 OVER 關鍵字。開窗函數格式：​

​函數名(列) OVER(選項)​

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

開窗函數​

​COUNT(*) OVER()​

​對于查詢結果的每一行都傳回所有符合條件的行的條數。OVER 關鍵字後的括号中還經常添加選項用以改變進行聚合運算的視窗範圍。如果 OVER 關鍵字後的括号中的選項為空，則開窗函數會對結果集中的所有行進行聚合運算。

PARTITION BY 子句：

開窗函數的 OVER 關鍵字後括号中的可以使用 PARTITION BY 子句來定義行的分區來供進行聚合計算。與 GROUP BY 子句不同，PARTITION BY 子句建立的分區獨立于結果集，建立的分區隻是供進行聚合計算的，且不同的開窗函數所建立的分區也不互相影響。

顯示每一個人員的資訊以及所屬城市的人員數：

​​

​select name, city, age, salary, count(*) over(partition by city) from person​

​ 在同一個SELECT語句中可以同時使用多個開窗函數，而且這些開窗函數并不會互相幹擾

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​

​部分用來定位聚合計算範圍，這個子句又被稱為定位架構。

​

​RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW​

​是開窗函數中最常使用的定位架構，為了簡化使用，如果使用的是這種定位架構，則可以省略定位架構聲明部分，

進階

在開窗函數中可使用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 name, salary, age, 
RANK() OVER(ORDER BY salary desc) RANK,
DENSE_RANK() OVER(ORDER BY salary desc) DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY salary desc) ROW_NUMBER
FROM person;      

參考