本踩坑指南视本人使用情况和本人心情不定期更新,很多是独家解决方案。如有更优解,欢迎在底下留言指出,共同探讨!

1. divide的分母为0判断无效

select divide(12.3456, 0);

返回是正常的,但是如果是正常数据库里面的内容

select divide(cost_sum, new_100_num)

在数据库字段中new_100_num的值为0时,会报错: DB::Exception: Division by zero: while executing 'FUNCTION divide 只能通过if子句来解决:

select
    if(cost_sum=0 or new_100_num = 0, cast(0 as Decimal(18,4)), cost_sum) / if(new_100_num = 0, 1, new_100_num) as cost_sum_new_100_num

2. round对于字段计算无效

select round(12.3456, 2)

结果是12.35是正常的,但是同样,在正常数据库的字段里面

select round(cost_sum/new_100_num, 2)
from stat.reports where cost_sum > 0 and new_100_num > 0;

结果却包括了4位小数点,原因是由于cost_sum是Decimal(18,4),解决方案是使用cast:

select cast(round(cost_sum/new_100_num, 2) as Decimal(18,2))
from stat.reports where cost_sum > 0 and new_100_num > 0;

3. if子句类型要一致

如果你的cost_sum是Decimal(18,4),则以下语句会报错:

select cast(if(cost_sum=0 or new_100_num = 0, 0, cost_sum) / if(new_100_num = 0, 1000000000, new_100_num) as Decimal(18,2)) as cost_sum_new_100_num

DB::Exception: Incompatible types of arguments corresponding to two conditional branches: while executing 是由于手动赋值的0这个数字是整型,而cost_sum是Decimal(18,4),需要做一层转化:

cast(if(cost_sum=0 or new_100_num = 0, cast(0 as Decimal(18,4)), cost_sum) / if(new_100_num = 0, 1000000000, new_100_num) as Decimal(18,2)) as cost_sum_new_100_num

4. json结果类型不匹配

对于整型浮点型字段类型,在指定FORMAT JSON后返回的json数据对于字段值会变成字符串,对于数据类型敏感的语言和业务场景,需要手动处理转为整形。

如果您觉得您在我这里学到了新姿势,博主支持转载,姿势本身就是用来相互学习的。同时,本站文章如未注明均为 hisune 原创 请尊重劳动成果 转载请注明 转自: clickhouse踩坑指南 - hisune.com