집계 함수와 NULL 처리 - SQL전문가 가이드 p. 265

 

p. 265 – 집계 함수와 NULL 처리

 

CASE 표현 사용시 ELSE 절을 생략하게 되면 Default 값이 NULL이다. NULL은 연산의 대상이 아닌 반면, SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0 END) 처럼 ELSE 절에서 0(Zero)을 지정하면 불필요하게 0이 SUM 연산에 사용되므로 자원의 사용이 많아진다. 같은 결과를 얻을 수 있다면 가능한 ELSE 절의 상수값을 지정하지 않거나 ELSE 절을 작성하지 않도록 한다. 같은 이유로 Oracle의 DECODE 함수는 4번째 인자를 지정하지 않으면 NULL이 Default로 할당된다.

 

많이 실수하는 것 중에 하나가 Oracle의 SUM(NVL(SAL,0)), SQL Server의 SUM(ISNULL(SAL,0)) 연산이다. 개별 데이터의 급여(SAL)가 NULL인 경우는 NULL의 특성으로 자동적으로 SUM 연산에서 빠지는 데, 불필요하게 NVL/ISNULL 함수를 사용해 0(Zero)으로 변환시켜 데이터 건수만큼의 연산이 일어나게 하는 것은 시스템의 자원을 낭비하는 일이다.

 

리포트 출력 때 NULL이 아닌 0을 표시하고 싶은 경우에는 NVL(SUM(SAL),0)이나, ISNULL(SUM(SAL),0) 처럼 전체 SUM의 결과가 NULL인 경우(대상 건수가 모두 NULL인 경우)에만 한 번 NVL/ISNULL 함수를 사용하면 된다.

 

예제) 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장을 작성한다. 데이터가 없는 경우는 0으로 표시한다.

 

*SIMPLE_CASE_EXPRESSION 조건

 

SELECT TEAM_ID,

         NVL(SUM(CASE POSITION WHEN ‘FW’ THEN 1 ELSE 0 END), 0) FW,

NVL(SUM(CASE POSITION WHEN ‘MF’ THEN 1 ELSE 0 END), 0) MF,

NVL(SUM(CASE POSITION WHEN ‘DF’ THEN 1 ELSE 0 END), 0) DF,

NVL(SUM(CASE POSITION WHEN ‘GK’ THEN 1 ELSE 0 END), 0) GK,

COUNT(*) SUM

FROM PLAYER

GROUP BY TEAM_ID;

 

*SIMPLE_CASE_EXPRESSION 조건

-       CASE 표현의 ELSE 0, ELSE NULL 문구는 생략 가능하므로 다음과 같이 조금 더 짧게 SQL 문장을 작성할 수 있다. Default 값인 NULL이 적용됨.

 

SELECT TEAM_ID,

         NVL(SUM(CASE POSITION WHEN ‘FW’ THEN 1 END), 0) FW,

NVL(SUM(CASE POSITION WHEN ‘MF’ THEN 1 END), 0) MF,

NVL(SUM(CASE POSITION WHEN ‘DF’ THEN 1 END), 0) DF,

NVL(SUM(CASE POSITION WHEN ‘GK’ THEN 1 END), 0) GK,

COUNT(*) SUM

FROM PLAYER

GROUP BY TEAM_ID;

 

 

*SEARCHED _CASE_EXPRESSION 조건

 

SELECT TEAM_ID,

         NVL(SUM(CASE WHEN POSITION = ‘FW’ THEN 1 END), 0) FW,

NVL(SUM(CASE WHEN POSITION = ‘MF’ THEN 1 END), 0) MF,

NVL(SUM(CASE WHEN POSITION = ‘DF’ THEN 1 END), 0) DF,

NVL(SUM(CASE WHEN POSITION = ‘GK’ THEN 1 END), 0) GK,

COUNT(*) SUM

FROM PLAYER

GROUP BY TEAM_ID;

 

 

예제) GROUP BY 절 없이 전체 선수들의 포지션별 평균 키 및 평균 키를 출력할 수도 있다.

SELECT ROUND(AVG(CASE WHEN POSITION = ‘MF’ THEN HEIGHT END), 2) 미드필더,

ROUND(AVG(CASE WHEN POSITION = ‘FW’ THEN HEIGHT END), 2) 포워드,

ROUND(AVG(CASE WHEN POSITION = ‘DF’ THEN HEIGHT END), 2) 디펜더,

ROUND(AVG(CASE WHEN POSITION = ‘GK’ THEN HEIGHT END), 2) 골키퍼,

ROUND(AVG(HEIGHT),2) 전체평균키

FROM PLAYER;