當(dāng)查詢(xún)條件需要用到復(fù)雜子查詢(xún)時(shí)，聚合函數(shù)操作起來(lái)非常麻煩，因此使用開(kāi)窗函數(shù)能夠輕松實(shí)現(xiàn)。
注意：在Oracle中稱(chēng)為分析函數(shù)。
? ? ? ? ? ?在MySQL中稱(chēng)為開(kāi)窗函數(shù)，使用于MySQL8.0以上版本，sql sever、hive、Oracle等。
1 開(kāi)窗函數(shù)
開(kāi)窗函數(shù)：為將要被操作的行的集合定義一個(gè)窗口，它對(duì)一組值進(jìn)行操作，不需要使用GROUP BY子句對(duì)數(shù)據(jù)進(jìn)行分組，能夠在同一行中同時(shí)返回基礎(chǔ)行的列和聚合列。
語(yǔ)法：函數(shù) + over(partition by <分組用列> order by <排序用列>)over() 按所有行進(jìn)行分組
over(partition by xxx)按xxx分組的所有行進(jìn)行分組
over(partition by xxx order by aaa)按列xxx分組，按列aaa排序
over(order by aaa) 按aaa列排序
括號(hào)中的兩個(gè)關(guān)鍵詞partition by 和order by可以只出現(xiàn)一個(gè)。
開(kāi)窗函數(shù)表示對(duì)數(shù)據(jù)集按照分組用列進(jìn)行分區(qū)，并且對(duì)每個(gè)分區(qū)按照函數(shù)聚合計(jì)算，最終將計(jì)算結(jié)果按照排序用列排序后返回到該行。
無(wú)論何種能力，窗口函數(shù)都不會(huì)影響數(shù)據(jù)行數(shù)，而是將計(jì)算平攤在每一行。
1.1 開(kāi)窗函數(shù)分類(lèi)
| 名稱(chēng) | 描述 |
| cume_dist() | 計(jì)算一組值中一個(gè)值的累積分布 |
| dense_rank() | 根據(jù)該order by子句為分區(qū)中的每一行分配一個(gè)等級(jí)。它將相同的等級(jí)分配給具有相等值的行。如果兩行或更多行具有相同的排名，則排名值序列中將沒(méi)有間隙 |
| first_value() | 返回相對(duì)于窗口框架第一行的指定表達(dá)式的值 |
| lag() | 返回分區(qū)中當(dāng)前行之前的第N行的值。如果不存在前一行，則返回NULL |
| last_value() | 返回相對(duì)于窗口框架中最后一行的指定表達(dá)式的值 |
| lead() | 返回分區(qū)中當(dāng)前行之后的第N行的值。如果不存在后續(xù)行，則返回NULL |
| nth_value() | 從窗口框架的第N行返回參數(shù)的值 |
| ntile() | 將每個(gè)窗口分區(qū)的行分配到指定數(shù)量的排名組中 |
| percent_rank() | 計(jì)算分區(qū)域結(jié)果集中行的百分?jǐn)?shù)等級(jí) |
| rank() | 與dense_rank()函數(shù)相似，不同之處在于當(dāng)兩行或更多行具有相同的等級(jí)時(shí)，等級(jí)值序列中存在間隙 |
| row_number() | 為分區(qū)中的每一行分配一個(gè)順序整數(shù) |
按照函數(shù)功能不同，MySQL支出的開(kāi)窗函數(shù)分為如下幾類(lèi)：
- 序號(hào)函數(shù)：row_number() / rank() / dense_rank()
- 分布函數(shù)：percent_rank() / cume_dist()
- 前后函數(shù)：lag() / lead()
- 頭尾函數(shù)：first_value() / last_value()
- 其他函數(shù) ：nth_value() / nfile()
2 排序開(kāi)窗函數(shù)和聚合開(kāi)窗函數(shù)
2.1 排序開(kāi)窗函數(shù)
- row_number（行號(hào)）
- rank（排名）
- dense_rank（密集排名）
- ntile（分組排名）
例1 先對(duì)所有數(shù)據(jù)進(jìn)行排序
select s.sid,s1.sname,s1.gender,c.cname,s.num, row_number() over
(partition by c.cname order by num desc) as row_number排名,
rank() over (partition by c.cname order by num desc) as rank排名,
dense_rank() over (partition by c.cname order by num desc) as dense_rank排名,
ntile(6) over (partition by c.cname order by num desc) as ntile排名
from score s join student s1 on s.student_id = s1.sid
left join course c on s.course_id = c.cid結(jié)果如下：
?row_number：
根據(jù)課程進(jìn)行分組，然后對(duì)每組內(nèi)的成績(jī)進(jìn)行降序排序
又上圖可知row_number對(duì)于同組內(nèi)的相同成績(jī)并沒(méi)有做特殊處理，而僅僅是生成連續(xù)的序號(hào)。row_number常用于按照某列生成連續(xù)序號(hào)。
rank
rank函數(shù)就是對(duì)查詢(xún)出來(lái)的記錄進(jìn)行排名。
與row_number函數(shù)不同的是，rank函數(shù)考慮到了over子句中排序字段值相同的情況，over子句中排序字段值相同的序號(hào)是一樣的，后面字段值不相同的序號(hào)將跳過(guò)相同的排名號(hào)排下一個(gè)，也就是相關(guān)行之前的排名數(shù)加一，通過(guò)上面的例子我們也可以看出，rank考慮到值相同情況，并且它的排名存在跳躍性。
dense_rank
密集排名，在考慮了值相同時(shí)，排名也相同，但是序號(hào)不跳躍，緊跟上一個(gè)序號(hào)。
例如題目中體育成績(jī)有2位同學(xué)(張三和劉三)并列第一，如果使用rank排名 ，那鋼蛋就是第三名，而如果采用dense_rank 那鋼蛋就是第二名，這個(gè)很容易理解吧。
ntile
ntile會(huì)先根據(jù)你的分組依據(jù)，本題中是課程名稱(chēng)，然后把每個(gè)組的總記錄數(shù)進(jìn)行按照你給的ntile()里的數(shù)字進(jìn)行，這個(gè)數(shù)字就是桶數(shù)，相當(dāng)于是把體育課程總共12條記錄，盡量等劃分成5桶，然后按照num的排序等級(jí)劃分，每個(gè)桶兩條記錄，也就是112233445566的排序結(jié)果了，很顯然，這個(gè)排序結(jié)果的數(shù)字大小只能用于桶與桶之間，而桶內(nèi)部記錄雖然序號(hào)相同，但是num不一定相同。
例如：統(tǒng)計(jì)各科成績(jī)前三?
select * from
(select s.sid,s1.sname,s1.gender,c.cname,s.num,dense_rank()
over (partition by c.cname order by num desc) as dense_rank排名 from score s
join student s1 on s.student_id = s1.sid
left join course c on s.course_id = c.cid) as e
where dense_rank排名 <= 3;2.2 聚合開(kāi)窗函數(shù)
函數(shù)名如果是聚合函數(shù)，則稱(chēng)為聚合開(kāi)窗函數(shù)
語(yǔ)法：聚合函數(shù)（列） over(partition by 列 order by 列)常見(jiàn)的聚合函數(shù)有：sum()、count()、average()、max()、min()
計(jì)算每個(gè)學(xué)生的及格科目數(shù)
select student_id,count(sid) from score where num>= 60 group by student_id;
通過(guò)普通的聚合函數(shù)分組計(jì)算后，數(shù)據(jù)表結(jié)構(gòu)發(fā)生了變化，它會(huì)根據(jù)分組進(jìn)行顯示，并且，如果你是根據(jù)學(xué)生ID分組，那你查詢(xún)的字段應(yīng)該也是學(xué)生ID，不然會(huì)影響到分組結(jié)果所對(duì)應(yīng)的數(shù)值，例如現(xiàn)在查詢(xún)條件在添加一個(gè)Sid
select sid,student_id,count(sid) from score where num>= 60 group by student_id;?
sid的數(shù)據(jù)并沒(méi)有實(shí)際意義，因?yàn)閿?shù)據(jù)表已經(jīng)根據(jù)分組發(fā)生了變化。
執(zhí)行結(jié)果
select sid,student_id,count(sid) over(PARTITION by student_id order by student_id) 及格數(shù) from score where num>= 60;
?
?總結(jié)：開(kāi)窗函數(shù)不會(huì)修改源數(shù)據(jù)表的結(jié)果，也是在表的最后一列添加想要的結(jié)果。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-416864.html
實(shí)例數(shù)據(jù)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-416864.html
CREATE TABLE class ( cid int(11) NOT NULL AUTO_INCREMENT, caption varchar(32) NOT NULL, PRIMARY KEY (cid) ) ENGINE=InnoDB CHARSET=utf8; INSERT INTO class VALUES (1, '三年二班'), (2, '三年三班'), (3, '一年二班'), (4, '二年九班'); CREATE TABLE teacher( tid int(11) NOT NULL AUTO_INCREMENT, tname varchar(32) NOT NULL, PRIMARY KEY (tid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO teacher VALUES (1, '張磊老師'), (2, '李平老師'), (3, '劉海燕老師'), (4, '朱云海老師'), (5, '李杰老師'); CREATE TABLE course( cid int(11) NOT NULL AUTO_INCREMENT, cname varchar(32) NOT NULL, teacher_id int(11) NOT NULL, PRIMARY KEY (cid), KEY fk_course_teacher (teacher_id), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO course VALUES (1, '生物', 1), (2, '物理', 2), (3, '體育', 3), (4, '美術(shù)', 2); CREATE TABLE student( sid int(11) NOT NULL AUTO_INCREMENT, gender char(1) NOT NULL, class_id int(11) NOT NULL, sname varchar(32) NOT NULL, PRIMARY KEY (sid), KEY fk_class (class_id), CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO student VALUES (1, '男', 1, '理解'), (2, '女', 1, '鋼蛋'), (3, '男', 1, '張三'), (4, '男', 1, '張一'), (5, '女', 1, '張二'), (6, '男', 1, '張四'), (7, '女', 2, '鐵錘'), (8, '男', 2, '李三'), (9, '男', 2, '李一'), (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), (13, '男', 3, '劉三'), (14, '男', 3, '劉一'), (15, '女', 3, '劉二'), (16, '男', 3, '劉四'); CREATE TABLE score ( sid int(11) NOT NULL AUTO_INCREMENT, student_id int(11) NOT NULL, course_id int(11) NOT NULL, num int(11) NOT NULL, PRIMARY KEY (sid), KEY fk_score_student (student_id), KEY fk_score_course (course_id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO score VALUES (1, 1, 1, 10), (2, 1, 2, 9), (5, 1, 4, 66), (6, 2, 1, 8), (8, 2, 3, 68), (9, 2, 4, 99), (10, 3, 1, 77), (11, 3, 2, 66), (12, 3, 3, 87), (13, 3, 4, 99), (14, 4, 1, 79), (15, 4, 2, 11), (16, 4, 3, 67), (17, 4, 4, 100), (18, 5, 1, 79), (19, 5, 2, 11), (20, 5, 3, 67), (21, 5, 4, 100), (22, 6, 1, 9), (23, 6, 2, 100), (24, 6, 3, 67), (25, 6, 4, 100), (26, 7, 1, 9), (27, 7, 2, 100), (28, 7, 3, 67), (29, 7, 4, 88), (30, 8, 1, 9), (31, 8, 2, 100), (32, 8, 3, 67), (33, 8, 4, 88), (34, 9, 1, 91), (35, 9, 2, 88), (36, 9, 3, 67), (37, 9, 4, 22), (38, 10, 1, 90), (39, 10, 2, 77), (40, 10, 3, 43), (41, 10, 4, 87), (42, 11, 1, 90), (43, 11, 2, 77), (44, 11, 3, 43), (45, 11, 4, 87), (46, 12, 1, 90), (47, 12, 2, 77), (48, 12, 3, 43), (49, 12, 4, 87), (52, 13, 3, 87);
到了這里，關(guān)于MySQL：開(kāi)窗函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
