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

大家好，今天分享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ù)方法。在日常工作中比較常見的例子比如求學(xué)生的單科成績排名、求前三名等等之類的。

窗口函數(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 成績單 ( 學(xué)號 VARCHAR ( 8 ), 姓名 VARCHAR ( 8 ), 科目 VARCHAR ( 8 ), 得分 INT ) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO 成績單
VALUES
 ('1000', '小明', '語文' ,112 ),
 ('1000', '小明', '數(shù)學(xué)' ,120 ),
 ('1000', '小明', '英語' ,92 ),
 ('1001', '云朵', '語文' ,112 ), 
 ('1001', '云朵', '數(shù)學(xué)' ,118 ),
 ('1001', '云朵', '英語' ,99 ), 
 ('1002', '庫里', '語文' ,101 ),
 ('1002', '庫里', '數(shù)學(xué)' ,111 ),
 ('1002', '庫里', '英語' ,90 ),
 ('1003', '才子', '語文' ,112 ), 
 ('1003', '才子', '數(shù)學(xué)' ,120 ),
 ('1003', '才子', '英語' ,112 ), 
 ('1004', '小華', '語文' ,112 ),
 ('1004', '小華', '數(shù)學(xué)' ,112 ),
 ('1004', '小華', '英語' ,112 ),
 ('1005', '強森', '語文' ,92 ), 
 ('1005', '強森', '數(shù)學(xué)' ,120 ),
 ('1005', '強森', '英語' ,92 );

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

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

比如RANK()

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

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

結(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é)果對比如下：

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

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

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ù)

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

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

查詢結(jié)果如下：

NTILE(n)在數(shù)據(jù)分析中應(yīng)用較多，比如由于數(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為當(dāng)前窗口的記錄總行數(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)小于、等于當(dāng)前rank值的行數(shù) / 分組內(nèi)總行數(shù)。

查詢小于等于當(dāng)前成績的比例

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

查詢結(jié)果如下：

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

4. 前后函數(shù)

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

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

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，對應(yīng)得分是120。

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

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é)果如下：

我們可以計算各個同學(xué)與第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)就是當(dāng)前最后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)地應(yīng)用聚合函數(shù)（SUM()、AVG()、MAX()、MIN()、COUNT()），可以動態(tài)計算在指定的窗口內(nèi)的各種聚合函數(shù)值。

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

DROP TABLE
IF
 EXISTS 語文成績單;
CREATE TABLE 語文成績單 ( 學(xué)號 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 );

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

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

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

查詢結(jié)果如下：

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