mysql8以前利用sql实现开窗函数效果

站长

2022年12月08日 16:53 · 阅读数 51

开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合)，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。 ----- 摘自百度百科说实话，这段话对没接触过的人来说很难理解，有兴趣的可以去了解下百度百科的全面介绍（开窗函数）。

今天，我们直接按照例子来说一说他能实现的功能，废话少说，开搞！目前我使用的环境是mysql 5.7.18，可以通过以下命令查看自己的mysql版本

SELECT VERSION();

首先，准备测试数据：

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `emp_name` varchar(255) DEFAULT NULL,
  `dept_no` varchar(255) DEFAULT NULL,
  `emp_salary` int(10) DEFAULT NULL,
  `emp_hire_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (1, '张三', '0001', 5000, '2017-01-11');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (2, '李四', '0002', 1000, '2018-10-10');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (3, '王五', '0003', 2000, '2018-12-19');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (4, '赵六', '0002', 4000, '2019-09-11');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (5, '王强强', '0001', 3000, '2019-03-14');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (6, '刘阳', '0002', 6000, '2019-08-08');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (7, '周心怡', '0003', 500, '2015-06-10');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (8, '毛志宇', '0004', 4500, '2016-09-20');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (9, '刘德仁', '0002', 3500, '2016-02-25');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (10, '范德武', '0001', 3000, '2020-02-12');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (11, '梅婷婷', '0005', 8000, '2013-07-07');
INSERT INTO `emp`(`id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date`) VALUES (12, '郑冰', '0005', 1000, '2014-11-17');

分组排序 需求描述：获取每一个部门薪水最高的员工的信息。

分析：看到这个需求，常规的sql可能不好处理，但是仔细想一想，通过 MAX() 函数以及 GROUP BY 我们还是可以完成这个需求的：于是有了如下的sql:

SELECT id,emp_name,dept_no,emp_hire_date, MAX(emp_salary) as salary FROM emp  GROUP BY dept_no;

幸运的你有可能已经拿到了如下的结果：

序号	姓名	部门编号	入职日期	薪资
1	张三	0001	2017-01-11	5000
2	李四	0002	2018-10-10	6000
3	王五	0003	2018-12-19	2000
8	毛志宇	0004	2016-09-20	4500
11	梅婷婷	0005	2013-07-07	8000

然而不幸的是我得到了如下的错误

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

看到提示信息里面有一个比较关键的信息

sql_mode=only_full_group_by

问题出现的原因： MySql从5.7版本开始默认开启only_full_group_by规则，规则核心原则如下，没有遵循原则的sql会被认为是不合法的sql

order by后面的列必须是在select后面存在的
select、having或order by后面存在的非聚合列必须全部在group by中存在

解决方法如下：第一种，修改sql使其遵守only_full_group_by规则第二种，将MySql的版本降到5.7以下第三种，关闭only_full_group_by规则第四种，使用 any_value() 函数关于 any_value() 的使用介绍，可以查看官方说明（any_value()函数）。这里通过 any_value 函数来处理这个报错，对sql作如下改造

SELECT ANY_VALUE(id),
       ANY_VALUE(emp_name),
       ANY_VALUE(dept_no),
       ANY_VALUE(emp_hire_date),
       MAX(emp_salary) as salary
FROM emp
GROUP BY dept_no;

没有意外的话，我们就可以拿到上面的结果了。

至此，我们完成了第一个需求，而且使用的方式和开窗函数没有半毛钱关系。开窗函数简单理解就是可以实现分组内排序、求和等的效果，这句话不是就很贴合我们这个需求么？如果能够对每个部门的员工薪资进行倒叙排序，然后取每个部门的第一名，不是同样可以拿到我们需要的结果么？当然，mysql8以及其他的部分数据库已经内置了对开窗函数的支持，通过 over(partition by xxx ) 等就可以简单实现了，今天我们讨论的是mysql8以下我们如何通过sql实现开窗函数的效果。

首先介绍一个知识点，在mysql里是可以通过 @变量名 := 变量值 来定义用户变量的。

SET @NAME := '张三';
SELECT @NAME;

执行上面这两句sql是可以正常查询到 @NAME 的值为张三的。因为要进行排序，这里是需要用到自定义变量的，看如下sql

SELECT IF(@dept_no != emp.dept_no, @row_num := 1, @row_num := @row_num + 1) AS sort,
       id,
       emp_name,
       @dept_no := dept_no                                                  AS dept_no,
       emp_hire_date,
       emp_salary
FROM emp,
     (SELECT @dept_no := '') AS t1,
     (SELECT @row_num := 0) AS t2
ORDER BY dept_no, emp_salary DESC;

语句不难理解，我们自定义了 @dept_no 和 @row_num 两个变量，初始的时候 @dept_no 的值为空字符串，@row_num 的值为0，同时对 @dept_no 赋值为 dept_no，针对每一列，当当前 @dept_no 不等于 dept_no 时，我们令 @row_num 值为1，否则为 @row_num +1，最后按照 dept_no,emp_salary DESC的方式进行排序，至此我们已经拿到了每个部门内员工按薪资排序的结果。

sort	id	emp_name	dept_no	emp_hire_date	emp_salary
1	1	张三	0001	2017-01-11	5000
2	5	王强强	0001	2019-03-14	3000
3	10	范德武	0001	2020-02-12	3000
1	6	刘阳	0002	2019-08-08	6000
2	4	赵六	0002	2019-09-11	4000
3	9	刘德仁	0002	2016-02-25	3500
4	2	李四	0002	2018-10-10	1000
1	3	王五	0003	2018-12-19	2000
2	7	周心怡	0003	2015-06-10	500
1	8	毛志宇	0004	2016-09-20	4500
1	11	梅婷婷	0005	2013-07-07	8000
2	12	郑冰	0005	2014-11-17	1000

至此，我们只需要在上面这个sql的外面再嵌套一层查询去查询每个部门中sort为1的员工信息就可以拿到我们想要的结果了，完整sql如下：

SELECT id, emp_name, dept_no, emp_hire_date, emp_salary
FROM (
         SELECT IF(@dept_no != emp.dept_no, @row_num := 1, @row_num := @row_num + 1) AS sort,
                id,
                emp_name,
                @dept_no := dept_no                                                  AS dept_no,
                emp_hire_date,
                emp_salary
         FROM emp,
              (SELECT @dept_no := '') AS t1,
              (SELECT @row_num := 0) AS t2
         ORDER BY dept_no, emp_salary DESC) tmp
WHERE sort = 1
ORDER BY dept_no;

结果如下：

序号	姓名	部门编号	入职日期	薪资
1	张三	0001	2017-01-11	5000
2	李四	0002	2018-10-10	6000
3	王五	0003	2018-12-19	2000
8	毛志宇	0004	2016-09-20	4500
11	梅婷婷	0005	2013-07-07	8000

分组求和 需求描述：累计统计每一个部门下所有员工的工资之和。

SELECT ANY_VALUE(dept_no),sum(emp_salary) FROM emp GROUP BY dept_no;

一条sql轻松搞定，但是如果要实现每个部门员工工资累计的效果呢（某部门第一行显示该员工的工资，该部门第二行显示的是第一个员工薪资+第二个员工的薪资......）有了第一个需求的经验，我们知道可以通过定义一个变量 @salary_sum 来存储部门员工薪资的累计值。有了上面的经验我们很快可以完成这个sql

SELECT IF(@dept_no != emp.dept_no, @salary_sum := emp_salary, @salary_sum := @salary_sum + emp_salary) AS salary_sum,
       emp_salary,
       id,
       emp_name,
       @dept_no := dept_no                                                                             AS dept_no,
       emp_hire_date
FROM emp,
     (SELECT @dept_no := '') AS t1,
     (SELECT @salary_sum := 0) AS t2
ORDER BY dept_no, emp_salary DESC;

结果如下：

salary_sum	emp_salary	id	emp_name	dept_no	emp_hire_date
5000	5000	1	张三	0001	2017-01-11
8000	3000	5	王强强	0001	2019-03-14
11000	3000	10	范德武	0001	2020-02-12
6000	6000	6	刘阳	0002	2019-08-08
10000	4000	4	赵六	0002	2019-09-11
13500	3500	9	刘德仁	0002	2016-02-25
14500	1000	2	李四	0002	2018-10-10
2000	2000	3	王五	0003	2018-12-19
2500	500	7	周心怡	0003	2015-06-10
4500	4500	8	毛志宇	0004	2016-09-20
8000	8000	11	梅婷婷	0005	2013-07-07
9000	1000	12	郑冰	0005	2014-11-17

分组求最大值

需求描述：计算每个员工和部门中工资最高员工的工资差。首先求出每个部门最高的薪资，然后用每个员工的工资与自己对应部门的最高工资做个减法。

SELECT id,
       emp_name,
       emp.dept_no,
       emp_salary,
       salary_info.maxSalary,
       salary_info.maxSalary - emp.emp_salary AS salaryError,
       emp_hire_date
FROM emp,
     (SELECT MAX(emp_salary) AS maxSalary, dept_no FROM emp GROUP BY dept_no) salary_info
WHERE emp.dept_no = salary_info.dept_no
ORDER BY emp.dept_no, emp.emp_salary;

结果如下：

id	emp_name	dept_no	emp_salary	maxSalary	salaryError	emp_hire_date
5	王强强	0001	3000	5000	2000	2019-03-14
10	范德武	0001	3000	5000	2000	2020-02-12
1	张三	0001	5000	5000	0	2017-01-11
2	李四	0002	1000	6000	5000	2018-10-10
9	刘德仁	0002	3500	6000	2500	2016-02-25
4	赵六	0002	4000	6000	2000	2019-09-11
6	刘阳	0002	6000	6000	0	2019-08-08
7	周心怡	0003	500	2000	1500	2015-06-10
3	王五	0003	2000	2000	0	2018-12-19
8	毛志宇	0004	4500	4500	0	2016-09-20
12	郑冰	0005	1000	8000	7000	2014-11-17
11	梅婷婷	0005	8000	8000	0	2013-07-07

现在看看按我们之前定义变量的方式怎么实现。原理类似，定义一个变量@maxSalary存储当前部门员工工资的最高值，如果同一部门下个员工的工资大于@maxSalary，则把@maxSalary赋值为该员工的工资。 sql如下：

SELECT tmp.id,
       tmp.emp_name,
       tmp.dept_no,
       tmp.emp_salary,
       tmp.max_salary,
       tmp.max_salary - tmp.emp_salary AS salaryError,
       tmp.emp_hire_date
FROM (
         SELECT CASE
                    WHEN @dept_no != emp.dept_no THEN @max_salary := emp_salary
                    WHEN @dept_no = dept_no AND @max_salary < emp_salary THEN @max_salary := emp_salary
                    ELSE @max_salary END AS max_salary,
                emp_salary,
                id,
                emp_name,
                @dept_no := dept_no      AS dept_no,
                emp_hire_date
         FROM emp,
              (SELECT @dept_no := '') AS t1,
              (SELECT @max_salary := 0) AS t2
         ORDER BY emp.dept_no, emp.emp_salary DESC) tmp
ORDER BY tmp.dept_no, tmp.emp_salary;

结果如下：

id	emp_name	dept_no	emp_salary	maxSalary	salaryError	emp_hire_date
5	王强强	0001	3000	5000	2000	2019-03-14
10	范德武	0001	3000	5000	2000	2020-02-12
1	张三	0001	5000	5000	0	2017-01-11
2	李四	0002	1000	6000	5000	2018-10-10
9	刘德仁	0002	3500	6000	2500	2016-02-25
4	赵六	0002	4000	6000	2000	2019-09-11
6	刘阳	0002	6000	6000	0	2019-08-08
7	周心怡	0003	500	2000	1500	2015-06-10
3	王五	0003	2000	2000	0	2018-12-19
8	毛志宇	0004	4500	4500	0	2016-09-20
12	郑冰	0005	1000	8000	7000	2014-11-17
11	梅婷婷	0005	8000	8000	0	2013-07-07

至此，通过实现类似开窗函数的效果完成了上面的三个需求，我也采用了我们常规的sql以及定义用户变量两种方式来实现，面对具体需求时，看哪种简单就采用哪种实现吧。

目前工作中接触带这类需求的场景并不多，最近刚好有这么个需求就简单研究了下这块，同时参考了这篇博客(MySQL5.7中如何使用开窗函数)，有兴趣的可以过去看看，或许能让你有不同的收获。

转载自:https://juejin.cn/post/7068096924968026126