使用MySql的窗口函數(shù)統(tǒng)計(jì)數(shù)據(jù)時(shí)，發(fā)現(xiàn)一個(gè)小的問(wèn)題，與大家一起探討下。

環(huán)境配置：

• mysql-installer-community-8.0.20.0
  

問(wèn)題點(diǎn)：在sum對(duì)window函數(shù)執(zhí)行時(shí)，如果有重復(fù)數(shù)據(jù)，會(huì)直接把相同的數(shù)據(jù)相加，并不是逐步相加。

問(wèn)題描述

數(shù)據(jù)：在一個(gè)成績(jī)表中，有三個(gè)個(gè)字段：學(xué)生s_id，課程c_id，成績(jī)s_score。

查詢條件查詢每個(gè)課程的學(xué)生成績(jī)排名和成績(jī)匯總。

查詢結(jié)果：發(fā)現(xiàn)如果同一個(gè)課程有相同成績(jī)是，匯總成績(jī)不是累加的，而是一次全部加上去。

創(chuàng)建數(shù)據(jù)表

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
)

插入數(shù)據(jù)

-- 成績(jī)表數(shù)據(jù)
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);

查詢數(shù)據(jù)

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);

查詢結(jié)果

看課程號(hào)01的統(tǒng)計(jì)結(jié)果，數(shù)據(jù)第一行的sum_v列，前兩個(gè)數(shù)據(jù)都是160，按照函數(shù)原理，數(shù)據(jù)應(yīng)該是80，160。

看課程號(hào)02的統(tǒng)計(jì)結(jié)果，發(fā)現(xiàn)結(jié)果是正確的，sum_v的第一個(gè)為90，第二個(gè)為179。

實(shí)際顯示與預(yù)期結(jié)果不一致，哪里出了問(wèn)題。

思考驗(yàn)證

課程號(hào)02的數(shù)據(jù)正確，01的不正確，01與02的區(qū)別是01課程的前兩個(gè)學(xué)生成績(jī)一樣都是80。

難道是成績(jī)一樣，導(dǎo)致sum時(shí)出錯(cuò)了。

為了驗(yàn)證這個(gè)問(wèn)題，把課程號(hào)01，學(xué)號(hào)為01的成績(jī)修改為82，然后在執(zhí)行查詢，結(jié)果如下

發(fā)現(xiàn)sum_v列顯示的為82、162，與預(yù)期結(jié)果一致。

這樣可以得出結(jié)論，在sum對(duì)window函數(shù)執(zhí)行時(shí)，如果有重復(fù)數(shù)據(jù)，會(huì)直接把相同的數(shù)據(jù)相加，并不是逐步相加。

其他Sql驗(yàn)證和對(duì)比

經(jīng)過(guò)上述驗(yàn)證，Mysql在sum時(shí)確實(shí)出現(xiàn)了錯(cuò)誤，不是逐步累加的。

其他平臺(tái)是否同樣存在問(wèn)題，在Sqlite Expert 5.3版本驗(yàn)證了下，發(fā)現(xiàn)結(jié)果一樣。

這個(gè)就奇怪了，如果是Mysql在實(shí)現(xiàn)時(shí)出錯(cuò)，Sqlite出同樣錯(cuò)誤的幾率小很多。

難道是sum和window函數(shù)結(jié)合使用時(shí)的特性導(dǎo)致的。歡迎一起討論和研究。

總結(jié)

到此這篇關(guān)于MySQL對(duì)window函數(shù)執(zhí)行sum函數(shù)可能出現(xiàn)的一個(gè)Bug的文章就介紹到這了,更多相關(guān)MySQL對(duì)window函數(shù)執(zhí)行sum函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論