[wiki_log]Clickhouse decimal--破解零除魔咒（Division by zero）

十口一

已于 2022-04-25 10:21:52 修改

阅读量2.7k

点赞数 4

文章标签：数据库大数据数据仓库 big data

于 2022-04-18 19:13:53 首次发布

本文链接：https://blog.csdn.net/ONE__P/article/details/124256890

版权

一、引言

想要知道解决方法可直接看方法三

业务场景：

在计算货物数量的时候采用总额除于单价得到数量

涉及的表结构：

订单表【

订单编号(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

结果就不再继续暴零除异常，但是这样也会带来一些其他的问题，就是每一次都需要而外多一层计算，导致下效率应该是下降（这个只是我的猜想，没有进行测试验证，希望之后有时间能进行）