SQL查询中算术运算计算cr_rate转化率结果异常如何解决

调试SQL查询时遇到算术运算异常，用于计算转化率的cr_rate字段输出与预期偏差较大，查询返回的前三个字段campaign_step、email_opened、contracts统计值均正确，仅运算生成的转化率字段不符合预期，例如某行预期返回3.86，实际结果偏差明显。
原查询代码如下：

select activecampaign.campaign_step,
       count(activecampaign.email_name) as email_opened,
       count(contracts.uuid) as contracts,
       count(contracts.uuid)/count(activecampaign.email_name)*100 as cr_rate

from il.mktg_activecampaign_email_opens activecampaign

full outer join bl.leads leads
    on leads.uuid = activecampaign.uuid

full outer join bl.contracts contracts
    on leads.uuid = contracts.uuid
    and contracts.conversion_channel = 'crm'

group by activecampaign.campaign_step

整数除法隐式截断是这类问题的最高发诱因：
PostgreSQL、SQL Server等绝大多数数据库引擎的规则是：两个整数类型值做除法时，会自动截断小数部分返回整数结果，不会保留浮点精度。比如预期计算结果为0.0386的除法，整数运算逻辑下会直接返回0，乘以100后结果为0，和预期的3.86偏差极大。
如果调整类型后结果仍不对，再排查全外连接的隐式重复问题：如果同一个uuid在左右表存在多条匹配记录，join后会生成笛卡尔积，哪怕当前观察到两个count的数值和预期一致，也可能存在分子分母同比例放大、比值看似正确但统计口径错误的隐患。

• 优先修复整数截断问题：将除法运算中任意一个运算数显式转换为高精度数值类型，强制引擎执行浮点除法保留小数位，通用SQL写法如下：

select activecampaign.campaign_step,
       count(activecampaign.email_name) as email_opened,
       count(contracts.uuid) as contracts,
       -- 显式把分子转为decimal类型，避免整数截断
       cast(count(contracts.uuid) as decimal(18,4))/count(activecampaign.email_name)*100 as cr_rate

from il.mktg_activecampaign_email_opens activecampaign

full outer join bl.leads leads
    on leads.uuid = activecampaign.uuid

full outer join bl.contracts contracts
    on leads.uuid = contracts.uuid
    and contracts.conversion_channel = 'crm'

group by activecampaign.campaign_step

如果是PostgreSQL引擎，也可以简写为count(contracts.uuid)::numeric / count(activecampaign.email_name)*100。

• 校验连接重复问题：如果调整类型后结果仍有偏差，把普通count替换为去重计数，避免多表连接生成的重复行干扰统计，例如将计数逻辑改为count(distinct activecampaign.email_name)、count(distinct contracts.uuid)，确保分子分母的统计口径都是独立去重后的实体数。

内容的提问来源于stack exchange，提问作者Yaniss Illoul