PostgreSQL：如何避免除以零？

Question

我试图从表 oc_cap 中获取平均占用率，但收到错误“错误：除以零”。两列中的值均为 0。 我一直在考虑使用 NULLIF(column_name,0) 但我不知道如何在下面的代码中实现它。

SELECT *, AVG((occupancy/capacity) * 100)) AS avg_occupancy_rate
FROM occu_cap
GROUP BY 1,2,3

样本数据和预期结果：

占用	容量	avg_occupancy_rate
1232	1630	75.58
0	658	null
0	0	null

Answer 1

该错误是由于 capacity 为 0 值（这可能不允许数学除法）引起的，如果您的预期结果是 0 时 capacity is 0 from ocpancy/capacity

AVG((COALESCE(occupancy / NULLIF(capacity,0), 0) * 100))

Edit

可以尝试使用CASE WHEN表达式来判断值是否为零则返回NULL

AVG(CASE WHEN capacity <> 0 AND occupancy <> 0 THEN ((occupancy::decimal/capacity * 1.0) * 100) END)

如果如果你想显示所有的列，你可以尝试使用窗口函数。

SELECT *,AVG(CASE WHEN capacity <> 0 AND occupancy <> 0 THEN ((occupancy::decimal/capacity * 1.0) * 100) END) OVER(PARTITION BY id)
FROM occu_cap

注意

如果您的占用或容量不是浮点数类型，我们需要CAST将其转换为执行 AVG 之前的浮点数

sqlfiddle