本篇文章小编给大家分享一下MySQL对window函数执行sum函数可能出现的一个Bug,小编觉得挺不错的,现在分享给大家供大家参考,对此感兴趣的小伙伴们可以来看看。
使用MySql的窗口函数统计数据时,发现一个小的问题,与大家一起探讨下。
环境配置:
mysql-installer-community-8.0.20.0
问题点:在sum对window函数执行时,如果有重复数据,会直接把相同的数据相加,并不是逐步相加。
问题描述
数据:在一个成绩表中,有三个个字段:学生s_id,课程c_id,成绩s_score。
查询条件查询每个课程的学生成绩排名和成绩汇总。
查询结果:发现如果同一个课程有相同成绩是,汇总成绩不是累加的,而是一次全部加上去。
创建数据表
CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) )
插入数据
-- 成绩表数据 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);
查询数据
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_id from score window w as (partition by c_id order by s_score desc);
查询结果
看课程号01的统计结果,数据第一行的sum_v列,前两个数据都是160,按照函数原理,数据应该是80,160。
看课程号02的统计结果,发现结果是正确的,sum_v的第一个为90,第二个为179。
实际显示与预期结果不一致,哪里出了问题。
思考验证
课程号02的数据正确,01的不正确,01与02的区别是01课程的前两个学生成绩一样都是80。
难道是成绩一样,导致sum时出错了。
为了验证这个问题,把课程号01,学号为01的成绩修改为82,然后在执行查询,结果如下
发现sum_v列显示的为82、162,与预期结果一致。
这样可以得出结论,在sum对window函数执行时,如果有重复数据,会直接把相同的数据相加,并不是逐步相加。
其他Sql验证和对比
经过上述验证,Mysql在sum时确实出现了错误,不是逐步累加的。
其他平台是否同样存在问题,在Sqlite Expert 5.3版本验证了下,发现结果一样。
这个就奇怪了,如果是Mysql在实现时出错,Sqlite出同样错误的几率小很多。
难道是sum和window函数结合使用时的特性导致的?