SQL視窗函數OVER用法範例整理

2022-08-26 14:03:44

OVER的定義

OVER用於為行定義一個視窗，它對一組值進行操作，不需要使用GROUP BY子句對資料進行分組，能夠在同一行中同時返回基礎行的列和聚合列。

語法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] [ROWS|RANGE BETWEEN 邊界規則1 and 邊界規則2])

PARTITION BY 子句進行分組；

ORDER BY 子句進行排序；

ROWS|RANGE 框架是對視窗進行進一步的分割區，框架有兩種範圍限定方式：一種是使用ROWS子句，通過指定當前行之前或之後的固定數目的行來限制分割區中的行數；另一種是RANGE子句，按照排序列的當前值，根據相同值來確定分割區中的行數。
視窗函數OVER()指定一組行，開窗函數計算從視窗函數輸出的結果集中各行的值。

RANGE表示按照值的範圍進行範圍的定義，而ROWS表示按照行的範圍進行範圍的定義；邊界規則的可取值見下表

用法

OVER開窗函數必須與聚合函數或排序函數一起使用，聚合函數一般指SUM(),MAX(),MIN,COUNT(),AVG()等常見函數。排序函數一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

範例

建立測試表

create table Employee
(
    ID INT PRIMARY KEY
    ,Name VARCHAR(20)
    ,GroupName VARCHAR(20)
    ,Salary INT
) INSERT into Employee VALUES(1, '小明', '開發部', 8000), (4, '小張', '開發部', 7600), (5, '小白', '開發部', 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 分割區後排序
from    Scores
;
select  *
        ,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分割區後排序
from    Scores
;
select  *
        ,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分割區後排序
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內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

SQL視窗函數OVER用法範例整理

目錄

OVER的定義

語法

用法

範例

SUM後的開窗函數

排序

ROWS|RANGE

參考

總結

熱門文章