建站
咨詢
售后
建站咨詢
新聞中心
大家好，我是才哥。
創(chuàng)新互聯(lián)主營(yíng)邢臺(tái)網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,app軟件開(kāi)發(fā),邢臺(tái)h5成都小程序開(kāi)發(fā)搭建,邢臺(tái)網(wǎng)站營(yíng)銷推廣歡迎邢臺(tái)等地區(qū)企業(yè)咨詢
感覺(jué)這個(gè)春節(jié)假期在除夕過(guò)完之后吧，時(shí)間就過(guò)的非?？炝?，余額已經(jīng)明顯不足了。嗯，是開(kāi)始可以學(xué)習(xí)起來(lái)了!
目錄：
- 1. 窗口函數(shù)是什么
- 2. 排序函數(shù)
- 3. 分布函數(shù)
- 4. 前后函數(shù)
- 5. 首尾函數(shù)
- 6. 聚合函數(shù)
1. 窗口函數(shù)是什么
窗口函數(shù)，也叫OLAP函數(shù)(Online Anallytical Processing，聯(lián)機(jī)分析處理)，可以對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理。
mysql從8.0版本開(kāi)始支持窗口函數(shù)了，今天我們就是以mysql為例來(lái)介紹這個(gè)窗口函數(shù)的。
窗口其實(shí)是指一個(gè)記錄集合，而窗口函數(shù)則是在滿足某些條件的記錄集合上執(zhí)行指定的函數(shù)方法。在日常工作中比較常見(jiàn)的例子比如求學(xué)生的單科成績(jī)排名、求前三名等等之類的。
窗口函數(shù)的基本語(yǔ)法如下：
<窗口函數(shù)> OVER (PARTITION BY <用于分組的列名> ORDER BY <用于排序的列名>)
像一些聚合函數(shù)如 SUM()、AVG()、COUNT()、MAX()與MIN()等等，以及專用的窗口函數(shù)RANK()、DENSE_RANK()與ROW_NUMBER()等等。
2. 排序函數(shù)
就是進(jìn)行排序操作，顯示排名
RANK()、DENSE_RANK()與ROW_NUMBER()
我們先創(chuàng)建數(shù)據(jù)表如下：
DROP TABLE
IF
EXISTS 成績(jī)單;
CREATE TABLE 成績(jī)單 ( 學(xué)號(hào) VARCHAR ( 8 ), 姓名 VARCHAR ( 8 ), 科目 VARCHAR ( 8 ), 得分 INT ) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO 成績(jī)單
VALUES
('1000', '小明', '語(yǔ)文' ,112 ),
('1000', '小明', '數(shù)學(xué)' ,120 ),
('1000', '小明', '英語(yǔ)' ,92 ),
('1001', '云朵', '語(yǔ)文' ,112 ),
('1001', '云朵', '數(shù)學(xué)' ,118 ),
('1001', '云朵', '英語(yǔ)' ,99 ),
('1002', '庫(kù)里', '語(yǔ)文' ,101 ),
('1002', '庫(kù)里', '數(shù)學(xué)' ,111 ),
('1002', '庫(kù)里', '英語(yǔ)' ,90 ),
('1003', '才子', '語(yǔ)文' ,112 ),
('1003', '才子', '數(shù)學(xué)' ,120 ),
('1003', '才子', '英語(yǔ)' ,112 ),
('1004', '小華', '語(yǔ)文' ,112 ),
('1004', '小華', '數(shù)學(xué)' ,112 ),
('1004', '小華', '英語(yǔ)' ,112 ),
('1005', '強(qiáng)森', '語(yǔ)文' ,92 ),
('1005', '強(qiáng)森', '數(shù)學(xué)' ,120 ),
('1005', '強(qiáng)森', '英語(yǔ)' ,92 );
這是一張成績(jī)表，分別是學(xué)號(hào)、姓名、科目與得分。
成績(jī)表
面對(duì)上面這份數(shù)據(jù)，我們要求各科目學(xué)生們得分排名，就可以用到排序函數(shù)。
比如RANK()
SELECT
*,
RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名
FROM
成績(jī)單
這個(gè)操作是按照科目進(jìn)行分組，然后按照得分進(jìn)行排序(DESC是由大到小)。
結(jié)果如下：
RANK()
可以看到，對(duì)于同樣得分而言，RANK()下的名次是同樣的，而且名次中存在間隙(不一定連續(xù))。
我們來(lái)看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
成績(jī)單
結(jié)果對(duì)比如下：
差異對(duì)比
可以看到這三者的作用如下：
|
函數(shù) |
說(shuō)明 |
|
ROW_NUMBER |
為表中的每一行分配一個(gè)序號(hào)，可以指定分組（也可以不指定）及排序字段(連續(xù)且不重復(fù)) |
|
DENSE_RANK |
根據(jù)排序字段為每個(gè)分組中的每一行分配一個(gè)序號(hào)。排名值相同時(shí)，序號(hào)相同，序號(hào)中沒(méi)有間隙（1,1,1,2,3這種） |
|
RANK |
根據(jù)排序字段為每個(gè)分組中的每一行分配一個(gè)序號(hào)。排名值相同時(shí)，序號(hào)相同，但序號(hào)中存在間隙（1,1,1,4,5這種） |
我們要獲取各科目排名第一的學(xué)生及得分，就可以再加個(gè)條件判斷即可，需要注意這里用到了子查詢。
SELECT
*
FROM
( SELECT *, DENSE_RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS DENSE_RANK_排名 FROM 成績(jī)單 ) a
WHERE
DENSE_RANK_排名 = 1;
查詢結(jié)果如下：
DENSE_RANK_排名第一
另外還有個(gè)NTILE(n)將分區(qū)中的有序數(shù)據(jù)分為n個(gè)等級(jí)，記錄等級(jí)數(shù)
比如按照學(xué)號(hào)分區(qū)得分排序進(jìn)行分2個(gè)等級(jí)
SELECT
*,
NTILE(2) OVER ( PARTITION BY 學(xué)號(hào) ORDER BY 得分 DESC ) AS NTILE_
FROM
成績(jī)單
查詢結(jié)果如下：
NTILE(2)
NTILE(n)在數(shù)據(jù)分析中應(yīng)用較多，比如由于數(shù)據(jù)量大，需要將數(shù)據(jù)平均分配到n個(gè)并行的進(jìn)程分別計(jì)算，此時(shí)就可以用NTILE(n)對(duì)數(shù)據(jù)進(jìn)行分組(由于記錄數(shù)不一定被n整除，所以數(shù)據(jù)不一定完全平均)，然后將不同桶號(hào)的數(shù)據(jù)再分配。
3. 分布函數(shù)
分布函數(shù)有兩個(gè)PERCENT_RANK()和CUME_DIST()
**PERCENT_RANK()**的用途是每行按照公式(rank-1) / (rows-1)進(jìn)行計(jì)算。其中，rank為RANK()函數(shù)產(chǎn)生的序號(hào)，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
成績(jī)單
查詢結(jié)果如下：
PERCENT_RANK()
CUME_DIST()的用途是分組內(nèi)小于、等于當(dāng)前rank值的行數(shù) / 分組內(nèi)總行數(shù)。
查詢小于等于當(dāng)前成績(jī)的比例
SELECT
*,
RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名 ,
CUME_DIST() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS CUME_DIST_
FROM
成績(jī)單
查詢結(jié)果如下：
CUME_DIST()
可以看到，數(shù)學(xué)科目中有0.5也就是50%的朋友得分120，超過(guò)66.66%的學(xué)生成績(jī)?cè)?18分及以上。
4. 前后函數(shù)
查詢當(dāng)前行指定字段往前后N行數(shù)據(jù)，LAG() 和 LEAD()
前N行LAG(expr[,N[,default]])，比如我們看各科目同學(xué)每個(gè)人往前3名的同學(xué)得分。
ELECT
*,
RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名 ,
LAG(得分, 3) OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS LAG_
FROM
成績(jī)單
查詢結(jié)果如下：
LAG(得分, 3)
可以看到，各科目前三行都是NULL空值，這是因?yàn)榍叭胁淮嬖谒鼈兺?行的值。rank 4的前3是rank 1，對(duì)應(yīng)得分是120。
這個(gè)可以用于進(jìn)行一些諸如環(huán)比的情況，在這里我們可以計(jì)算當(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
成績(jī)單
) 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
成績(jī)單
查詢結(jié)果如下：
FIRST_VALUE(得分)
我們可以計(jì)算各個(gè)同學(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
成績(jī)單
) 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
成績(jī)單
查詢結(jié)果如下：
NTH_VALUE(得分,4)
6. 聚合函數(shù)
在窗口中每條記錄動(dòng)態(tài)地應(yīng)用聚合函數(shù)(SUM()、AVG()、MAX()、MIN()、COUNT())，可以動(dòng)態(tài)計(jì)算在指定的窗口內(nèi)的各種聚合函數(shù)值。
所以，這里我們構(gòu)造一個(gè)帶有時(shí)間字段的數(shù)據(jù)表。
DROP TABLE
IF
EXISTS 語(yǔ)文成績(jī)單;
CREATE TABLE 語(yǔ)文成績(jī)單 ( 學(xué)號(hào) VARCHAR ( 8 ), 姓名 VARCHAR ( 8 ), 時(shí)間 DATE, 得分 INT ) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO 語(yǔ)文成績(jī)單
VALUES
('1000', '小明', '2022-01-02' ,102 ),
('1001', '云朵', '2022-01-04' ,112 ),
('1002', '庫(kù)里', '2022-01-07' ,101 ),
('1003', '才子', '2022-01-07' ,118 ),
('1004', '小華', '2022-01-08' ,112 ),
('1005', '強(qiáng)森', '2022-01-09' ,92 );
這是一張語(yǔ)文成績(jī)表，分別是學(xué)號(hào)、姓名、時(shí)間與得分。
語(yǔ)文成績(jī)表
比如，我們要查詢?cè)诮刂姑總€(gè)時(shí)間語(yǔ)文最高分，可以這樣操作：
SELECT
*,
MAX(得分) OVER ( ORDER BY 時(shí)間 ) AS MAX_
FROM
語(yǔ)文成績(jī)單
查詢結(jié)果如下：
MAX(得分)
以上就是本次的基礎(chǔ)介紹，日常工作的的實(shí)際操作應(yīng)該會(huì)更加復(fù)雜，不過(guò)抽絲剝繭我們總會(huì)發(fā)現(xiàn)復(fù)雜都是由很多基礎(chǔ)拼接而成，打好基礎(chǔ)就可以變得很強(qiáng)!
新聞名稱：SQL數(shù)據(jù)分析實(shí)戰(zhàn)：好用的窗口函數(shù)
文章鏈接：http://m.fisionsoft.com.cn/article/dpijhjp.html
