先看這段像天書一樣的 SQL ,看著就頭疼。
SELECTs1.name,s1.subject,s1.score,sub.avg_score AS average_score_per_subject,(SELECT COUNT(DISTINCT s2.score) + 1 FROM scores s2 WHERE s2.score > s1.score) AS score_rank
FROM scores s1
JOIN (SELECT subject, AVG(score) AS avg_scoreFROM scoresGROUP BY subject
) sub ON s1.subject = sub.subject
ORDER BY s1.score DESC;
這段SQL是干什么用的呢，就是為了計算一個成績排名，簡直大動干戈啊。
那有沒有簡化的方法呢?有的。
簡化后的版本就是利用今天說的窗口函數。
SELECTname,subject,score,AVG(score) OVER (PARTITION BY subject) AS average_score_per_subject,RANK() OVER (ORDER BY score DESC) AS score_rank
FROM scores
ORDER BY score DESC;
是不是看上去就簡潔清晰多了。
下面我們看看是什么樣的功能。
首先創建一個表，包含姓名、學科、分數三個字段，用于后面功能的演示。
CREATE TABLE `scores` (`name` varchar(20) COLLATE utf8_bin NOT NULL,`subject` varchar(20) COLLATE utf8_bin NOT NULL,`score` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
然后向表中插入一些隨機記錄。
INSERT INTO scores (name, subject, score) VALUES ('Student1', '化學', 75);
INSERT INTO scores (name, subject, score) VALUES ('Student2', '生物', 92);
INSERT INTO scores (name, subject, score) VALUES ('Student3', '物理', 87);
INSERT INTO scores (name, subject, score) VALUES ('Student4', '數學', 68);
INSERT INTO scores (name, subject, score) VALUES ('Student5', '英語', 91);
INSERT INTO scores (name, subject, score) VALUES ('Student6', '化學', 58);
INSERT INTO scores (name, subject, score) VALUES ('Student7', '物理', 79);
INSERT INTO scores (name, subject, score) VALUES ('Student8', '數學', 90);
INSERT INTO scores (name, subject, score) VALUES ('Student9', '數學', 45);
##什么是窗口函數
在 MySQL 8.x 版本中，MySQL 提供了窗口函數，窗口函數是一種在查詢結果的特定窗口范圍內進行計算的函數。
很早以前用 Oracle 和 MS SQL 的時候會用到里面的窗口函數，但是用 MySQL 后才發現，MySQL 竟然沒有窗口函數，以至于一些負責的統計查詢都要用各種子查詢、join，層層嵌套，看上去很簡單的需求，結果搞得 SQL 語句寫的是龍飛鳳舞，別人一看跟天書似的。就一個字兒，懵。
窗口函數主要的應用場景是統計和計算，例如對查詢結果進行分組、排序和計算聚合，通過各個函數的組合，可以實現各種復雜的邏輯，而且比起 MySQL 8.0之前用子查詢、join 的方式，性能上要好得多。
OVER()
OVER() 是用于定義窗口函數的子句，它必須結合其他的函數才有意義，比如求和、求平均數。而它只用于指定要計算的數據范圍和排序方式。
function_name(...) OVER ([PARTITION BY expr_list] [ORDER BY expr_list] [range]
)
PARTITION BY
用于指定分區字段,對不同分區進行分析計算，分區其實就列，可以指定一個列，也可以指定多個列。
ORDER BY
用于對分區內記錄進行排序,排序后可以與「范圍和滾動窗口」一起使用。
范圍和滾動窗口
用于指定分析函數的窗口,包括范圍和滾動窗口。
范圍窗口(Range window)
指定窗口的起止行號,使用UNBOUNDED PRECEDING表示起點,UNBOUNDED FOLLOWING表示終點。
例如:
SUM(salary) OVER (ORDER BY id RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING)
這會計算當前行及之前5行和之后5行的salary總和。
滾動窗口(Row window)
使用了基于當前行的滾動窗口
例如:
SUM(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
這會計算當前行及之前2行和之后2行的salary總和。
OVER()可搭配的函數：
聚合函數
MAX(),MIN(),COUNT(),SUM()等，用于生成每個分區的聚合結果。
排序相關
ROW_NUMBER(),RANK(),DENSE_RANK()等，用于生成每個分區的行號或排名。
窗口函數
LAG(),LEAD(),FIRST_VALUE(),LAST_VALUE()等，用于基于窗口框生成結果。
搭配聚合函數
1、按subject列進行分區，并求出某學科的最大最小值
獲取分數和此學科最高分
SELECT subject,score, MAX(score) OVER (PARTITION BY subject) as `此學科最高分` FROM scores;
得出的結果是:
| subject | score | 此學科最高分 |
|---|---|---|
| 化學 | 75 | 75 |
| 化學 | 58 | 75 |
| 數學 | 68 | 90 |
| 數學 | 90 | 90 |
| 數學 | 45 | 90 |
| 物理 | 87 | 87 |
| 物理 | 79 | 87 |
| 生物 | 92 | 92 |
| 英語 | 91 | 91 |
2、獲取學科的報名人數
SELECT subject,score, count(name) OVER (PARTITION BY subject) as `報名此學科人數` FROM scores;
得到的結果為:
| subject | score | 報名此學科人數 |
|---|---|---|
| 化學 | 75 | 2 |
| 化學 | 58 | 2 |
| 數學 | 68 | 3 |
| 數學 | 90 | 3 |
| 數學 | 45 | 3 |
| 物理 | 87 | 2 |
| 物理 | 79 | 2 |
| 生物 | 92 | 1 |
| 英語 | 91 | 1 |
3、求學科的總分
SELECT subject, SUM(score) OVER (PARTITION BY subject) as `此學科總分` FROM scores;
得到的結果:
| subject | 此學科總分 |
|---|---|
| 化學 | 133 |
| 化學 | 133 |
| 數學 | 203 |
| 數學 | 203 |
| 數學 | 203 |
| 物理 | 166 |
| 物理 | 166 |
| 生物 | 92 |
| 英語 | 91 |
4、使用 order by 求累加分數
SELECT name,subject,score, SUM(score) OVER (order BY score) as `累加分數` FROM scores;
得到的結果：
| name | subject | score | 累加分數 |
|---|---|---|---|
| Student9 | 數學 | 45 | 45 |
| Student6 | 化學 | 58 | 103 |
| Student4 | 數學 | 68 | 171 |
我們看這是怎么算出來的，OVER 函數里面是 order by 。
首先根據分數排序（默認升序），得到第一行分數是45，所以累加分數就是它自己，也就是45。
然后排序得到第二行 58，然后將第一行和第二行相加，這樣得到累加分數就是45+58=103。
同理，第三行就是前三行的總和，也就是45+58+68=171。
以此類推，第 N 行就是1~N的累加和。
5、使用 order by + 范圍
前面因為沒有限定范圍，所以就是前 N 行的累加，還可以限定范圍。
SELECT name,subject,score, SUM(score) OVER (order BY `score` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as `累加分數` FROM scores;
這里的累加分數是指當前行+前一行+后一行的和。
獲取的結果為：
| name | subject | score | 累加分數 |
|---|---|---|---|
| Student9 | 數學 | 45 | 103 |
| Student6 | 化學 | 58 | 171 |
| Student4 | 數學 | 68 | 201 |
| Student1 | 化學 | 75 | 222 |
| Student7 | 物理 | 79 | 241 |
| Student3 | 物理 | 87 | 256 |
| Student8 | 數學 | 90 | 268 |
| Student5 | 英語 | 91 | 273 |
第一行 103，是當前行 45+后一行（58）的和，等于103，因為沒有前一行。
第二行171，是當前行58+前一行（45）+后一行（68）的和，等于171。
以此類型，后面的累加分數都是這樣算出來的。
搭配排序相關函數
ROW_NUMBER()
ROW_NUMBER() 函數用于為結果集中的每一行分配一個唯一的排序。
如下，對成績進行排名，分數高的排在前面，如果有兩個人分數相同，那仍然是一個第一，另一個第二。
SELECT name,subject,score, ROW_NUMBER() OVER (order BY `score` desc) as `排名` FROM scores;
查詢結果為：
| name | subject | score | 排名 |
|---|---|---|---|
| Student2 | 生物 | 92 | 1 |
| Student5 | 英語 | 91 | 2 |
| Student8 | 數學 | 90 | 3 |
| Student3 | 物理 | 87 | 4 |
| Student7 | 物理 | 79 | 5 |
如果不用 ROW_NUMBER(),比如在 MySQL 5.7的版本中，就會像下面這樣:
SELECT s1.name, s1.subject, s1.score, COUNT(s2.score) + 1 AS `排名`
FROM scores s1
LEFT JOIN scores s2 ON s1.score < s2.score
GROUP BY s1.name, s1.subject, s1.score
ORDER BY s1.score DESC;
是不是比使用 ROW_NUMBER()復雜的多。
RANK()
RANK() 函數用于為結果集中的每一行分配一個排名值，它也是排名的，但是它和 ROW_NUMBER()有，RANK()函數在遇到相同值的行會將排名設置為相同的，就像是并列排名。
就像是奧運比賽，如果有兩個人都是相同的高分，那可能就是并列金牌，但是這時候就沒有銀牌了，僅次于這兩個人的排名就會變成銅牌。
SELECT name,subject,score, RANK() OVER (order BY `score` desc) as `排名` FROM scores;
查詢結果為：
| name | subject | score | 排名 |
|---|---|---|---|
| Student1 | 化學 | 92 | 1 |
| Student2 | 生物 | 92 | 1 |
| Student5 | 英語 | 91 | 3 |
| Student8 | 數學 | 90 | 4 |
| Student3 | 物理 | 87 | 5 |
DENSE_RANK()
DENSE_RANK() 也是用作排名的，和 RANK()函數的差別就是遇到相同值的時候，不會跳過排名，比如兩個人是并列金牌，排名都是1，那僅次于這兩個人的排名就是2，而不像 RANK()那樣是3。
SELECT name,subject,score, DENSE_RANK() OVER (order BY `score` desc) as `排名` FROM scores;
查詢結果為：
| name | subject | score | 排名 |
|---|---|---|---|
| Student1 | 化學 | 92 | 1 |
| Student2 | 生物 | 92 | 1 |
| Student5 | 英語 | 91 | 2 |
| Student8 | 數學 | 90 | 3 |
配合其他窗口函數
NTILE()
NTILE() 函數用于將結果集劃分為指定數量的組，并為每個組分配一個編號。例如，將分數倒序排序并分成4個組，相當于有了4個梯隊。
SELECT name,subject,score, NTILE(4) OVER (order BY `score` desc) as `組` FROM scores;
查詢結果為：
| name | subject | score | 組 |
|---|---|---|---|
| Student1 | 化學 | 92 | 1 |
| Student2 | 生物 | 92 | 1 |
| Student5 | 英語 | 91 | 1 |
| Student8 | 數學 | 90 | 2 |
| Student3 | 物理 | 87 | 2 |
| Student7 | 物理 | 79 | 3 |
| Student4 | 數學 | 68 | 3 |
| Student6 | 化學 | 58 | 4 |
| Student9 | 數學 | 45 | 4 |
LAG()
LAG() 函數用于在查詢結果中訪問當前行之前的行的數據。它允許您檢索前一行的值，并將其與當前行的值進行比較或計算差異。LAG()函數對于處理時間序列數據或比較相鄰行的值非常有用。
LAG()函數完整的表達式為 LAG(column, offset, default_value)，包含三個參數：
column：就是列名，獲取哪個列的值就是哪個列名，很好理解。
offset: 就是向前的偏移量，取當前行的前一行就是1，前前兩行就是2。
default_value：是可選值，如果向前偏移的行不存在，就取這個默認值。
例如比較相鄰兩個排名的分數差，可以這樣寫：
SELECTname,subject,score,ABS(score - LAG(score, 1,score) OVER (ORDER BY score DESC)) AS `分值差`
FROMscores;
得到的結果為：
| name | subject | score | 分值差 |
|---|---|---|---|
| Student1 | 化學 | 92 | 0 |
| Student2 | 生物 | 92 | 0 |
| Student5 | 英語 | 91 | 1 |
| Student8 | 數學 | 90 | 1 |
| Student3 | 物理 | 87 | 3 |
| Student7 | 物理 | 79 | 8 |
| Student4 | 數學 | 68 | 11 |
LEAD()
LEAD() 函數和 LAG()的功能一致，只不過它的偏移量是向后偏移，也就是取當前行的后 N 行。
所以前面的比較相鄰兩行差值的邏輯，也可以向后比較。
SELECTname,subject,score,score - LEAD(score, 1,score) OVER (ORDER BY score DESC) AS `分值差`
FROMscores;
得到的結果：
| name | subject | score | 分值差 |
|---|---|---|---|
| Student1 | 化學 | 92 | 0 |
| Student2 | 生物 | 92 | 1 |
| Student5 | 英語 | 91 | 1 |
| Student8 | 數學 | 90 | 3 |
| Student3 | 物理 | 87 | 8 |
| Student7 | 物理 | 79 | 11 |
| Student4 | 數學 | 68 | 10 |
)
)
通配符 失效怎么解決)
)
