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

最新推荐文章于 2023-07-17 15:37:13 发布
最新推荐文章于 2023-07-17 15:37:13 发布
阅读量278 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hxj413977035/article/details/120668106
版权

开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合)，它对一组值进行操作，不需要使用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张三00012017-01-115000
2李四00022018-10-106000
3王五00032018-12-192000
8毛志宇00042016-09-204500
11梅婷婷00052013-07-078000

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

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

  1. order by后面的列必须是在select后面存在的
  2. 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的方式进行排序，至此我们已经拿到了每个部门内员工按薪资排序的结果。

sortidemp_namedept_noemp_hire_dateemp_salary
11张三00012017-01-115000
25王强强00012019-03-143000
310范德武00012020-02-123000
16刘阳00022019-08-086000
24赵六00022019-09-114000
39刘德仁00022016-02-253500
42李四00022018-10-101000
13王五00032018-12-192000
27周心怡00032015-06-10500
18毛志宇00042016-09-204500
111梅婷婷00052013-07-078000
212郑冰00052014-11-171000

至此，我们只需要在上面这个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张三00012017-01-115000
2李四00022018-10-106000
3王五00032018-12-192000
8毛志宇00042016-09-204500
11梅婷婷00052013-07-078000

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

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_sumemp_salaryidemp_namedept_noemp_hire_date
500050001张三00012017-01-11
800030005王强强00012019-03-14
11000300010范德武00012020-02-12
600060006刘阳00022019-08-08
1000040004赵六00022019-09-11
1350035009刘德仁00022016-02-25
1450010002李四00022018-10-10
200020003王五00032018-12-19
25005007周心怡00032015-06-10
450045008毛志宇00042016-09-20
8000800011梅婷婷00052013-07-07
9000100012郑冰00052014-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;

结果如下：

idemp_namedept_noemp_salarymaxSalarysalaryErroremp_hire_date
5王强强00013000500020002019-03-14
10范德武00013000500020002020-02-12
1张三00015000500002017-01-11
2李四00021000600050002018-10-10
9刘德仁00023500600025002016-02-25
4赵六00024000600020002019-09-11
6刘阳00026000600002019-08-08
7周心怡0003500200015002015-06-10
3王五00032000200002018-12-19
8毛志宇00044500450002016-09-20
12郑冰00051000800070002014-11-17
11梅婷婷00058000800002013-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;

结果如下：

idemp_namedept_noemp_salarymaxSalarysalaryErroremp_hire_date
5王强强00013000500020002019-03-14
10范德武00013000500020002020-02-12
1张三00015000500002017-01-11
2李四00021000600050002018-10-10
9刘德仁00023500600025002016-02-25
4赵六00024000600020002019-09-11
6刘阳00026000600002019-08-08
7周心怡0003500200015002015-06-10
3王五00032000200002018-12-19
8毛志宇00044500450002016-09-20
12郑冰00051000800070002014-11-17
11梅婷婷00058000800002013-07-07

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

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

不务正业的攻城狮
关注
专栏目录
mysql8窗口函数
while(True): print('adorable')
02-12 1219
window
SQL开窗函数
最新发布
zwldn的博客
05-21 1656
数据分析必备函数：开窗函数提供了在查询结果中进行排序、排名、聚合和分析的灵活性，在数据分析和报表生成中非常有用，可以实现更复杂的计算和分析需求。
SQL SERVER开窗函数
ddkyij9397的博客
01-07 1237
作为一名开发人员来讲，我感觉在职场白混了好多年，可能是自己真的没有进取的精神吧，看了《程序员的SQL金典》这本电子书，真的让我学到了不少知识，真心喜欢这本电子书，书中讲解的内容比较好懂，也比较实用。谢谢作者的辛勤汗水:)。 今天将要介绍SQL Sever的开窗函数，何谓开窗函数，不懂吧。反正对于我来说，我是摸不着头脑了，第一次听说过。那么，什么是开窗函数，其实可以理解为是聚合函数的...
mysql开窗方式
weixin_38430685的博客
07-17 265
mysql开窗
MySQL8窗口函数
mmqgirlfriend的博客
04-04 249
窗口限定一个范围，它可以理解为满足某些条件的记录集合，窗口函数也就是在窗口范围内执行的函数。
mysql语句实现hive开窗效果
luo981695830的博客
05-04 388
建表语句 create table t{ id varchar(20), dates varchar(20), v_num int }charset=utf8; 插入多行数据 insert into t values ("a","2020-01",5), ("b","2020-01",20"), ``` ``` ``` ("a","2020-05",25), ("b","...
Oracle数据库中SQL开窗函数的使用
12-14
Oracle、SQL Server、DB2等主流数据库系统都实现了开窗函数，但MySQL暂时还不支持。 开窗函数与聚合函数相似，都是对一组行进行计算，但不同之处在于，开窗函数可以在每组内返回多个结果，而不是像聚合函数那样只...
MySQL对window函数执行sum函数可能出现的一个Bug
12-14
总之，虽然MySQL的窗口函数在某些特定情况下的行为可能不符合预期，但通过理解这些行为并调整查询策略，我们仍然可以利用这些功能强大的工具来获取我们需要的分析结果。确保在使用窗口函数时仔细测试和验证结果，...
mysql窗口函数同比环比_MySQL利用窗口函数解决排名问题
weixin_33654339的博客
02-08 906
从MySQL8.0开始，MySQL增加了对窗口函数的支持。本文主要介绍如何利用窗口函数row_number(),dense_rank()和rank()解决相关排名问题。说明：本文中的SQL代码均测试于MySQL8.0.13环境。在总体上进行排名：# 创建学生成绩表：create table grade(id int,name varchar(10),score int (10))engine my...
mysql within 函数_SQL基础----（四）复杂的子查询(select within select)
weixin_35802555的博客
02-17 2553
(一) 关键知识点1.1 消化阶梯任何知识点，均可遵循此消化路径1.2 功能点1.2.1 视图案例选中视图，右键刷新，将会呈现新创建的视图选中具体视图，右键弹出功能菜单，选择对应功能项注意事项(1) 视图中存放的是sql语句，非表的实际数据，节省存储空间，动态更新较便捷；(2) from子句中，视图名代替表名，运行时动态创建出一张临时表；(3) 频繁/复杂的sql语句，保存成视图，可反复使用，节省...
MySQL8中的开窗函数
Gan_1314的博客
07-05 4591
MySQL从8.0版本开始支持窗口函数了，窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组（GROUP BY）的某种聚合值，它和聚合函数的不同之处是：窗口函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。..................
mysql开窗函数
m0_46926492的博客
04-18 1万+
窗口函数为了解决想要即显示聚集前的数据，又要显示聚集后的数据。开窗函数对一组值进行操作，不需要group by 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列注意：开窗函数在mysql8.0后才有开窗函数不会修改源数据表的结构，会在表的最后一列添加想要的结果，如果分组存在多行数据，则重复显示。
mysql中使用分析函数(开窗函数)
热门推荐
彤哥读源码
03-28 4万+
众说周知，oracle的分析函数用于复杂的统计方面非常好用，但mysql不支持oracle的分析函数（开窗函数），怎么办呢？ 我们可以通过另外的途径来使用，直接贴上代码： create table income_tl( user_id int, create_date date, income int ); insert into income_tl values(1,'201...
MySQL 8.0新特性（第一篇）—开窗函数
Max_Leo的博客
08-15 500
MySQL从5.X直接蹦到8.0时代，从甲骨文收购其之前就是5.1，收购之后5.X版本陆续发布到5.7，8.0可谓是一次突飞猛进的改变，一堆新特性的加持，让MySQL在NoSQL、NewSQL横行的年代，重焕生机。 MySQL在5.X时代，就没有开窗函数，着实让使用SQL的小伙伴们难受啊，我甚至写过一篇MySQL5.X用集合思维来实现开窗函数的功能。开窗函数作为一种高级函数，Oracle早就加持了，终于在8.0版本，MySQL也有了。 开窗函数名([<字段名>...
【SQL】Sql如何用开窗函数实现去重
tting_的博客
05-24 2573
直接上代码啦，注释很详细啦 --先drop再create实现重跑机制 drop table if exists table_a; create table table_a row format delimited fields terminated by '\001' collection items terminated by '\002' map keys terminated by '\003' lines terminated by '\n' stored as textfile as
MySQL经典案例分析---查询平均工资最低的部门信息
QYJXY的博客
02-25 8895
#一.背景描述： ##来源：尚硅谷MySQL教学98课案例2 ##问题：查询平均工资最低的部门信息 ##附件：相关数据库 提取码：8r48 #二.问题分析： 该问题中平均工资涉及employees表，部门信息涉及departments表， 可将该问题按步骤拆分： 查询各部门平均工资 查询最低部门平均工资 查询最低部门平均工资对应部门编号 查询此部门编号对应部门信息 按此逻辑，尚硅谷的李玉婷老师给...
主键约束、外键约束、唯一约束、检查约束、默认值约束实例
走马观花
08-06 2780
http://xinxinyin666.i.sohu.com/blog/view/226237927.htm 主键(primary key)约束、外键(foreign key)约束、唯一(unique)约束、检查(check)约束、默认值(default)约束实例   Oracle 有如下类型的约束: NOT NULL(非空)、UNIQUE Key(唯一约束)、PRIMARY KEY(主键
查询平均工资小于5时候的部门
Jiashilin
09-08 1223
select 部门名称，人员ID，人员姓名，人员薪资 from dept_salary_info group by 部门名称 having avg(人员薪资)<5;
MySQL 5.7.x 中如何实现类似开窗函数的功能，如分组后取每组的topN等
penriver的博客
01-21 5325
在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版本中，如何实现开窗函数的功能呢？ 本文提供这方面的示例。
MySQL集合运算与开窗函数：模拟Intersect、Except
"Mysql 集合运算，伪列，开窗函数及示例代码" MySQL中的集合运算主要包括并集（Union），但在标准SQL中常见的交集（Intersect）和差集（Except）在MySQL中并未直接实现。通常，我们通过其他方式如IN和NOT IN来模拟...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

不务正业的攻城狮

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值