我试图从表occu_cap中获得平均占用率,但我得到了错误"错误:除以零".两列中都有0个值.
SELECT *, AVG((occupancy/capacity) * 100)) AS avg_occupancy_rate
FROM occu_cap
GROUP BY 1,2,3
样本数据和预期结果:
occupancy | capacity | avg_occupancy_rate |
---|---|---|
1232 | 1630 | 75.58 |
0 | 658 | null |
0 | 0 | null |
我试图从表occu_cap中获得平均占用率,但我得到了错误"错误:除以零".两列中都有0个值.
SELECT *, AVG((occupancy/capacity) * 100)) AS avg_occupancy_rate
FROM occu_cap
GROUP BY 1,2,3
样本数据和预期结果:
occupancy | capacity | avg_occupancy_rate |
---|---|---|
1232 | 1630 | 75.58 |
0 | 658 | null |
0 | 0 | null |
错误是由于capacity
是0
值(这可能不允许数学除法),如果您的预期结果是0
,那么capacity
是occupancy/capacity
的0
AVG((COALESCE(occupancy / NULLIF(capacity,0), 0) * 100))
Edit
您可以try 使用CASE WHEN
表达式来判断值是否为零,然后返回NULL
AVG(CASE WHEN capacity <> 0 AND occupancy <> 0 THEN ((occupancy::decimal/capacity * 1.0) * 100) END)
如果要显示所有列,可以try 使用窗口功能.
SELECT *,AVG(CASE WHEN capacity <> 0 AND occupancy <> 0 THEN ((occupancy::decimal/capacity * 1.0) * 100) END) OVER(PARTITION BY id)
FROM occu_cap
NOTE
如果你的occupancy
或capacity
不是浮点数的类型,我们需要在做AVG
之前将其作为浮点数