2.5.16版本sum统计结果不一致

💬 StarRocks 用户问答
#1

版本2.5.16，执行sum统计每次结果都不一样

image
image2516×1350 224 KB

CREATE TABLE dws_kpi_v_cbfyhs_month_dd1 (
kid varchar(65533) NOT NULL COMMENT “主键”,
month varchar(65533) NOT NULL COMMENT “月”,
industry_code varchar(65533) NULL COMMENT “”,
industry_name varchar(65533) NULL COMMENT “”,
par_center_code varchar(65533) NULL COMMENT “”,
par_center_name varchar(65533) NULL COMMENT “”,
kpi015396 varchar(65533) NULL COMMENT “”,
kpi015400 varchar(65533) NULL COMMENT “”,
kpi015398 varchar(65533) NULL COMMENT “”,
kpi015402 varchar(65533) NULL COMMENT “”,
kpi015412 varchar(65533) NULL COMMENT “”,
kpi015417 varchar(65533) NULL COMMENT “”,
kpi015421 varchar(65533) NULL COMMENT “”,
kpi015423 varchar(65533) NULL COMMENT “”,
kpi015425 varchar(65533) NULL COMMENT “”,
kpi015436 varchar(65533) NULL COMMENT “”,
kpi015434 varchar(65533) NULL COMMENT “”,
kpi015438 varchar(65533) NULL COMMENT “”,
kpi015439 varchar(65533) NULL COMMENT “”,
kpi015489 varchar(65533) NULL COMMENT “”,
kpi015494 varchar(65533) NULL COMMENT “”,
kpi015519 varchar(65533) NULL COMMENT “”,
kpi015524 varchar(65533) NULL COMMENT “”,
kpi015536 varchar(65533) NULL COMMENT “”,
kpi015547 varchar(65533) NULL COMMENT “”,
kpi015705 varchar(65533) NULL COMMENT “”,
kpi015708 varchar(65533) NULL COMMENT “”,
kpi015712 varchar(65533) NULL COMMENT “”,
kpi015728 varchar(65533) NULL COMMENT “”,
kpi015729 varchar(65533) NULL COMMENT “”,
kpi015730 varchar(65533) NULL COMMENT “”,
kpi015731 varchar(65533) NULL COMMENT “”,
kpi015732 varchar(65533) NULL COMMENT “”,
kpi015733 varchar(65533) NULL COMMENT “”,
kpi015737 varchar(65533) NULL COMMENT “”,
kpi015740 varchar(65533) NULL COMMENT “”,
kpi015741 varchar(65533) NULL COMMENT “”,
kpi015742 varchar(65533) NULL COMMENT “”,
kpi015744 varchar(65533) NULL COMMENT “”,
kpi015743 varchar(65533) NULL COMMENT “”,
kpi015745 varchar(65533) NULL COMMENT “”,
kpi015788 varchar(65533) NULL COMMENT “”,
kpi015789 varchar(65533) NULL COMMENT “”,
kpi015787 varchar(65533) NULL COMMENT “”,
kpi015790 varchar(65533) NULL COMMENT “”,
kpi015791 varchar(65533) NULL COMMENT “”,
kpi015792 varchar(65533) NULL COMMENT “”,
kpi015801 varchar(65533) NULL COMMENT “”,
kpi015803 varchar(65533) NULL COMMENT “”,
kpi015802 varchar(65533) NULL COMMENT “”,
kpi015805 varchar(65533) NULL COMMENT “”,
kpi015806 varchar(65533) NULL COMMENT “”,
kpi015804 varchar(65533) NULL COMMENT “”,
kpi015886 varchar(65533) NULL COMMENT “”,
kpi015891 varchar(65533) NULL COMMENT “”,
kpi015890 varchar(65533) NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY(kid)
COMMENT “”
DISTRIBUTED BY HASH(kid) BUCKETS 5
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “true”,
“compression” = “LZ4”,
“storage_medium” = “SSD”
);

#2

数字中有科学技术法排查这些数据统计结果是准的，但是文档中写的double类型支持科学技术化转换
4.6889575555653096E-15
1.7786000698113207E7
1.720638893690496E7
2.639674763280056E-4
1.848456461320755E7
2.7342711428346433E-14
1.076891500934741E7
1.2492774246321619E7
-5.836796081455757E-4
4.274401776648779E7
3.393166752432058E7
7.285211794721622E-15
4.800802883962265E7
1.0998644846999965E-4
1.9721621650424093E7
1.3340342800934741E7
1.6175455434568116E7
2.8276108594339628E7
3.0721155670590274E7
1.3682778882724596E7
3.569315291492124E7
2.0366558180024233E7
-2.6623148130511254E-13
1.7597831755201553E-4
-4.761596803292971E-4
1.112665913865328E7
1.0998644847000469E-5
-4.705437428587089E-17
4.464671616955166E7
1.2895569132075468E7
2.178808954440021E7
1.048498203107149E7
4.971810510117708E7
2.315764220780682E7
3.490011431911027E7
2.7472734179245286E7
8.039359108980015E-17
2.9948590443396226E7
1.8323379543448158E7
1.9797560724603641E-4
1.8202226484507527E7
1.1053645545698458E7
1.678607769811321E7
1.0998644846999493E-4
2.9202119188246496E7

#3

测试出来了，每次统计这个数值不准确
select sum ( cast (a as double )) as b from (

select “4.6889575555653096E-15” as a union select

“1.7786000698113207E7” as a union select

“1.720638893690496E7” as a union select

“2.639674763280056E-4” as a union select

“1.848456461320755E7” as a union select

“2.7342711428346433E-14” as a union select

“1.076891500934741E7” as a union select

“1.2492774246321619E7” as a union select

“-5.836796081455757E-4” as a union select

“4.274401776648779E7” as a union select

“3.393166752432058E7” as a union select

“7.285211794721622E-15” as a union select

“4.800802883962265E7” as a union select

“1.0998644846999965E-4” as a union select

“1.9721621650424093E7” as a union select

“1.3340342800934741E7” as a union select

“1.6175455434568116E7” as a union select

“2.8276108594339628E7” as a union select

“3.0721155670590274E7” as a union select

“1.3682778882724596E7” as a union select

“3.569315291492124E7” as a union select

“2.0366558180024233E7” as a union select

“-2.6623148130511254E-13” as a union select

“1.7597831755201553E-4” as a union select

“-4.761596803292971E-4” as a union select

“1.112665913865328E7” as a union select

“1.0998644847000469E-5” as a union select

“-4.705437428587089E-17” as a union select

“4.464671616955166E7” as a union select

“1.2895569132075468E7” as a union select

“2.178808954440021E7” as a union select

“1.048498203107149E7” as a union select

“4.971810510117708E7” as a union select

“2.315764220780682E7” as a union select

“3.490011431911027E7” as a union select

“2.7472734179245286E7” as a union select

“8.039359108980015E-17” as a union select

“2.9948590443396226E7” as a union select

“1.8323379543448158E7” as a union select

“1.9797560724603641E-4” as a union select

“1.8202226484507527E7” as a union select

“1.1053645545698458E7” as a union select

“1.678607769811321E7” as a union select

“1.0998644846999493E-4” as a union select

“2.9202119188246496E7” as a) a

#4

超出double精度了

#5

统计数据不要用doule类型，double类型会丢失精度。

京ICP备2022026421号-1