집계 함수 사용 시 주의사항

SELECT NUM
     , VALUE
FROM (
      SELECT TRUNC(DBMS_RANDOM.VALUE(1,1000)) NUM 
           , DECODE(MOD(TRUNC(DBMS_RANDOM.VALUE(1,1000)) ,2),0,NULL 
                   ,TRUNC(DBMS_RANDOM.VALUE(1,1000))) VALUE  --짝수면 NULL
        FROM DUAL
      CONNECT BY LEVEL <=10
     );

짝수면 NULL이 나오게 했다.

SELECT 
     DISTINCT AVG(VALUE) OVER() AVG1  --NULL포함 안함
     , AVG(NVL(VALUE,0)) OVER() AVG2  -- NULL포함
     , SUM(VALUE) OVER() / COUNT(VALUE) OVER() AVG3 --NULL포함 안함
     , SUM(VALUE) OVER() / COUNT(*) OVER()     AVG4 --카운트만 NULL포함
     , SUM(VALUE) OVER() / COUNT(NVL(VALUE,0)) OVER() AVG5 -- 카윤트만 NULL 포함
     , COUNT(VALUE) OVER() CN1
     , COUNT(1) OVER() CN2
FROM (
      SELECT TRUNC(DBMS_RANDOM.VALUE(1,1000)) NUM 
           , DECODE(MOD(TRUNC(DBMS_RANDOM.VALUE(1,1000)) ,2),0,NULL 
                   ,TRUNC(DBMS_RANDOM.VALUE(1,1000))) VALUE  --짝수면 NULL
        FROM DUAL
      CONNECT BY LEVEL <=10
     );

기본적으로 집계함수는 NULL 값을 제외한다. 따라서 AVG함수나, COUNT함수는 주의해서 사용해야 한다.

NULL인 값을 포함해서 계산을 하고 싶은 경우도 많기 때문이다.

 

추가로 집계함수의 NULL 처리 방식을 이해하면 아래 중 무엇이 잘못된 사용 방식인지 판별할 수 있다.

SELECT SUM(NVL(123,0)) FROM DUAL;
SELECT NVL(SUM(123), 0) FROM DUAL;

1번 째의 경우 나름 생각한다고 NULL처리를 했지만 DB에 불필요한 부하를 주게 된다.

내부적으로 NULL을 처리하는데 개발자가 NVL을 또 주었으니 불필요한 연산이 들어가게 된다.

이게 행이 많고, 집계함수를 몇 십개 쓴다고 생각해보자.