SQL窗口函數OVER用法實例整理
OVER的定義
OVER用于為行定義一個窗口，它對一組值進行操作，不需要使用GROUP BY子句對數據進行分組，能夠在同一行中同時返回基礎行的列和聚合列。
語法
OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] [ROWS|RANGE BETWEEN 邊界規(guī)則1 and 邊界規(guī)則2])
PARTITION BY 子句進行分組；
ORDER BY 子句進行排序；
ROWS|RANGE 框架是對窗口進行進一步的分區(qū)，框架有兩種范圍限定方式：一種是使用ROWS子句，通過指定當前行之前或之后的固定數目的行來限制分區(qū)中的行數；另一種是RANGE子句，按照排序列的當前值，根據相同值來確定分區(qū)中的行數。
窗口函數OVER()指定一組行，開窗函數計算從窗口函數輸出的結果集中各行的值。
RANGE表示按照值的范圍進行范圍的定義，而ROWS表示按照行的范圍進行范圍的定義；邊界規(guī)則的可取值見下表
用法
OVER開窗函數必須與聚合函數或排序函數一起使用，聚合函數一般指SUM(),MAX(),MIN,COUNT(),AVG()等常見函數。排序函數一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。
示例
創(chuàng)建測試表
create table Employee
(
ID INT PRIMARY KEY
,Name VARCHAR(20)
,GroupName VARCHAR(20)
,Salary INT
) INSERT into Employee VALUES(1, '小明', '開發(fā)部', 8000), (4, '小張', '開發(fā)部', 7600), (5, '小白', '開發(fā)部', 7000), (8, '小王', '財務部', 5000), (9, null, '財務部', NULL), (15, '小劉', '財務部', 6000), (16, '小高', '行政部', 4500), (18, '小王', '行政部', 4000), (23, '小李', '行政部', 4500), (29, '小吳', '行政部', 4700)
;
SUM后的開窗函數
select *
,SUM(Salary) OVER(PARTITION BY Groupname) 每個組的總工資
,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每個組的累計總工資
,SUM(Salary) OVER(ORDER BY ID) 累計工資
,SUM(Salary) OVER() 總工資
from Employee
結果如下：
其中開窗函數的每個含義不同，我們來具體解讀一下：
SUM(Salary) OVER (PARTITION BY Groupname)
只對PARTITION BY后面的列Groupname進行分組，分組后求解Salary的和。
SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)
對PARTITION BY后面的列Groupname進行分組，然后按ORDER BY 后的ID進行排序，然后在組內對Salary進行累加處理。
SUM(Salary) OVER (ORDER BY ID)
只對ORDER BY 后的ID內容進行排序，對排完序后的Salary進行累加處理。
SUM(Salary) OVER ()
對Salary進行匯總處理
排序
ROW_NUMBER()
定義：ROW_NUMBER()函數作用就是將SELECT查詢到的數據進行排序，每一條數據加一個序號
select *
,ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班內排序
,ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 總排序
from Scores
;
RANK()
select ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK]
,*
from Scores
;
select RANK() OVER (ORDER BY SCORE DESC) AS [RANK]
,*
from Scores
;
NTILE()
定義：NTILE(N)函數是將數據分成N塊，然后再添加1~N的序號。
select *
,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分區(qū)后排序
from Scores
;
select *
,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分區(qū)后排序
from Scores
;
select *
,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分區(qū)后排序
from Scores
;
ROWS|RANGE
select *
,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID ROWS between CURRENT ROW and 5 FOLLOWING) 從當前行開始后5行的和
from Employee
參考
https://www.modb.pro/db/42912
總結
到此這篇關于SQL窗口函數OVER用法的文章就介紹到這了,更多相關SQL窗口函數OVER內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
下面小編就為大家?guī)硪黄狹ySQL和Lucene索引的對比分析。小編覺得挺不錯的，現在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-09-09
這篇文章主要介紹了SQL如何按照年月來查詢數據問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要介紹了mysql存儲過程之返回多個值的方法,結合實例形式分析了mysql存儲過程返回多個值的實現方法與PHP調用技巧,需要的朋友可以參考下2019-12-12
這篇文章主要介紹了Mysql離線安裝8.0.26的圖文教程,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-02-02
這篇文章主要介紹了mysql查詢連續(xù)記錄方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-05-05
在數據庫的設計中, 我們通常都是會有很多張表 , 通過表與表之間的關系建立我們想要的數據關系, 所以在多張表的前提下, 多表的關聯查詢就尤為重要，這篇文章主要介紹了MySQL關聯查詢優(yōu)化2022-11-11
這篇文章主要給大家介紹了關于mysql索引最左原則的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-07-07
這篇文章主要介紹了MySQL中NULL和空值的區(qū)別及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
本文章討論了當您沒有足夠的磁盤空間來存儲兩個數據集時,使用帶有安全選項DATA DIRECTORY 的 CLONE INSTANCE 命令,所以接下來小編給大家詳細的介紹一下,MySQL如何避免克隆失敗后再次初始化,需要的朋友可以參考下2023-10-10
死鎖是在并發(fā)執(zhí)行的過程中,兩個或多個事務相互等待對方釋放資源的情況,本文主要介紹了Mysql并發(fā)常見的死鎖及解決方法,具有一定的參考價值,感興趣的可以了解一下2023-12-12