clickhouse 条件语句内decimal除0报错处理

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