SQL整数除法导致取消率计算结果异常问题求助

这是整数除法截断导致的问题：

• count_cancelled和count_total都是整数类型，在SQL中整数除以整数的结果会自动截断小数部分，只保留整数。比如2023-12-23的count_cancelled=2、count_total=4，2/4的整数运算结果是0，再乘以100还是0，最终round后显示为0.00。

修复方案 #

将其中一个操作数转换为浮点/数值类型，强制触发浮点除法：

修改后的SQL示例1：

with counted_status as (SELECT count(*) as count_total, 
  count(*) filter (where status like 'completed%') as count_completed,
  count(*) filter (where status like 'cancelled%') as count_cancelled,
  request_at as Date
  FROM rides 
 GROUP BY Date)
select Date, round(1.0 * count_cancelled / count_total * 100, 2) as Cancellation_rate
from counted_status
where Date >= '2023-12-23' and Date <= '2023-12-25'

修改后的SQL示例2（用类型转换）：

with counted_status as (SELECT count(*) as count_total, 
  count(*) filter (where status like 'completed%') as count_completed,
  count(*) filter (where status like 'cancelled%') as count_cancelled,
  request_at as Date
  FROM rides 
 GROUP BY Date)
select Date, round(count_cancelled::numeric / count_total * 100, 2) as Cancellation_rate
from counted_status
where Date >= '2023-12-23' and Date <= '2023-12-25'

调整后就能得到正确的取消率，比如2023-12-23会显示50.00，2023-12-25会显示33.33。

内容的提问来源于stack exchange，提问作者jingchun liu