使用MySql的窗口函数统计数据时，发现一个小的问题，与大家一起探讨下。
环境配置：
- mysql-installer-community-8.0.20.0
问题点：在sum对window函数执行时，如果有重复数据，会直接把相同的数据相加，并不是逐步相加。
问题描述
数据：在一个成绩表中，有三个个字段：学生s_id，课程c_id，成绩s_score。
查询条件查询每个课程的学生成绩排名和成绩汇总。
查询结果：发现如果同一个课程有相同成绩是，汇总成绩不是累加的，而是一次全部加上去。
创建数据表
|
1
2
3
4
5
6
|
CREATE TABLE `Score`(`s_id` VARCHAR(20),`c_id` VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`)) |
插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
-- 成绩表数据insert into Score values('01' , '01' , 80);insert into Score values('01' , '02' , 90);insert into Score values('01' , '03' , 99);insert into Score values('02' , '01' , 70);insert into Score values('02' , '02' , 60);insert into Score values('02' , '03' , 80);insert into Score values('03' , '01' , 80);insert into Score values('03' , '02' , 80);insert into Score values('03' , '03' , 80);insert into Score values('04' , '01' , 50);insert into Score values('04' , '02' , 30);insert into Score values('04' , '03' , 20);insert into Score values('05' , '01' , 76);insert into Score values('05' , '02' , 87);insert into Score values('06' , '01' , 31);insert into Score values('06' , '03' , 34);insert into Score values('07' , '02' , 89);insert into Score values('07' , '03' , 98); |
查询数据
|
1
2
3
4
5
6
7
8
9
10
11
|
select c_id,s_id,s_score,first_value(s_score) over w as first_v,last_value(s_score) over w as last_v,sum(s_score) over w as sum_v,max(s_score) over w as max_v,min(s_score) over w as min_v,count(s_id) over w as count_v,row_number() over w as row_id,rank() over w as rank_id,dense_rank() over w as dense_idfrom score window w as (partition by c_id order by s_score desc); |
查询结果
看课程号01的统计结果，数据第一行的sum_v列，前两个数据都是160，按照函数原理，数据应该是80，160。
看课程号02的统计结果，发现结果是正确的，sum_v的第一个为90，第二个为179。
实际显示与预期结果不一致，哪里出了问题。
| c_id | s_id | s_score | first_v | last_v | sum_v | max_v | min_v | count_v | row_id | rank_id | dense_id |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 01 | 01 | 80 | 80 | 80 | 160 | 80 | 80 | 2 | 1 | 1 | 1 |
| 01 | 03 | 80 | 80 | 80 | 160 | 80 | 80 | 2 | 2 | 1 | 1 |
| 01 | 05 | 76 | 80 | 76 | 236 | 80 | 76 | 3 | 3 | 3 | 2 |
| 01 | 02 | 70 | 80 | 70 | 306 | 80 | 70 | 4 | 4 | 4 | 3 |
| 01 | 04 | 50 | 80 | 50 | 356 | 80 | 50 | 5 | 5 | 5 | 4 |
| 01 | 06 | 31 | 80 | 31 | 387 | 80 | 31 | 6 | 6 | 6 | 5 |
| 02 | 01 | 90 | 90 | 90 | 90 | 90 | 90 | 1 | 1 | 1 | 1 |
| 02 | 07 | 89 | 90 | 89 | 179 | 90 | 89 | 2 | 2 | 2 | 2 |
| 02 | 05 | 87 | 90 | 87 | 266 | 90 | 87 | 3 | 3 | 3 | 3 |
| 02 | 03 | 80 | 90 | 80 | 346 | 90 | 80 | 4 | 4 | 4 | 4 |
| 02 | 02 | 60 | 90 | 60 | 406 | 90 | 60 | 5 | 5 | 5 | 5 |
| 02 | 04 | 30 | 90 | 30 | 436 | 90 | 30 | 6 | 6 | 6 | 6 |
| 03 | 01 | 99 | 99 | 99 | 99 | 99 | 99 | 1 | 1 | 1 | 1 |
| 03 | 07 | 98 | 99 | 98 | 197 | 99 | 98 | 2 | 2 | 2 | 2 |
| 03 | 02 | 80 | 99 | 80 | 357 | 99 | 80 | 4 | 3 | 3 | 3 |
| 03 | 03 | 80 | 99 | 80 | 357 | 99 | 80 | 4 | 4 | 3 | 3 |
| 03 | 06 | 34 | 99 | 34 | 391 | 99 | 34 | 5 | 5 | 5 | 4 |
| 03 | 04 | 20 | 99 | 20 | 411 | 99 | 20 | 6 | 6 | 6 | 5 |
思考验证
课程号02的数据正确，01的不正确，01与02的区别是01课程的前两个学生成绩一样都是80。
难道是成绩一样，导致sum时出错了。
为了验证这个问题，把课程号01，学号为01的成绩修改为82，然后在执行查询，结果如下
发现sum_v列显示的为82、162，与预期结果一致。
这样可以得出结论，在sum对window函数执行时，如果有重复数据，会直接把相同的数据相加，并不是逐步相加。
| c_id | s_id | s_score | first_v | last_v | sum_v | max_v | min_v | count_v | row_id | rank_id | dense_id |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 01 | 01 | 80 | 80 | 82 | 82 | 82 | 82 | 2 | 1 | 1 | 1 |
| 01 | 03 | 80 | 80 | 80 | 162 | 82 | 80 | 2 | 2 | 1 | 1 |
| 01 | 05 | 76 | 80 | 76 | 236 | 82 | 76 | 3 | 3 | 3 | 2 |
| 01 | 02 | 70 | 80 | 70 | 306 | 82 | 70 | 4 | 4 | 4 | 3 |
| 01 | 04 | 50 | 80 | 50 | 356 | 82 | 50 | 5 | 5 | 5 | 4 |
| 01 | 06 | 31 | 80 | 31 | 387 | 82 | 31 | 6 | 6 | 6 | 5 |
| 02 | 01 | 90 | 90 | 90 | 90 | 90 | 90 | 1 | 1 | 1 | 1 |
| 02 | 07 | 89 | 90 | 89 | 179 | 90 | 89 | 2 | 2 | 2 | 2 |
| 02 | 05 | 87 | 90 | 87 | 266 | 90 | 87 | 3 | 3 | 3 | 3 |
| 02 | 03 | 80 | 90 | 80 | 346 | 90 | 80 | 4 | 4 | 4 | 4 |
| 02 | 02 | 60 | 90 | 60 | 406 | 90 | 60 | 5 | 5 | 5 | 5 |
| 02 | 04 | 30 | 90 | 30 | 436 | 90 | 30 | 6 | 6 | 6 | 6 |
| 03 | 01 | 99 | 99 | 99 | 99 | 99 | 99 | 1 | 1 | 1 | 1 |
| 03 | 07 | 98 | 99 | 98 | 197 | 99 | 98 | 2 | 2 | 2 | 2 |
| 03 | 02 | 80 | 99 | 80 | 357 | 99 | 80 | 4 | 3 | 3 | 3 |
| 03 | 03 | 80 | 99 | 80 | 357 | 99 | 80 | 4 | 4 | 3 | 3 |
| 03 | 06 | 34 | 99 | 34 | 391 | 99 | 34 | 5 | 5 | 5 | 4 |
| 03 | 04 | 20 | 99 | 20 | 411 | 99 | 20 | 6 | 6 | 6 | 5 |
其他Sql验证和对比
经过上述验证，Mysql在sum时确实出现了错误，不是逐步累加的。
其他平台是否同样存在问题，在Sqlite Expert 5.3版本验证了下，发现结果一样。
这个就奇怪了，如果是Mysql在实现时出错，Sqlite出同样错误的几率小很多。
难道是sum和window函数结合使用时的特性导致的。欢迎一起讨论和研究。
总结
到此这篇关于MySQL对window函数执行sum函数可能出现的一个Bug的文章就介绍到这了,更多相关MySQL对window函数执行sum函数内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://www.cnblogs.com/StitchSun/p/13267707.html
