MySQL窗口函數(shù)OVER使用示例詳細講解
窗口函數(shù)
OVER （PARTITION BY xxx ORDER BY xxx ASC/DESC）
測試數(shù)據(jù)表及數(shù)據(jù)
測試表 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;插入測試數(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ū)，每個分區(qū)形成一個窗口，聚合等計算都在這個分區(qū)內(nèi)完成；
order by 是排序，排完序的數(shù)據(jù)組成不同的窗口，不同值的數(shù)據(jù)組成不同的窗口；
空窗口
當窗口中為空時，就是對表中所有數(shù)據(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
當窗口中只有 order by 時候，對全表數(shù)據(jù)進行排序，其作用和 FROM 后面的 ORDER BY 一樣，
1）當與 FROM 后面的 ORDER BY 字段相同時，相當于只有 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）當與 FROM 后面的 ORDER BY 字段不同時，F(xiàn)ROM 子句的 ORDER BY 會覆蓋 OVER() 中的 ORDER BY，F(xiàn)ROM 子句中 ORDER BY 后值相同的才會按照 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ù)會按照分組進行計算，分組內(nèi)的所有行的數(shù)據(jù)都是這個分組中所有數(shù)據(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|
同時有 PARTITION BY 與 ORDER BY
ORDER BY 對 PARTITION BY 窗口中的數(shù)據(jù)進行排序，當 PARTITION BY 與 ORDER BY 列名不同時，聚合函數(shù)是根據(jù)排序進行逐個聚合計算的，當碰到 ORDER BY 相同的兩個值時，同時計算兩個值，并兩行數(shù)據(jù)一致；當 PARTITION BY 與 ORDER BY 的列一致時，相當于只有 PARTITION BY；FROM 后面的 ORDER BY 是對整個表的數(shù)據(jù)進行排序，與 OVER 子句中的不同；當兩者的字段不同時，先按照 OVER() 子句進行聚合計算，然后按照 FROM 子句的進行排序輸出；
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 字段一致時，相當于只有 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|
到此這篇關于MySQL窗口函數(shù)OVER使用示例詳細講解的文章就介紹到這了,更多相關MySQL窗口函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
mysql安裝報錯unknown variable mysqlx_port=0.0
本文主要介紹了mysql安裝報錯unknown variable mysqlx_port=0.0,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-06-06
數(shù)據(jù)庫邏輯備份就是備份軟件按照我們最初所設計的邏輯關系，以數(shù)據(jù)庫的邏輯結構對象為單位，將數(shù)據(jù)庫中的數(shù)據(jù)按照預定義的邏輯關聯(lián)格式一條一條生成相關的文本文件，以達到備份的目的。本文將具體介紹MySQL 邏輯備份的相關概念及如何做恢復測試。2021-05-05
MySql使用skip-name-resolve解決外網(wǎng)鏈接客戶端過慢問題
在騰訊云上面搭建的mysql使用開發(fā)的電腦上navicat進行訪問時總是特別的慢，原來是Mysql會對請求的地址進行域名解析，開發(fā)的電腦并沒有域名，所以會導致特別的慢，下面通過本文給大家分享MySql使用skip-name-resolve解決外網(wǎng)鏈接客戶端過慢問題2017-07-07
當面試官問mysql中char與varchar的區(qū)別
這篇文章主要以聊天形式圖片的添加，將面試官面試真實場景體現(xiàn)出來，好奇的朋友不要錯過奧2021-08-08
這篇文章主要介紹了MySQL之JSON類型字段的使用技巧，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
Mysql數(shù)據(jù)庫性能優(yōu)化之子查詢
這篇文章主要介紹了Mysql數(shù)據(jù)庫性能優(yōu)化之子查詢的相關資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-01-01
數(shù)據(jù)庫的Id自增越來越大，要讓自增重新從1開始：那么就用下面的方法吧2012-11-11
刪除表不是特別常用,特別是對于存在外鍵關聯(lián)的表,刪除更得小心,這篇文章主要給大家介紹了關于MySQL刪除表的外鍵約束的相關資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2024-07-07
這篇文章主要給大家介紹了關于MySQL的時間進位問題的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-12-12
約束是作用于表中字段上的規(guī)則，用于限制存儲在表中的數(shù)據(jù)。這篇文章主要為大家整理了一下MySQL中六種約束的用法，感興趣的可以了解一下2022-07-07