NULL과 0 의 AVG 차이비교

조건.
    TABLE : TEST26

    YYMMDD   KEY1 VAL1       VAL2       VAL3      
    -------- ---- ---------- ---------- ----------
    19990601 A            10         10         10
    19990601 B                        0         10
    19990601 C                        0         10
    19990601 D                        0         10
    19990602 A            10         10         10
    19990602 B            10         10         10
    19990602 C            10          0          0
    19990603 A            10                      
    19990603 B            10         10         10

문제.
    1.SELECT YYMMDD,AVG(VAL1),AVG(VAL2),AVG(VAL3)
      FROM   TEST26
      GROUP BY YYMMDD;
    2.SELECT KEY1,AVG(VAL1),AVG(VAL2),AVG(VAL3)
       FROM   TEST26
       GROUP BY KEY1;
    3.SELECT YYMMDD,SUM(VAL1),SUM(VAL2),SUM(VAL3)
      FROM   TEST26
      GROUP BY YYMMDD;
    4.SELECT YYMMDD,
             SUM(VAL1) + SUM(VAL2),
             SUM(VAL1 + VAL2)
      FROM   TEST26
      GROUP BY YYMMDD;
    5.SELECT KEY1,MAX(VAL1),MIN(VAL2),COUNT(VAL3)
      FROM   TEST26
      GROUP BY KEY1;
    위의 4가지 경우에 대해서 나올 결과에 대해서 예측해 보자.

생각.
    흔히 우리는 NULL을 숫자 COLUMN인 경우 0 으로 치환하여 사용한다.
    하지만 그것이 옳은 경우가 있고 그렇지 못한 경우가 있다.
    NULL로 치환을 하건 그렇지 않건 동일한 답이 나오는 경우는
    치환해도 무관하다 하더라도 치환을 하면 답 자체가
    바뀌는 경우가 있다.
    주로 AVG 함수를 사용하는 경우는 NULL값이 ZERO 값으로 치환될 때
    결과가 달라진다.
    아주 기본적인 사항이라 어느정도 경력이 있는 사람은 다 알겠지만
    초보자는 특히 주의해야할 사항이다.
해법.
    1번 문제를 보자.
    문제를 실행시키면 다음과 같은 형식으로 답이 나올 것이다.

    YYMMDD   AVG(VAL1)  AVG(VAL2)  AVG(VAL3)
    -------- ---------- ---------- ----------
    19990601        (1)        (2)        (3)
    19990602        (4)        (5)        (6)
    19990603        (7)        (8)        (9)

   (1) 에서 (9)까지 어떤 값이 나올지를 한번쯤 생각해본 후
   실행 시켜보는 것이 바람직 할 듯하다.
   우선 (1)에 들어갈 값을 생각해보자.
   19990601 에는 A,B,C,D 4개의 KEY를 가진 RECORD가 존재한다.
   A에는 10 이라는 값이 있고 B,C,D는 NULL 이다.
   10,NULL,NULL,NULL 의 4개 값을 가지고 평균을 내면 2.5 가
   나오는가??..
   아니면 10 이 나오는가??.
   아니면 NULL 이 나오는가?..
   첫번째 2.5가 나온다는 것은 4가지중 NULL이 들어있는 3경우를
   0으로 인식하여 평균에 포함시킨다는 의미이고,
   10이 나온다는 것은 NULL이 들어있는 3개의 값을 빼고 계산한다는
   뜻이며,
   NULL이 나온다는 것은 치환없이 4개을 더한후 그 값으로 평균을
   구한다는 것이리라.
   10+NULL+NULL+NULL 은 일반적인 연산에서는 NULL이기 때문이다.
   AVG 라는 함수가 내부적으로 어떤 방식의 계산을 행하는냐에 따라서
   3가지 경우 모두 가능성이 있다.
   과연 어떤 방식으로 값을 도출할까?..
   결과를 예상해보고 예상한 결과가 맞는지 확인해보자.
   어느게 정답이고 어느것이 답이 아니다의 문제가 아니고
   AVG 라는 함수가 어떤 방식으로 연산을 행하는가를 짐작하자는
   뜻이다.
   결국 사용 경험이 정답을 맞추는 확률을 좌우 하리라.
   10 이 나온다.
   NULL인 COLUMN은 평균에 포함시키지 않는다는 뜻이다.
   값이 없다는 것과 값이 0 이라는 것을 엄연히 구분해 주고
   있는 것이다.
   이런경우에 값이 없음을 0 으로 치환하여 계산 한다면
   엉뚱한 값이 나오리라.
   반대로 0 으로 계산 되는것이 맞는데 NULL을 집어넣어 놓아도
   낭패를 볼 것이다.
   (2)는 어떤값이 나올까?..
   19990601 인 DATA의 4가지 값은 각각 10,0,0,0 의 값을 갖는다.
   당연히 0을 값으로 인식하여 계산에 포함을 시키게 되므로 2.5 가
   나온다.
   (3)은 10,10,10,10 4개의 값을 가지므로 평균은 당연히 10이
   될 것이고,
   실행 결과는 다음과 같을 것이다.

    YYMMDD   AVG(VAL1)  AVG(VAL2)  AVG(VAL3)
    -------- ---------- ---------- ----------
    19990601         10        2.5         10
    19990602         10 6.66666667 6.66666667
    19990603         10         10         10

    2번 문제도 같은 원리에 의해 예상을 해보자 이제는 누구나 쉽게 결과를
    예상할 수 있을 것이다.

    KEY1으로 SORT를 한후 살펴보자.

    YYMMDD   KEY1 VAL1       VAL2       VAL3      
    -------- ---- ---------- ---------- ----------
    19990601 A            10         10         10
    19990602 A            10         10         10
    19990603 A            10                      
    19990601 B                        0         10
    19990602 B            10         10         10
    19990603 B            10         10         10
    19990601 C                        0         10
    19990602 C            10          0          0
    19990601 D                        0         10

    결과는 아래와 같다.

    KEY1 AVG(VAL1)  AVG(VAL2)  AVG(VAL3)
    ---- ---------- ---------- ----------
    A           10         10         10
    B           10 6.66666667         10
    C           10          0          5
    D                       0         10

    KEY1이 B 인 경우의 AVG(VAL1)을 보자.
    NULL,10,10   3개 값의 AVG를 구한다.
    NULL이 연산에서 제외되고 10,10 두개가 더해진후 2로 나누어지는
    과정이 내부적으로 일어 날 것이다.

    SUM인 경우는 어떻겠는가?..
    문제3 번의 답을 예측해 보자.
    SUM 에서는 NULL값이 제외되어도 결과에 영향을 안 미치고,
    NULL이 0으로 치환되어도 결과에 영향을 안 미친다.

    YYMMDD   SUM(VAL1)  SUM(VAL2)  SUM(VAL3)
    -------- ---------- ---------- ----------
    19990601         10         10         40
    19990602         30         20         20
    19990603         20         10         10

    그렇다면 한가지 의문이 생긴다.
    SUM 함수에서는 NULL을 제외시키고 연산을 수행하는가?...
    아니면 NULL을 0으로 치환한 후 연산을 수행하는가?.
    0으로 치환하는지 NULL값을 빼버리는지 확인할 방법이 없는가?..
    아직은 확인할 방법이 없다.
    이 문제는 나중에 다시 살펴보기로 하고  4번문제를 풀어보자.
    SUM(VAL1) + SUM(VAL2) 와 SUM(VAL1 + VAL2) 는 같은 값을
    RETURN 하는가 그렇지 않은가?.
    SUM(VAL1) + SUM(VAL2) 는 쉽게 예측할 수 있으리라.
    두값을 따로 SUM한 결과를 3번에서 보았기 때문이다.
    그냥 두 결과를 더하면 된다.

    YYMMDD   SUM(VAL1)+
    -------- ----------
    19990601         20
    19990602         50
    19990603         30

    그렇다면 SUM(VAL1+VAL2)는 어떠할까?.
    SUM 함수가 VAL1과 VAL2를 PARAMETER로 따로 받는 것이 아니고
    두개가 미리 더해진 결과를 PARAMETER로 받는다는 것은 쉽게
    알아차릴 수 있으리라.
    그렇다면 결과가 어떻게 나올지 짐작할 수 있을 것이다.
    19990601 이나 19990602는 두 경우의 결과가 같다.
    SUM한 후 더하거나 두값을 더한 후 SUM을 해도 결과가 같지만
    19990603 의 경우를 보자

    YYMMDD   KEY1 VAL1       VAL2      
    -------- ---- ---------- ----------
    19990603 A            10          
    19990603 B            10         10

    위의 값을 가지고 있다.
    먼저 SUM을 따로 한 후 더한 결과는 바로 위에 있다.
    그렇다면 먼저 더한후 SUM을 해보자.
    VAL1 + VAL2 는 A의 경우 NULL이 되며 B의 경우 20이 된다.
    NULL과 20을 SUM하면 20이된다.
    따로 SUM한후 더한 결과인 30과 다르다.
    결과를 보자    

    YYMMDD   SUM(VAL1+V
    -------- ----------
    19990601         20
    19990602         50
    19990603         20
      
    실행시킨후 두 결과를 비교해보자.
    
    YYMMDD   SUM(VAL1)+ SUM(VAL1+V
    -------- ---------- ----------
    19990601         20         20
    19990602         50         50
    19990603         30         20 <- 예상대로 이것만 다르다.

    결코 NULL이 SUM 함수에서 연산에 작용했다고 생각하지마라.
    먼저 덧셈 연산이 일어난 후 SUM함수에 작용된 것이다.

    이제 5번문제를 풀어보자.
    NULL,0,10의 MAX는 어찌되고,MIN은 어찌되나?.
    COUNT는 어찌되나.

    KEY1 MAX(VAL1)  MIN(VAL2)  COUNT(VAL3
    ---- ---------- ---------- ----------
    A            10        (3)        (4)
    B            (1)         0          3
    C            10          0          2
    D            (2)         0          1
  
    (1),(2),(3),(4)외의 다른 값은 쉽게 예상 가능하다.
    (1)을 보자.
    3일치의 해당 DATA는 NULL,10,10 이다.
    MAX함수를 적용하면 어떤답이 나올까?..
    10 이 나온다,
    이것만 가지고는 NULL이 0으로 치환되었는지?.
    NULL을 제외하고 MAX를 찾았는지 알수가 없다.
    그렇다면 (2)를 보자.
    D 는 하루치 NULL 만을 해당 DATA로 가진다.
    MAX 함수가 NULL을 0으로 치환한후 연산을 수행한다면 0이
    나올 것이고,
    치환하지 않는다면 NULL이 나올 것이다.
    결과는 어떤가?.
    NULL이 나온다.
    일단 치환하지 않는다는 것을 알았다.
    그런데 NULL을 제외시키고 연산을 했기에 RETURN할 값이 없어서
    NULL이 나온 것인지,
    아니면 NULL을 포함하고 MAX를 찾아서 NULL이 나온 것인지
    또 궁금해 진다.
    NULL을 제외시킨다면 해당값이 없으므로 NULL을 RETURN 할 것이고,
    NULL을 포함시켜도 NULL 밖에 값이 없기에 NULL을 RETURN 할
    가능성이 있다.
    그렇다면 (3)을 보자.
    3일치 해당 DATA는 10,10,NULL 이다.
    이들의 MIN값은 무엇이 나오겠는가?.
    결과는 10 이다.
    NULL이 숫자보다 크다면 (1)의 값이 NULL이어야 하고,
    NULL이 숫자보다 작다면 (3)의 값이 NULL이어야 한다.
    그런데 어떤가?.
    (1)의값 (3)의값은 모두 10 이다.
    NULL이 제외 되었다는 얘기다.
    NULL이 숫자보다 크냐 작냐를 따지는 것 자체가 우스운 얘기지만
    이르자면 그렇단 얘기다.
    AVG,MIN,MAX 모두가 NULL을 제외한 DATA만으로 연산을
    수행 한다는 것을 알았다.
    마지막으로 5번 문제의  COUNT 함수의 결과를 보자.
    (4)의 답은 무엇이 나올려나?.
    10,10,NULL을 COUNT 한 것이다.
    2개 가 답이냐?...3개가 답이냐??..
    답은 2개다.
    COUNT 함수를 통해 NULL이 함수에서 제외된다는 확인을 했다.
    그렇다면 아까 미루었던 SUM함수도 NULL이 제외되고 결과가
    도출된다는 것을 알 수 있다.
    GROUP 함수 모두는 NULL DATA를 제외하고 결과를 RETURN 한다.
    AVG,MIN,MAX,COUNT 함수 등에서는 함부로 NULL을 치환해서는
    안된다는 것도 알았다.
    그렇지만 NULL의 의도가 0 이라면 반드시 치환하는 작업을
    잊어서는 안될 것이다.
    GROUP 함수는 모두 NULL DATA가 제외된후 결과가 나온다.
    그렇다면 SUM하기 전에 NVL치환이 필요 없다는 결론도
    당연히 나온다.
    SUM()이나 SUM(NVL())은 같은 결과가 나오는데 왜 NVL()함수를
    한번 더 넣어서 CPU를 생고생을 시키는가...
    그럴 필요가 없는 것이다.
뒷풀이.
    얼마동안 헷갈리는가?..
    함수를 직접 만든 사람이 아니면 사용할때마다 헷갈린다.
    아리송하다.
    - 이렇게 DATA를 놓고 직접 추적을 해보지 않은 사람이라면 -
    이제 GROUP함수를 자신있게 사용하자.