SQL數(shù)據(jù)分析實戰(zhàn)：好用的窗口函數(shù)

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

目錄：

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
IF
?EXISTS?成績單;
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是由大到?。?/p>

結(jié)果如下：

可以看到，對于同樣得分而言，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
?成績單

結(jié)果對比如下：

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

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

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

查詢結(jié)果如下：

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

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

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

查詢結(jié)果如下：

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
?成績單

查詢結(jié)果如下：

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
?成績單

查詢結(jié)果如下：

可以看到，數(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
?成績單

查詢結(jié)果如下：

可以看到，各科目前三行都是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

查詢結(jié)果如下：

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
?成績單

查詢結(jié)果如下：

我們可以計算各個同學與第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

查詢結(jié)果如下：

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
?成績單

查詢結(jié)果如下：

6. 聚合函數(shù)

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

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

DROP?TABLE
IF
?EXISTS?語文成績單;
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
?語文成績單

查詢結(jié)果如下：

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

本文為轉(zhuǎn)載分享&推薦閱讀，若侵權(quán)請聯(lián)系后臺刪除

對比Excel系列圖書累積銷量達15w冊，讓你輕松掌握數(shù)據(jù)分析技能，可以在全網(wǎng)搜索書名進行了解：