今天給大家分享下關于SQL的窗口函數(shù)基礎。

目錄：

1. 窗口函數(shù)是什么

2. 排序函數(shù)

3. 分布函數(shù)

4. 前后函數(shù)

5. 首尾函數(shù)

6. 聚合函數(shù)

1. 窗口函數(shù)是什么

窗口函數(shù)，也叫OLAP函數(shù)（Online Anallytical Processing，聯(lián)機分析處理），可以對數(shù)據(jù)庫數(shù)據(jù)進行實時分析處理。

mysql從8.0版本開始支持窗口函數(shù)了，今天我們就是以mysql為例來介紹這個窗口函數(shù)的。

窗口其實是指一個記錄集合，而窗口函數(shù)則是在滿足某些條件的記錄集合上執(zhí)行指定的函數(shù)方法。在日常工作中比較常見的例子比如求學生的單科成績排名、求前三名等等之類的。

窗口函數(shù)的基本語法如下：

<窗口函數(shù)>?OVER?(PARTITION?BY?<用于分組的列名>?ORDER?BY?<用于排序的列名>)

像一些聚合函數(shù)如 SUM()、AVG()、COUNT()、MAX()與MIN()等等，以及專用的窗口函數(shù)RANK()、DENSE_RANK()與ROW_NUMBER()等等。

2. 排序函數(shù)

就是進行排序操作，顯示排名

RANK()、DENSE_RANK()與ROW_NUMBER()

我們先創(chuàng)建數(shù)據(jù)表如下：

DROP?TABLE IFEXISTS?成績單; CREATE?TABLE?成績單?(?學號?VARCHAR?(?8?),?姓名?VARCHAR?(?8?),?科目?VARCHAR?(?8?),?得分?INT?)?ENGINE?=?INNODB?DEFAULT?CHARSET?=?utf8; INSERT?INTO?成績單 VALUES('1000',?'小明',?'語文'?,112?),('1000',?'小明',?'數(shù)學'?,120?),('1000',?'小明',?'英語'?,92?),('1001',?'云朵',?'語文'?,112?),?('1001',?'云朵',?'數(shù)學'?,118?),('1001',?'云朵',?'英語'?,99?),?('1002',?'庫里',?'語文'?,101?),('1002',?'庫里',?'數(shù)學'?,111?),('1002',?'庫里',?'英語'?,90?),('1003',?'才子',?'語文'?,112?),?('1003',?'才子',?'數(shù)學'?,120?),('1003',?'才子',?'英語'?,112?),?('1004',?'小華',?'語文'?,112?),('1004',?'小華',?'數(shù)學'?,112?),('1004',?'小華',?'英語'?,112?),('1005',?'強森',?'語文'?,92?),?('1005',?'強森',?'數(shù)學'?,120?),('1005',?'強森',?'英語'?,92?);

這是一張成績表，分別是學號、姓名、科目與得分。

成績表

面對上面這份數(shù)據(jù)，我們要求各科目學生們得分排名，就可以用到排序函數(shù)。

比如RANK()

SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?RANK_排名 FROM成績單

這個操作是按照科目進行分組，然后按照得分進行排序（DESC是由大到小）。

結果如下：

RANK()

可以看到，對于同樣得分而言，RANK()下的名次是同樣的，而且名次中存在間隙（不一定連續(xù)）。

我們來看RANK()、DENSE_RANK()與ROW_NUMBER()三者的差異：

SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?RANK_排名?,DENSE_RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?DENSE_RANK_排名?,ROW_NUMBER()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?ROW_NUMBER_排名 FROM成績單

結果對比如下：

差異對比

可以看到這三者的作用如下：

函數(shù)說明

ROW_NUMBER	為表中的每一行分配一個序號，可以指定分組（也可以不指定）及排序字段(連續(xù)且不重復)
DENSE_RANK	根據(jù)排序字段為每個分組中的每一行分配一個序號。排名值相同時，序號相同，序號中沒有間隙（1,1,1,2,3這種）
RANK	根據(jù)排序字段為每個分組中的每一行分配一個序號。排名值相同時，序號相同，但序號中存在間隙（1,1,1,4,5這種）

我們要獲取各科目排名第一的學生及得分，就可以再加個條件判斷即可，需要注意這里用到了子查詢。

SELECT*? FROM(?SELECT?*,?DENSE_RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?DENSE_RANK_排名?FROM?成績單?)?a? WHEREDENSE_RANK_排名?=?1;

查詢結果如下：

DENSE_RANK_排名第一

另外還有個NTILE(n)將分區(qū)中的有序數(shù)據(jù)分為n個等級，記錄等級數(shù)

比如按照學號分區(qū)得分排序進行分2個等級

SELECT*,NTILE(2)?OVER?(?PARTITION?BY?學號?ORDER?BY?得分?DESC?)?AS?NTILE_ FROM成績單

查詢結果如下：

NTILE(2)

NTILE(n)在數(shù)據(jù)分析中應用較多，比如由于數(shù)據(jù)量大，需要將數(shù)據(jù)平均分配到n個并行的進程分別計算，此時就可以用NTILE(n)對數(shù)據(jù)進行分組（由于記錄數(shù)不一定被n整除，所以數(shù)據(jù)不一定完全平均），然后將不同桶號的數(shù)據(jù)再分配。

3. 分布函數(shù)

分布函數(shù)有兩個PERCENT_RANK()和CUME_DIST()

**PERCENT_RANK()**的用途是每行按照公式(rank-1) / (rows-1)進行計算。其中，rank為RANK()函數(shù)產(chǎn)生的序號，rows為當前窗口的記錄總行數(shù)。

SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?RANK_排名?,PERCENT_RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?PERCENT_RANK_ FROM成績單

查詢結果如下：

PERCENT_RANK()

CUME_DIST()的用途是分組內(nèi)小于、等于當前rank值的行數(shù) / 分組內(nèi)總行數(shù)。

查詢小于等于當前成績的比例

SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?RANK_排名?,CUME_DIST()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?CUME_DIST_ FROM成績單

查詢結果如下：

CUME_DIST()

可以看到，數(shù)學科目中有0.5也就是50%的朋友得分120，超過66.66%的學生成績在118分及以上。

4. 前后函數(shù)

查詢當前行指定字段往前后N行數(shù)據(jù)，LAG() 和 LEAD()

前N行LAG(expr[,N[,default]])，比如我們看各科目同學每個人往前3名的同學得分。

SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?RANK_排名?,LAG(得分,?3)?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC)?AS?LAG_ FROM成績單

查詢結果如下：

LAG(得分, 3)

可以看到，各科目前三行都是NULL空值，這是因為前三行不存在它們往前3行的值。rank 4的前3是rank 1，對應得分是120。

這個可以用于進行一些諸如環(huán)比的情況，在這里我們可以計算當前同學與前1名同學得分差值，操作如下：

SELECT*,LAG_?-?得分? FROM(SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?RANK_排名,LAG(得分,?1?)?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?LAG_?FROM成績單?)?a

查詢結果如下：

LEAD(expr[,N[,default]])就是往后N名了，這里就不再贅述。

5. 首尾函數(shù)

查詢指定字段第一或最后的數(shù)據(jù)FIRST_VALUE(expr)和LAST_VALUE(expr)

查詢各科目得分第1的分值

SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?RANK_排名,FIRST_VALUE(得分)?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?FIRST_VALUE_得分? FROM成績單

查詢結果如下：

FIRST_VALUE(得分)

我們可以計算各個同學與第1名的差距（上面前后函數(shù)部分介紹了和前1名的差距）：

SELECT*,FIRST_VALUE_得分?-?得分? FROM(SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?RANK_排名,FIRST_VALUE(得分)?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?FIRST_VALUE_得分?FROM成績單?)?a

查詢結果如下：

LAST_VALUE(expr)就是最后1名了，這里不再贅述。

另外還有NTH_VALUE(expr, n)查詢指定字段有序行的第n的值

比如查詢排名第4的數(shù)據(jù)

SELECT*,RANK()?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?RANK_排名,NTH_VALUE(得分,4)?OVER?(?PARTITION?BY?科目?ORDER?BY?得分?DESC?)?AS?NTH_VALUE_得分? FROM成績單

查詢結果如下：

NTH_VALUE(得分,4)

6. 聚合函數(shù)

在窗口中每條記錄動態(tài)地應用聚合函數(shù)（SUM()、AVG()、MAX()、MIN()、COUNT()），可以動態(tài)計算在指定的窗口內(nèi)的各種聚合函數(shù)值。

所以，這里我們構造一個帶有時間字段的數(shù)據(jù)表。

DROP?TABLE IFEXISTS?語文成績單; CREATE?TABLE?語文成績單?(?學號?VARCHAR?(?8?),?姓名?VARCHAR?(?8?),?時間??DATE,?得分?INT?)?ENGINE?=?INNODB?DEFAULT?CHARSET?=?utf8; INSERT?INTO?語文成績單 VALUES('1000',?'小明',?'2022-01-02'?,102?),('1001',?'云朵',?'2022-01-04'?,112?),?('1002',?'庫里',?'2022-01-07'?,101?),('1003',?'才子',?'2022-01-07'?,118?),??('1004',?'小華',?'2022-01-08'?,112?),('1005',?'強森',?'2022-01-09'?,92?);

這是一張語文成績表，分別是學號、姓名、時間與得分。

語文成績表

比如，我們要查詢在截止每個時間語文最高分，可以這樣操作：

SELECT*,MAX(得分)?OVER?(?ORDER?BY?時間?)?AS?MAX_? FROM語文成績單

查詢結果如下：

MAX(得分)

以上就是本次的基礎介紹，日常工作的的實際操作應該會更加復雜，不過抽絲剝繭我們總會發(fā)現(xiàn)復雜都是由很多基礎拼接而成，打好基礎就可以變得很強！

-?END -

本文為轉(zhuǎn)載分享&推薦閱讀，若侵權請聯(lián)系后臺刪除 對比Excel系列圖書累積銷量達15w冊，讓你輕松掌握數(shù)據(jù)分析技能，可以在全網(wǎng)搜索書名進行了解：

總結

以上是生活随笔為你收集整理的SQL数据分析实战：好用的窗口函数的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。