SQL窗口函數(shù)OVER用法實例整理
OVER的定義
OVER用于為行定義一個窗口，它對一組值進行操作，不需要使用GROUP BY子句對數(shù)據(jù)進行分組，能夠在同一行中同時返回基礎行的列和聚合列。
語法
OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] [ROWS|RANGE BETWEEN 邊界規(guī)則1 and 邊界規(guī)則2])
PARTITION BY 子句進行分組；
ORDER BY 子句進行排序；
ROWS|RANGE 框架是對窗口進行進一步的分區(qū)，框架有兩種范圍限定方式：一種是使用ROWS子句，通過指定當前行之前或之后的固定數(shù)目的行來限制分區(qū)中的行數(shù)；另一種是RANGE子句，按照排序列的當前值，根據(jù)相同值來確定分區(qū)中的行數(shù)。
窗口函數(shù)OVER()指定一組行，開窗函數(shù)計算從窗口函數(shù)輸出的結果集中各行的值。
RANGE表示按照值的范圍進行范圍的定義，而ROWS表示按照行的范圍進行范圍的定義；邊界規(guī)則的可取值見下表
用法
OVER開窗函數(shù)必須與聚合函數(shù)或排序函數(shù)一起使用，聚合函數(shù)一般指SUM(),MAX(),MIN,COUNT(),AVG()等常見函數(shù)。排序函數(shù)一般指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后的開窗函數(shù)
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
結果如下：
其中開窗函數(shù)的每個含義不同，我們來具體解讀一下：
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進行排序，然后在組內(nèi)對Salary進行累加處理。
SUM(Salary) OVER (ORDER BY ID)
只對ORDER BY 后的ID內(nèi)容進行排序，對排完序后的Salary進行累加處理。
SUM(Salary) OVER ()
對Salary進行匯總處理
排序
ROW_NUMBER()
定義：ROW_NUMBER()函數(shù)作用就是將SELECT查詢到的數(shù)據(jù)進行排序，每一條數(shù)據(jù)加一個序號
select *
,ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班內(nèi)排序
,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)函數(shù)是將數(shù)據(jù)分成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窗口函數(shù)OVER用法的文章就介紹到這了,更多相關SQL窗口函數(shù)OVER內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
解決Java程序使用MySQL時返回參數(shù)為亂碼的示例教程
這篇文章主要介紹了解決Java程序使用MySQL返回參數(shù)為亂碼的問題的教程,通過示例來講解utf-8編碼在MySQL中的相關配置,示例需要的朋友可以參考下2015-12-12
這篇文章主要介紹了MySQL數(shù)據(jù)庫之約束簡析,約束是作用于表中字段上的規(guī)則,用于限制存儲在表中的數(shù)據(jù),保證數(shù)據(jù)庫中數(shù)據(jù)的正確、有效性和完整性,需要的朋友可以參考下2023-09-09
這篇文章主要介紹了MySQL與存儲過程的相關資料,需要的朋友可以參考下2007-03-03
這篇文章主要介紹了jdbc連接mysq之serverTimezone設定方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
這篇文章主要介紹了MySQL 兩種恢復數(shù)據(jù)的方法，幫助恢復線上數(shù)據(jù)，保證數(shù)據(jù)完整，感興趣的朋友可以了解下2020-10-10
Linux下MYSQL 5.7 找回root密碼的問題(親測可用)
這篇文章主要介紹了Linux下MYSQL 5.7 找回root密碼的問題(親測可用),通過 --skip-grant-tables 找回,新增完成后，:wq 保存退出，重啟mysqld服務,具體內(nèi)容詳情跟隨小編一起看看吧2021-10-10
MySQL8.0.27安裝過程中卡在Initializing?Database中并報錯的解決
本文主要介紹了MySQL8.0.27安裝過程中卡在Initializing?Database中并報錯的解決，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-05-05
使用MySQL數(shù)據(jù)庫時,我們可能會遇到數(shù)據(jù)庫表被鎖定的情況,本文主要介紹了mysql鎖表確認及解除鎖表的實現(xiàn)示例, 具有一定的參考價值,感興趣的可以了解一下2024-06-06
數(shù)據(jù)庫的索引就像一本書的目錄，能夠加快數(shù)據(jù)庫的查詢速度，MYSQL索引有四種PRIMARY、INDEX、UNIQUE、FULLTEXT，?其中PRIMARY、INDEX、UNIQUE是一類，F(xiàn)ULLTEXT是一類，本文給大家介紹Mysql索引分類及其使用實例，感興趣的朋友一起看看吧2022-07-07
本篇文章介紹了，在mysql中修改數(shù)據(jù)的解決方法。需要的朋友參考下2013-05-05