案例
电话接听漏接表:tel_info
person | day | recevice | miss |
---|---|---|---|
A | 2018-01-18 | 10 | 0 |
B | 2018-01-18 | 0 | 5 |
C | 2018-01-18 | 4 | 6 |
D | 2018-01-18 | 0 | 0 |
计算每个人的漏接率,大家肯定会直接写SQL:
SELECT
person,
round(miss::NUMERIC/(recevice+miss)::NUMERIC, 2) miss_rate
FROM
tel_info
ERROR: division by zero
#执行提示除零错误
教一招SQL 解决除零错误:
SELECT
person,
CASE (recevice+miss)
WHEN 0 THEN 1.00
ELSE round(miss::NUMERIC/(recevice+miss)::NUMERIC, 2) END miss_rate
FROM
tel_info
注:miss::NUMERIC 为类型转化,因为miss 与 recevice 都是整形,整形除整形还是整形。