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