Oracle View 中 Select 和除以零的问题

Question

我想在 Oracle 中创建一个视图来计算“利用率百分比”。

AS SELECT
  sw.SWITCH_ID,
  sw.ASSET_ID,
  sw.SYSTEMNAME,
  sw.MAX_INSTALLABLE_PORTS,
  sw.INSTALLED_PORTS,
  sw.USED_PORTS,
  (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100 AS UTIL_INSTALLED_PORTS,
  sw.RES_INFRASTRUCTURE_PORTS,
  sw.USED_INFRASTRUCTURE_PORTS,
  sw.FREE_INFRASTRUCTURE_PORTS,
  (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100 AS UTIL_INFRASTRUCTURE_PORTS,
  sw.RESERVED_DEVICE_PORTS,
  sw.USED_DEVICE_PORTS,
  sw.FREE_DEVICE_PORTS,
  (sw.FREE_DEVICE_PORTS/sw.RESERVED_DEVICE_PORTS)*100 AS UTIL_DEVICE_PORTS,
  sw.RUN_DATE

问题：有时 sw.INSTALLED_PORTS 或 sw.MAX_INSTALLABLE_PORTS 可以为 NULL（对于其他 UTIL 行也是如此）。

有没有什么好的方法可以做类似的事情：

if (sw.INSTALLED_PORTS or sw.MAX_INSTALLABLE_PORTS == null) 
      UTIL_INSTALLABLE_PORTS = null 
else (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100 AS UTIL_INSTALLABLE_PORTS,

Answer 1

或更短一点：

sw.INSTALLED_PORTS/NULLIF(sw.MAX_INSTALLABLE_PORTS,0)

问候，
抢。

Answer 2

除以 NULL 与除以零不同（正如您在标题中引用的问题一样）。

从对偶中选择 1/null = null
select null/null from Dual = null

因此，您将通过(sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100自动获得您想要的内容。

我认为，问题出在 sw.MAX_INSTALLABLE_PORTS 为零时。在这种情况下，您可以使用以下内容：

case
  when sw.MAX_INSTALLABLE_PORTS = 0 then null
  else (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100
end

Answer 3

CASE
   WHEN sw.INSTALLED_PORTS IS NULL OR sw.MAX_INSTALLABLE_PORTS IS NULL THEN NULL
   ELSE (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100
END UTIL_INFRASTRUCTURE_PORTS

Answer 4

您可以使用 COALESCE() ，它从其参数中返回第一个非空值，例如：

(COALESCE(sw.INSTALLED_PORTS, 0)/COALESCE(sw.MAX_INSTALLABLE_PORTS,1))*100  
 AS UTIL_DEVICE_PORTS

请注意，我使用 1 作为分母中的合并默认值，您不需要使用 DIVIDE/0 代替。