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安裝報(bào)錯(cuò)unknown variable mysqlx_port=0.0
本文主要介紹了mysql安裝報(bào)錯(cuò)unknown variable mysqlx_port=0.0,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2024-06-06
MySQL 邏輯備份與恢復(fù)測(cè)試的相關(guān)總結(jié)
數(shù)據(jù)庫(kù)邏輯備份就是備份軟件按照我們最初所設(shè)計(jì)的邏輯關(guān)系，以數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)對(duì)象為單位，將數(shù)據(jù)庫(kù)中的數(shù)據(jù)按照預(yù)定義的邏輯關(guān)聯(lián)格式一條一條生成相關(guān)的文本文件，以達(dá)到備份的目的。本文將具體介紹MySQL 邏輯備份的相關(guān)概念及如何做恢復(fù)測(cè)試。2021-05-05
MySql使用skip-name-resolve解決外網(wǎng)鏈接客戶端過(guò)慢問(wèn)題
在騰訊云上面搭建的mysql使用開(kāi)發(fā)的電腦上navicat進(jìn)行訪問(wèn)時(shí)總是特別的慢，原來(lái)是Mysql會(huì)對(duì)請(qǐng)求的地址進(jìn)行域名解析，開(kāi)發(fā)的電腦并沒(méi)有域名，所以會(huì)導(dǎo)致特別的慢，下面通過(guò)本文給大家分享MySql使用skip-name-resolve解決外網(wǎng)鏈接客戶端過(guò)慢問(wèn)題2017-07-07
當(dāng)面試官問(wèn)mysql中char與varchar的區(qū)別
這篇文章主要以聊天形式圖片的添加，將面試官面試真實(shí)場(chǎng)景體現(xiàn)出來(lái)，好奇的朋友不要錯(cuò)過(guò)奧2021-08-08
這篇文章主要介紹了MySQL之JSON類型字段的使用技巧，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
Mysql數(shù)據(jù)庫(kù)性能優(yōu)化之子查詢
這篇文章主要介紹了Mysql數(shù)據(jù)庫(kù)性能優(yōu)化之子查詢的相關(guān)資料,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2017-01-01
數(shù)據(jù)庫(kù)的Id自增越來(lái)越大，要讓自增重新從1開(kāi)始：那么就用下面的方法吧2012-11-11
刪除表不是特別常用,特別是對(duì)于存在外鍵關(guān)聯(lián)的表,刪除更得小心,這篇文章主要給大家介紹了關(guān)于MySQL刪除表的外鍵約束的相關(guān)資料,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-07-07
關(guān)于MySQL的時(shí)間進(jìn)位問(wèn)題淺析
這篇文章主要給大家介紹了關(guān)于MySQL的時(shí)間進(jìn)位問(wèn)題的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
約束是作用于表中字段上的規(guī)則，用于限制存儲(chǔ)在表中的數(shù)據(jù)。這篇文章主要為大家整理了一下MySQL中六種約束的用法，感興趣的可以了解一下2022-07-07