MySQL窗口函数OVER使用示例详细讲解
开发老张 人气:0窗口函数
OVER (PARTITION BY xxx ORDER BY xxx ASC/DESC)
测试数据表及数据
测试表 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;
插入测试数据
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);
窗口函数
partition by 是分区,每个分区形成一个窗口,聚合等计算都在这个分区内完成;
order by 是排序,排完序的数据组成不同的窗口,不同值的数据组成不同的窗口;
空窗口
当窗口中为空时,就是对表中所有数据进行计算
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 时候,对全表数据进行排序,其作用和 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 字段不同时,FROM 子句的 ORDER BY 会覆盖 OVER() 中的 ORDER BY,FROM 子句中 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 时
此时的聚合函数会按照分组进行计算,分组内的所有行的数据都是这个分组中所有数据计算后的值;
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 窗口中的数据进行排序,当 PARTITION BY 与 ORDER BY 列名不同时,聚合函数是根据排序进行逐个聚合计算的,当碰到 ORDER BY 相同的两个值时,同时计算两个值,并两行数据一致;当 PARTITION BY 与 ORDER BY 的列一致时,相当于只有 PARTITION BY;FROM 后面的 ORDER BY 是对整个表的数据进行排序,与 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|
加载全部内容