一、引言
想要知道解决方法可直接看方法三
业务场景:
在计算货物数量的时候采用总额除于单价得到数量
涉及的表结构:
订单表【
订单编号(Int32)、
总额Decimal64(8)、
单价Decimal64(8)
、.....】
写成sql也就是:
select `总额`/`单价` as `数量` from default.`订单表`;
然后订单有些赠品单价有时可能存在零值,加上判断语句
select if(`单机`=0,`总额`,`总额`/`单价` )as `数量` from default.`订单表`;
看似没有问题,结果clickhouse竟然还是报了零除错误
DB::Exception: Division by zero: while executing 'FUNCTION divide(....)
二、为什么
猜测一 除法会丢弃最低有效位数(并不是真正导致零除异常的原因)
查看官网的文档
发现Decimal对于除法,最低有效数字会被丢弃(不舍入),并且除法:S = S1。
也就是说计算过程实际上是:
在对两个Decimal 进行除法操作时会先同步两者的位数,以分子的精度为准,然后分母存在这样的数据(0.0000000000004),就会舍去有数字,变成0.00000 所以出现零除错误。
猜测二 表达式非惰性(大概率是导致零除异常的原因)
-- 业务场景
select if(b=0,0,a/b)
from (
select toDecimal128(0.002,3) as a ,
toDecimal128(0.002,2) as b
) as demo3
解决方案
一般的表达式会具备逻辑段路(惰性),当满足第一个条件之后就不去计算第二个情况;但是click house在表达式执行之前返回所有可能结果
三、怎么做
其实Decimal本身已经不是很准确了,因为精度的计算会导致舍入的情况。这个问题不仅存在clikchouse,诸如mysql也是类似的处理方式。
有几种方法,
方法一、存储时倍数和精度分开储存,统一扩大相同倍数
,将所有的Decimal化作整数处理,统一乘上10的倍数,计算结果再除于十的倍数
方法二、扩大 分子的精度
方法三、表达式提前返回值之前内嵌一层(隔离变量)
问题就是当分子在不可能出现的零的情况下,提前进行计算,那么可以考虑在嵌套一层表达式,让它预执行的时候就开始替换变量。
-- demo 破解零除错误 可执行
select if(b=0,0, a/if(b=0,999,b))
from (
select toDecimal128(0.002,3) as a ,
toDecimal128(0.002,2) as b
) as demo1
-- 比对 报错(Division by zero)
select if(b=0,0, a/if(b=0,0,b))
from (
select toDecimal128(0.002,3) as a ,
toDecimal128(0.002,2) as b
) as demo2
结果就不再继续暴零除异常,但是这样也会带来一些其他的问题,就是每一次都需要而外多一层计算,导致下效率应该是下降(这个只是我的猜想,没有进行测试验证,希望之后有时间能进行)
四、后续
因为类型转化会很容易出现各种精度丢失问题。
Excessive digits in integer part will lead to an exception(类型转换是昂贵的操作)
在设计数据模型的时候,需要对字段类型进行取舍,考虑相关应用场景:如果对数据的严谨性要求较高,可采用Decimal,如果是经常经常计算的可用Float 吗,但是float本身就不准确,这就会失去一些精度,但是采用方法三不会这样的问题, 但是不可避免的带来其他的问题,例如效率低下等。
未完待续......
参考资料:
clickhouse官方wiki decimal类型
如果有问题也可以在下方留言讨论
陈棋烂柯,一甲子矣
转载须获得本人许可