在MySQL 5.7.x中，如何轻松实现像SQL窗口函数那样的功能，比如分组后获取每组内的前N个数据？

最编程 2024-07-23 07:17:25

...

在SQL中经常遇到一种需求：分组排序后取TopN、累加和最大值之间的差值。
这样的需求，如果数据库支持窗口函数，如row_number() OVER (PARTITION BY dept_no ORDER BY emp_salary DESC ) AS row_num 是很容易实现的。

在MySQL 8.0 之前的版本不支持窗口函数。
但是目前还有很多人在使用5.7.x版本，在MySQL 5.7.x版本中，如何实现开窗函数的功能呢？本文提供这方面的示例。

注意：本文窗口函数的示例在clickhouse 21.8上验证通过。

MySQL 5.7.x中如何实现开窗函数的功能

MySQL 5.7.x中实现开窗函数的功能，主要是基于会话变量实现的。

测试数据

drop table if exists emp;
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'),
(2,'李四','0002',1000,'2018-10-10'),
(3,'王五','0003',8000,'2018-12-19'),
(4,'赵六','0002',4000,'2019-09-11'),
(5,'王强强','0001',3000,'2019-03-14'),
(6,'刘阳','0002',6000,'2019-08-08'),
(7,'周心怡','0003',500,'2015-06-10'),
(8,'毛志宇','0004',4500,'2016-09-20'),
(9,'刘德仁','0002',3500,'2016-02-25'),
(10,'范德武','0001',3000,'2020-02-12'),
(11,'梅婷婷','0005',8000,'2013-07-07'),
(12,'郑冰','0005',1000,'2014-11-17'),
(13,'张一冰','0005',12000,'2014-11-17');

业务使用场景

分组后取topN

需求：获取每一个部门薪水最高的2名员工信息
分析：按照部门分组，每一部门的员工放在一组，然后基于这个组中的数据，按照工资降序排列。然后再根据排序后的结果集，获取排序为1的数据行即为结果
实现SQL: 如下：

select dept_no,emp_name,emp_salary,row_num from (
	select 
		case when @dept_no_g != x.dept_no then @row_num_g :=1 else @row_num_g :=@row_num_g + 1 end as row_num,
		 x.emp_name,
		 @dept_no_g := x.dept_no as dept_no,
		 x.emp_salary,
		 x.emp_hire_date
	from emp as x,(select @dept_no_g :='') as t1,(select @row_num_g :=0) as t2 
	order by dept_no,emp_salary desc 
) res where row_num < 3

窗口函数实现：如下

select id,emp_name,dept_no,emp_salary,emp_hire_date,row_num from (
	SELECT id,emp_name,dept_no,emp_salary,emp_hire_date,row_number() OVER w AS row_num 
	FROM emp WINDOW  w AS (PARTITION BY  dept_no ORDER BY emp_salary DESC) order by dept_no 
) as res where row_num <3

在这里插入图片描述

分组后累计求和

需求：累计统计每一个部门下所有员工的工资之和。
分析：按照部门分组，每一部门的员工放在一组，然后基于这个组中的数据，逐行累加该部门下所有员工的工资。
实现SQL: 如下：

select 
  x.id, 
	x.emp_name,
	x.emp_salary,
	case when @dept_no_g != x.dept_no then @emp_salary_sum_g := x.emp_salary 
	     when @dept_no_g = x.dept_no then @emp_salary_sum_g := @emp_salary_sum_g + x.emp_salary 
	     end as emp_salary_sum, 
	@dept_no_g := x.dept_no as dept_no,  
	x.emp_hire_date
from emp as x,(select @dept_no_g :='') as t1,(select @emp_salary_sum_g :=0) as t2
order by dept_no, emp_salary desc;

窗口函数实现：如下

SELECT id,emp_name,dept_no,emp_salary,emp_hire_date,sum(emp_salary) OVER w AS emp_salary_sum 
FROM emp WINDOW  w AS (PARTITION BY  dept_no ORDER BY emp_salary DESC) order by dept_no, emp_salary desc

在这里插入图片描述

分组后求和最大值的差值

需求：计算每个员工和部门中工资最高员工的工资差
分析：根据员工的部门分组，然后判断得到每组数据中，工资最高的员工的工资。把这个作为一个新列查询出出来。基于步骤1的结果集中的新列，和员工的工资列做减法得到差值

实现SQL: 如下：

select id,emp_name,emp_salary,emp_salary_max,emp_salary_max - emp_salary as salary_cha,dept_no,emp_hire_date from (
	select 
		x.id, 
		x.emp_name,
		x.emp_salary,
		case when @dept_no_g!= x.dept_no then @emp_salary_max_g := x.emp_salary 
				 when @dept_no_g= x.dept_no and x.emp_salary > @emp_salary_max_g 
						then @emp_salary_max_g := x.emp_salary else @emp_salary_max_g 
				end as emp_salary_max,
		@dept_no_g := x.dept_no as dept_no,  
		x.emp_hire_date
	from emp as x,(select @dept_no_g :='') as t1,(select @emp_salary_max_g :=0) as t2
	order by dept_no, emp_salary desc
	) as res

窗口函数实现：如下

select id,emp_name,emp_salary,emp_salary_max,emp_salary_max - emp_salary as salary_cha,dept_no,emp_hire_date from (
	SELECT id,emp_name,dept_no,emp_salary,emp_hire_date,max(emp_salary) OVER w AS emp_salary_max
	FROM emp WINDOW  w AS (PARTITION BY  dept_no ORDER BY emp_salary DESC) order by dept_no, emp_salary desc 
) as res

在这里插入图片描述

实现row_number功能

MySQL 5.7.x实现

	select 
		 x.id,
		 x.emp_name,
		 @dept_no_g := x.dept_no as dept_no,
		 x.emp_salary,
		 x.emp_hire_date,
		 @row_num_g := IF(@dept_no_g = x.dept_no, @row_num_g + 1, 1) num
	from emp as x,(select @dept_no_g :='') as t1,(select @row_num_g :=0) as t2 
	order by dept_no,emp_salary desc

窗口函数实现

SELECT id,emp_name,dept_no,emp_salary,emp_hire_date,
row_number() OVER (PARTITION BY  dept_no ORDER BY emp_salary DESC)  AS row_num
FROM emp order by dept_no, emp_salary desc

实现dense_rank()的功能

MySQL 5.7.x实现

select 
		 x.id,
		 x.emp_name,
		 x.emp_salary,
		 case when @salary_g = emp_salary then @row_num_g
				  when @salary_g := emp_salary then @row_num_g := @row_num_g+1 
					else @row_num_g := @row_num_g + 1 end as rn,
		 x.emp_hire_date
	from emp as x,(select @salary_g := 0, @row_num_g :=0) as t1 
	order by emp_salary asc

窗口函数实现

SELECT id,emp_name,dept_no,emp_salary,emp_hire_date,
	dense_rank() OVER (ORDER BY emp_salary DESC)  AS row_num2
FROM emp order by emp_salary desc

在这里插入图片描述

附录

clickhouse测试建表语句

CREATE TABLE emp (
	id UInt32,
	emp_name String,
	dept_no String,
	emp_salary UInt32,
	emp_hire_date Date
) ENGINE = MergeTree()
ORDER BY emp_hire_date;

上一篇：在MySQL 5.7中使用窗口函数获取每个分组的最新记录

下一篇：理解 SQL 窗口函数: over()与 sum, AVG, MIN, MAX 的实战应用