clickhouse decimal类型除0异常
最近使用clickhouse做一个报表页面出现一个问题。
SELECT
sum( impressions) impressions,
sum( clicks ) clicks,
if(impressions>0,clicks/impressions,0) clickRate,
sum( cost ) cost,
if(clicks>0, cost/clicks,0) clickCost
FROM
amazon_ad_report_campaign
GROUP BY campaign_id
HAVING campaign_id in (214011130363);
出现如下报错
There is no supertype for types Decimal(38, 2), Float64 because some of them have no lossless conversion to Decimal
意思就是Decimal 和Float类型这间不能直接进行转换
错误原因在 if(clicks>0, cost/clicks,0) clickCost 这里
于是我单独对这个sql进行测试
select toDecimal32(0,2) as cost,toInt64(0) as clicks , if(clicks>0, cost/clicks,0) clickCost;
-- Exception: Division by zero
-- Decimal除零会异常
实际上按理说 上面不会执行到 cost/clicks,因此不会触发Division by zero才对。
条件运算符(如果/条件/三元)惰性计算问题
条件运算符(如果/条件/三元)始终计算所有分支这里给出了部分解释。
目前,惰性计算仅适用于纯常量,不适用于常量表达式
如 SELECT 1 ? 0 : 1 % 0 不会执行1%0
而 SELECT 1>0 ? 0 : 1 % 0 则会执行 1%0
也就是说 如果条件函数中条件不是表达式,那么其实每个分支都会被执行,但是只是返回正确分支得数据。
NaN(非数据)和Inf(无穷)和允许除零数据
实际上clickhouse 有定义NaN(非数据)和Inf(无穷)两种数据,允许计算时除数为0.
select toDecimal32(0,2) as cost,toInt64(1) as clicks , if(clicks>0, cost/clicks,0) clickCost;
-- 没有异常
-- 0.00,1,0.00
select toFloat64(1.0)/0;
-- 可以执行 值为 ∞
select toInt64(1)/0;
-- 可以执行 值为 ∞
select toDecimal32(2,0)/0;
-- 执行异常 Exception: Division by zero
测试部分数据除0,发现数字类型中Decimal会报错,其他数字类型 int,float式可以除0.
个人对decimal除0处理方案
因此对应decimal除0可以有两种处理方案(个人方案)
方案1:避免除0
select toDecimal32(0,2) as cost,toInt64(1) as clicks , divide(cost,if(clicks>0,clicks,toInt64(9223372036854775807))) clickCost
-- 这种将被除数设置为很大,使得出的结果接近为0
方案2:转换类型
对于计算精度要求不高的,可以将 Decimal转换为 float类型进行计算
select toDecimal32(0,2) as cost,toInt64(1) as clicks ,
if(clicks>0,divide(toFloat64(cost),clicks),0) clickCost