欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

一篇文章看懂SQL中的開窗函數(shù)

 更新時間:2022年08月31日 09:49:56   作者:蕭木易  
開窗函數(shù)也叫分析函數(shù)有兩類,一類是聚合開窗函數(shù),一類是排序開窗函數(shù),下面這篇文章主要給大家介紹了關(guān)于SQL中開窗函數(shù)的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下

OVER的定義

OVER用于為行定義一個窗口,它對一組值進(jìn)行操作,不需要使用GROUP BY子句對數(shù)據(jù)進(jìn)行分組,能夠在同一行中同時返回基礎(chǔ)行的列和聚合列。

OVER的語法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

PARTITION BY 子句進(jìn)行分組;

ORDER BY 子句進(jìn)行排序。

窗口函數(shù)OVER()指定一組行,開窗函數(shù)計算從窗口函數(shù)輸出的結(jié)果集中各行的值。

開窗函數(shù)不需要使用GROUP BY就可以對數(shù)據(jù)進(jìn)行分組,還可以同時返回基礎(chǔ)行的列和聚合列。

OVER的用法

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

OVER在聚合函數(shù)中使用的示例

我們以SUM和COUNT函數(shù)作為示例來給大家演示。

--建立測試表和測試數(shù)據(jù)
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,'小王','財務(wù)部',5000),
      (9, null,'財務(wù)部',NULL),
      (15,'小劉','財務(wù)部',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

(提示:可以左右滑動代碼)

結(jié)果如下:

其中開窗函數(shù)的每個含義不同,我們來具體解讀一下:

SUM(Salary) OVER (PARTITION BY Groupname)

只對PARTITION BY后面的列Groupname進(jìn)行分組,分組后求解Salary的和。

SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

對PARTITION BY后面的列Groupname進(jìn)行分組,然后按ORDER BY 后的ID進(jìn)行排序,然后在組內(nèi)對Salary進(jìn)行累加處理。

SUM(Salary) OVER (ORDER BY ID)

只對ORDER BY 后的ID內(nèi)容進(jìn)行排序,對排完序后的Salary進(jìn)行累加處理。

SUM(Salary) OVER ()

對Salary進(jìn)行匯總處理

COUNT后的開窗函數(shù)

SELECT *,
       COUNT(*) OVER(PARTITION BY Groupname ) 每個組的個數(shù),
       COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每個組的累積個數(shù),
       COUNT(*) OVER(ORDER BY ID) 累積個數(shù) ,
       COUNT(*) OVER() 總個數(shù)
from Employee

返回的結(jié)果如下圖:

后面的每個開窗函數(shù)就不再一一解讀了,可以對照上面SUM后的開窗函數(shù)進(jìn)行一一對照。

OVER在排序函數(shù)中使用的示例

我們對4個排序函數(shù)一一演示

--先建立測試表和測試數(shù)據(jù)
WITH t AS
(SELECT 1 StuID,'一班' ClassName,70 Score
UNION ALL
SELECT 2,'一班',85
UNION ALL
SELECT 3,'一班',85
UNION ALL
SELECT 4,'二班',80
UNION ALL
SELECT 5,'二班',74
UNION ALL
SELECT 6,'二班',80
)
SELECT * INTO Scores FROM t;
SELECT * FROM Scores

ROW_NUMBER()

定義:ROW_NUMBER()函數(shù)作用就是將SELECT查詢到的數(shù)據(jù)進(jìn)行排序,每一條數(shù)據(jù)加一個序號,他不能用做于學(xué)生成績的排名,一般多用于分頁查詢,比如查詢前10個 查詢10-100個學(xué)生。ROW_NUMBER()必須與ORDER BY一起使用,否則會報錯。 

對學(xué)生成績排序

SELECT *,
ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班內(nèi)排序,
ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 總排序
FROM Scores;

結(jié)果如下:

這里的PARTITION BY和ORDER BY的作用與我們在上面看到的聚合函數(shù)的作用一樣,都是用來進(jìn)行分組和排序使用的。

此外ROW_NUMBER()函數(shù)還可以取指定順序的數(shù)據(jù)。

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 總排序
FROM Scores
) t WHERE t.總排序=2;

結(jié)果如下:

RANK() 

定義:RANK()函數(shù),顧名思義排名函數(shù),可以對某一個字段進(jìn)行排名,這里和ROW_NUMBER()有什么不一樣呢?ROW_NUMBER()是排序,當(dāng)存在相同成績的學(xué)生時,ROW_NUMBER()會依次進(jìn)行排序,他們序號不相同,而Rank()則不一樣。如果出現(xiàn)相同的,他們的排名是一樣的。下面看例子:

示例

SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
 
SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

結(jié)果:

其中上圖是ROW_NUMBER()的結(jié)果,下圖是RANK()的結(jié)果。當(dāng)出現(xiàn)兩個學(xué)生成績相同是里面出現(xiàn)變化。RANK()是1-1-3-3-5-6,而ROW_NUMBER()則還是1-2-3-4-5-6,這就是RANK()和ROW_NUMBER()的區(qū)別了。

DENSE_RANK() 

定義:DENSE_RANK()函數(shù)也是排名函數(shù),和RANK()功能相似,也是對字段進(jìn)行排名,那它和RANK()到底有什么不同那?特別是對于有成績相同的情況,DENSE_RANK()排名是連續(xù)的,RANK()是跳躍的排名,一般情況下用的排名函數(shù)就是RANK() 我們看例子:

示例

SELECT 
RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
 
SELECT 
DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

結(jié)果如下:

上面是RANK()的結(jié)果,下面是DENSE_RANK()的結(jié)果

NTILE()

定義:NTILE()函數(shù)是將有序分區(qū)中的行分發(fā)到指定數(shù)目的組中,各個組有編號,編號從1開始,就像我們說的'分區(qū)'一樣 ,分為幾個區(qū),一個區(qū)會有多少個。  

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;

結(jié)果如下:

就是將查詢出來的記錄根據(jù)NTILE函數(shù)里的參數(shù)進(jìn)行平分分區(qū)。

總結(jié)

OVER開窗函數(shù)是我們工作中經(jīng)常要使用到的,特別是在做數(shù)據(jù)分析計算的時候,經(jīng)常要對數(shù)據(jù)進(jìn)行分組排序。上面我們額外介紹了聚合函數(shù)和排序函數(shù)的與OVER結(jié)合的使用方法,此外還有很多與OVER一起使用的函數(shù),比如LEAD函數(shù),LAG函數(shù),STRING_AGG函數(shù)等等都會使用到開窗函數(shù)OVER,其使用方法也要務(wù)必掌握。

到此這篇關(guān)于SQL中開窗函數(shù)的文章就介紹到這了,更多相關(guān)SQL開窗函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • MySQL的事件調(diào)度器使用介紹

    MySQL的事件調(diào)度器使用介紹

    這篇文章主要介紹了MySQL的事件調(diào)度器使用介紹,本文講解了事件調(diào)度器的開啟、創(chuàng)建、修改、刪除等操作的使用實例,需要的朋友可以參考下
    2015-06-06
  • mysql中cast函數(shù)用法小結(jié)

    mysql中cast函數(shù)用法小結(jié)

    在MySQL中,CAST函數(shù)用于將一個表達(dá)式轉(zhuǎn)換為指定的數(shù)據(jù)類型,本文主要介紹了mysql中cast函數(shù)用法小結(jié),具有一定的參考價值,感興趣的可以了解一下
    2023-12-12
  • 幾個縮減MySQL以節(jié)省磁盤空間的建議

    幾個縮減MySQL以節(jié)省磁盤空間的建議

    這篇文章主要介紹了幾個縮減MySQL以節(jié)省磁盤空間的建議,主要從表結(jié)構(gòu)和存儲內(nèi)容兩個方面來談減容,需要的朋友可以參考下
    2015-05-05
  • MySQL limit使用方法以及超大分頁問題解決

    MySQL limit使用方法以及超大分頁問題解決

    這篇文章主要給大家介紹了關(guān)于MySQL limit使用方法以及超大分頁問題解決的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-10-10
  • 一條 SQL 語句執(zhí)行過程

    一條 SQL 語句執(zhí)行過程

    這篇文章主要介紹了一條 SQL 語句執(zhí)行過程的相關(guān)資料,沒人詳細(xì)具有一的的參考價值,需要的小伙伴可以參考一下,希望對你的學(xué)習(xí)和工作有所幫助
    2022-03-03
  • mysql增加新用戶無法登陸解決方法

    mysql增加新用戶無法登陸解決方法

    在使用mysql增加新用戶之后,發(fā)現(xiàn)新增的用戶無法登陸,一時束手無策,網(wǎng)上搜集整理了一下,曬出來和大家分享一下,希望可以幫助你們
    2012-11-11
  • SQL?JOIN?子句合并多個表中相關(guān)行全面指南

    SQL?JOIN?子句合并多個表中相關(guān)行全面指南

    這篇文章主要為大家介紹了SQL?JOIN?子句合并多個表中相關(guān)行全面指南,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪
    2023-11-11
  • Docker搭建MySQL并掛載數(shù)據(jù)的全過程

    Docker搭建MySQL并掛載數(shù)據(jù)的全過程

    環(huán)境搭建費時費力,但要必不可少,這篇文章主要給大家介紹了關(guān)于Docker搭建MySQL并掛載數(shù)據(jù)的相關(guān)資料,文中通過圖文以及實例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
    2022-01-01
  • MySQL數(shù)據(jù)庫誤操作后快速回滾的方法

    MySQL數(shù)據(jù)庫誤操作后快速回滾的方法

    這篇文章主要介紹了MySQL數(shù)據(jù)庫誤操作后快速回滾的方法的相關(guān)資料,需要的朋友可以參考下
    2016-12-12
  • Mysql中的innoDB如何解決幻讀

    Mysql中的innoDB如何解決幻讀

    這篇文章主要介紹了Mysql中的innoDB如何解決幻讀,幻讀是指在同一個事務(wù)中,前后兩次查詢相同范圍的時候得到的結(jié)果不一致,文章將介紹InnoDB引入間隙鎖和next-key?lock機制去解決幻讀問題,感興趣的小伙伴可以參考一下
    2022-04-04

最新評論