MySQL窗口函數(shù)OVER使用示例詳細(xì)講解
窗口函數(shù)
OVER （PARTITION BY xxx ORDER BY xxx ASC/DESC）
測(cè)試數(shù)據(jù)表及數(shù)據(jù)
測(cè)試表 employee
CREATE TABLE employee (
`id` int unsigned not null auto_increment primary key,
`name` varchar(80),
`age` int(11),
`salary` DECIMAL(18,1),
`dept_id` int(11)
) ENGINE=InnoDB default charset=utf8mb4;插入測(cè)試數(shù)據(jù)
INSERT into employee values(3, '小肖', 29, 30000.0, 1); INSERT into employee values(4, '小東', 30, 40000.0, 2); INSERT into employee values(6, '小非', 24, 23456.0, 3); INSERT into employee values(7, '曉飛', 30, 15000.0, 4); INSERT into employee values(8, '小林', 23, 24000.0, null); INSERT into employee values(10, '小五', 20, 4500.0, null); INSERT into employee values(11, '張山', 24, 40000.0, 1); INSERT into employee values(12, '小肖', 28, 35000.0, 2); INSERT into employee values(13, '李四', 23, 50000.0, 1); INSERT into employee values(17, '王武', 24, 56000.0, 2); INSERT into employee values(18, '豬小屁', 2, 56000.0, 2); INSERT into employee values(19, '小玉', 25, 58000.0, 1); INSERT into employee values(21, '小張', 23, 50000.0, 1); INSERT into employee values(22, '小胡', 25, 25000.0, 2); INSERT into employee values(96, '小肖', 19, 35000.0, 1); INSERT into employee values(97, '小林', 20, 20000.0, 2);
窗口函數(shù)
partition by 是分區(qū)，每個(gè)分區(qū)形成一個(gè)窗口，聚合等計(jì)算都在這個(gè)分區(qū)內(nèi)完成；
order by 是排序，排完序的數(shù)據(jù)組成不同的窗口，不同值的數(shù)據(jù)組成不同的窗口；
空窗口
當(dāng)窗口中為空時(shí)，就是對(duì)表中所有數(shù)據(jù)進(jìn)行計(jì)算
mysql> select name,salary,SUM(salary) over() AS already_paid_salary FROM employee e ;
name|salary |already_paid_salary| ----+-------+-------------------+ 小肖 |30000.0| 561956.0| 小東 |40000.0| 561956.0| 小非 |23456.0| 561956.0| 曉飛 |15000.0| 561956.0| 小林 |24000.0| 561956.0| 小五 | 4500.0| 561956.0| 張山 |40000.0| 561956.0| 小肖 |35000.0| 561956.0| 李四 |50000.0| 561956.0| 王武 |56000.0| 561956.0| 豬小屁 |56000.0| 561956.0| 小玉 |58000.0| 561956.0| 小張 |50000.0| 561956.0| 小胡 |25000.0| 561956.0| 小肖 |35000.0| 561956.0| 小林 |20000.0| 561956.0|
窗口中只有 ORDER BY
當(dāng)窗口中只有 order by 時(shí)候，對(duì)全表數(shù)據(jù)進(jìn)行排序，其作用和 FROM 后面的 ORDER BY 一樣，
1）當(dāng)與 FROM 后面的 ORDER BY 字段相同時(shí)，相當(dāng)于只有 OVER(ORDER BY xxx)
mysql> select name,salary,SUM(salary) over(ORDER BY salary) AS already_paid_salary FROM employee e ;
name|salary |already_paid_salary| ----+-------+-------------------+ 小五 | 4500.0| 4500.0| 曉飛 |15000.0| 19500.0| 小林 |20000.0| 39500.0| 小非 |23456.0| 62956.0| 小林 |24000.0| 86956.0| 小胡 |25000.0| 111956.0| 小肖 |30000.0| 141956.0| 小肖 |35000.0| 211956.0| 小肖 |35000.0| 211956.0| 小東 |40000.0| 291956.0| 張山 |40000.0| 291956.0| 李四 |50000.0| 391956.0| 小張 |50000.0| 391956.0| 王武 |56000.0| 503956.0| 豬小屁 |56000.0| 503956.0| 小玉 |58000.0| 561956.0|
2）當(dāng)與 FROM 后面的 ORDER BY 字段不同時(shí)，F(xiàn)ROM 子句的 ORDER BY 會(huì)覆蓋 OVER() 中的 ORDER BY，F(xiàn)ROM 子句中 ORDER BY 后值相同的才會(huì)按照 OVER() 子句中的 ORDER BY 排序；
mysql> select id,name,salary,SUM(salary) over(ORDER BY salary) AS already_paid_salary FROM employee e ORDER BY name;
id|name|salary |already_paid_salary| --+----+-------+-------------------+ 4|小東 |40000.0| 291956.0| 10|小五 | 4500.0| 4500.0| 21|小張 |50000.0| 391956.0| 97|小林 |20000.0| 39500.0| 8|小林 |24000.0| 86956.0| 19|小玉 |58000.0| 561956.0| 3|小肖 |30000.0| 141956.0| 12|小肖 |35000.0| 211956.0| 96|小肖 |35000.0| 211956.0| 22|小胡 |25000.0| 111956.0| 6|小非 |23456.0| 62956.0| 11|張山 |40000.0| 291956.0| 7|曉飛 |15000.0| 19500.0| 13|李四 |50000.0| 391956.0| 18|豬小屁 |56000.0| 503956.0| 17|王武 |56000.0| 503956.0|
窗口中只有 PARTITION BY 時(shí)
此時(shí)的聚合函數(shù)會(huì)按照分組進(jìn)行計(jì)算，分組內(nèi)的所有行的數(shù)據(jù)都是這個(gè)分組中所有數(shù)據(jù)計(jì)算后的值；
mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id) AS already_paid_salary FROM employee;
id|name|salary |dept_id|already_paid_salary| --+----+-------+-------+-------------------+ 8|小林 |24000.0| | 28500.0| 10|小五 | 4500.0| | 28500.0| 3|小肖 |30000.0| 1| 263000.0| 11|張山 |40000.0| 1| 263000.0| 13|李四 |50000.0| 1| 263000.0| 19|小玉 |58000.0| 1| 263000.0| 21|小張 |50000.0| 1| 263000.0| 96|小肖 |35000.0| 1| 263000.0| 4|小東 |40000.0| 2| 232000.0| 12|小肖 |35000.0| 2| 232000.0| 17|王武 |56000.0| 2| 232000.0| 18|豬小屁 |56000.0| 2| 232000.0| 22|小胡 |25000.0| 2| 232000.0| 97|小林 |20000.0| 2| 232000.0| 6|小非 |23456.0| 3| 23456.0| 7|曉飛 |15000.0| 4| 15000.0|
同時(shí)有 PARTITION BY 與 ORDER BY
ORDER BY 對(duì) PARTITION BY 窗口中的數(shù)據(jù)進(jìn)行排序，當(dāng) PARTITION BY 與 ORDER BY 列名不同時(shí)，聚合函數(shù)是根據(jù)排序進(jìn)行逐個(gè)聚合計(jì)算的，當(dāng)碰到 ORDER BY 相同的兩個(gè)值時(shí)，同時(shí)計(jì)算兩個(gè)值，并兩行數(shù)據(jù)一致；當(dāng) PARTITION BY 與 ORDER BY 的列一致時(shí)，相當(dāng)于只有 PARTITION BY；FROM 后面的 ORDER BY 是對(duì)整個(gè)表的數(shù)據(jù)進(jìn)行排序，與 OVER 子句中的不同；當(dāng)兩者的字段不同時(shí)，先按照 OVER() 子句進(jìn)行聚合計(jì)算，然后按照 FROM 子句的進(jìn)行排序輸出；
mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id ORDER BY name) AS already_paid_salary FROM employee e ;
id|name|salary |dept_id|already_paid_salary| --+----+-------+-------+-------------------+ 10|小五 | 4500.0| | 4500.0| 8|小林 |24000.0| | 28500.0| 21|小張 |50000.0| 1| 50000.0| 19|小玉 |58000.0| 1| 108000.0| 3|小肖 |30000.0| 1| 173000.0| 96|小肖 |35000.0| 1| 173000.0| 11|張山 |40000.0| 1| 213000.0| 13|李四 |50000.0| 1| 263000.0| 4|小東 |40000.0| 2| 40000.0| 97|小林 |20000.0| 2| 60000.0| 12|小肖 |35000.0| 2| 95000.0| 22|小胡 |25000.0| 2| 120000.0| 18|豬小屁 |56000.0| 2| 176000.0 17|王武 |56000.0| 2| 232000.0| 6|小非 |23456.0| 3| 23456.0| 7|曉飛 |15000.0| 4| 15000.0|
添加 FROM 子句的 ORDER BY
mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id ORDER BY name) AS already_paid_salary FROM employee e ORDER BY name;
id|name|salary |dept_id|already_paid_salary| --+----+-------+-------+-------------------+ 4|小東 |40000.0| 2| 40000.0| 10|小五 | 4500.0| | 4500.0| 21|小張 |50000.0| 1| 50000.0| 8|小林 |24000.0| | 28500.0| 97|小林 |20000.0| 2| 60000.0| 19|小玉 |58000.0| 1| 108000.0| 3|小肖 |30000.0| 1| 173000.0| 96|小肖 |35000.0| 1| 173000.0| 12|小肖 |35000.0| 2| 95000.0| 22|小胡 |25000.0| 2| 120000.0| 6|小非 |23456.0| 3| 23456.0| 11|張山 |40000.0| 1| 213000.0| 7|曉飛 |15000.0| 4| 15000.0| 13|李四 |50000.0| 1| 263000.0| 18|豬小屁 |56000.0| 2| 176000.0| 17|王武 |56000.0| 2| 232000.0|
PARTITION BY 與 ORDER BY 字段一致時(shí)，相當(dāng)于只有 PARTITION BY：
mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id ORDER BY dept_id) AS already_paid_salary FROM employee;
id|name|salary |dept_id|already_paid_salary| --+----+-------+-------+-------------------+ 8|小林 |24000.0| | 28500.0| 10|小五 | 4500.0| | 28500.0| 3|小肖 |30000.0| 1| 263000.0| 11|張山 |40000.0| 1| 263000.0| 13|李四 |50000.0| 1| 263000.0| 19|小玉 |58000.0| 1| 263000.0| 21|小張 |50000.0| 1| 263000.0| 96|小肖 |35000.0| 1| 263000.0| 4|小東 |40000.0| 2| 232000.0| 12|小肖 |35000.0| 2| 232000.0| 17|王武 |56000.0| 2| 232000.0| 18|豬小屁 |56000.0| 2| 232000.0 22|小胡 |25000.0| 2| 232000.0| 97|小林 |20000.0| 2| 232000.0| 6|小非 |23456.0| 3| 23456.0| 7|曉飛 |15000.0| 4| 15000.0|
到此這篇關(guān)于MySQL窗口函數(shù)OVER使用示例詳細(xì)講解的文章就介紹到這了,更多相關(guān)MySQL窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MYSQL數(shù)據(jù)庫(kù)GTID實(shí)現(xiàn)主從復(fù)制實(shí)現(xiàn)(超級(jí)方便)
這篇文章主要介紹了MYSQL數(shù)據(jù)庫(kù)GTID實(shí)現(xiàn)主從復(fù)制實(shí)現(xiàn)(超級(jí)方便)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
MySQL?中MATCH?全文搜索關(guān)鍵字示例詳解
這篇文章主要介紹了MySQL?中MATCH?全文搜索關(guān)鍵字詳解,本文結(jié)合示例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧2023-09-09
windows 10 下mysql-8.0.17-winx64的安裝方法圖解
這篇文章主要介紹了windows 10 mysql-8.0.17-winx64的方法，本文圖文并茂給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-08-08
MySql狀態(tài)查看方法 MySql如何查看連接數(shù)和狀態(tài)?
如果是root帳號(hào)，你能看到所有用戶的當(dāng)前連接。如果是其它普通帳號(hào)，只能看到自己占用的連接2012-11-11
這篇文章主要介紹了MySQL修改root密碼的4種方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
這篇文章主要介紹了MySQL如何刪除表中的數(shù)據(jù)問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-11-11
這篇文章主要為大家詳細(xì)介紹了MySQL?8.0.29?安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-07-07
文詳細(xì)介紹了MySQL數(shù)據(jù)庫(kù)中的四種關(guān)鍵鍵類型：主鍵、超鍵、候選鍵和外鍵,并探討了它們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)和管理中的作用,感興趣的可以了解一下2024-09-09
MySQL存儲(chǔ)過(guò)程例子(包含事務(wù)，輸出參數(shù)，嵌套調(diào)用)
MySQL存儲(chǔ)過(guò)程例子，包含事務(wù)，輸出參數(shù)，嵌套調(diào)用,學(xué)習(xí)mysql存儲(chǔ)過(guò)程的朋友可以參考下。2010-09-09
MySQL中VARCHAR與CHAR格式數(shù)據(jù)的區(qū)別
char是一種固定長(zhǎng)度的類型，varchar則是一種可變長(zhǎng)度的類型，那么他們具體使用過(guò)程中有什么區(qū)別嗎2015-09-09