2013年9月22日 星期日

NULL對運算上之影響

NULL為資料庫相當特殊資料值,需特別注意每個函數運算/處理上之模式,大部分的彙總函數是針對非NULL資料進行彙總運算,如採用COUNT(expr)指令時,是將expr運算式(或欄位)中非NULL值的資料筆數計數之結果,其他如加總(Sum)、平均值(Average)、標準差(Standard Devation)或變異數(Variance)等常用彙總函數也適用;但COUNT(*)計數函數則傳回所有資料列筆數,當然包含NULL值之資料。以下以平均值運算為例進行說明,SQL及執行結果如下:
SELECT COUNT(*)                 " 1. COUNT(*)"
     , COUNT(VAL)             " 2. COUNT(VAL)"   
     , SUM(VAL)               " 3. SUM(VAL)" 
     , SUM(VAL) / COUNT(*)    " 4. SUM(VAL) / COUNT(*)"
     , SUM(VAL) / COUNT(VAL)  " 5. SUM(VAL) / COUNT(VAL)"
     , AVG(VAL)                " 6. AVG(VAL)"
     , AVG(NVL(VAL,0))        " 7. AVG(NVL(VAL,0))"  
FROM  (
  SELECT 10 VAL FROM DUAL --1. 10
  UNION ALL
  SELECT 0 FROM DUAL      --2. 0
  UNION ALL
  SELECT NULL FROM DUAL  --3. NULL
  )

將每個運算欄位整理及說明如下表。
No
運算式
結果
說明
1
COUNT(*)
3
所有筆數
2
COUNT(expr)
2
NULL值會自動剔除,不計入
3
SUM(expr)
10
NULL值會自動剔除,且並不會影響結果。無需將NULL轉成0
4
SUM(expr) / COUNT(*)
3.33310/3

5
SUM(expr) / COUNT(expr)
5    10/2

6
AVG(expr)
5    10/2
請特別注意AVG函數其運算模式為SUM(expr)/COUNT(expr),分母之資料筆數是剔除NULL 值的數量。
7
AVG(NVL(expr,0))
3.33 10/3
AVG函數自動剔除NULL值,影響運算結果,需將NULL轉成0
其中第6AVG平均值之結果,與一般預期不同,請注意運算模式與常用數學模式之差異,當具有NULL值時其結果兩者大相逕庭,建議採行第4項或第7AVG(NVL(expr,0))之作法,以解決NULL值之問題
因此當使用彙總函數時,請注意NULL值可能影響,並視情況進行NULL值之轉換。

沒有留言:

張貼留言