[SQL] GROUPING 완벽히 이해하고 사용하기, NULL값 '총합'으로 변경

[데이터베이스 완전정복 목차]

안녕하세요

양햄찌 블로그 주인장입니다.

오늘은 그룹바이 롤업에 이어서 GROUPING과 GROUPING_ID를 알아보려해요.

일단 GROUPING부터~~~

 

※ 사전에 숙지되어 있어야 할 부분!

GROUP BY와 ROLLUP에 대한 개념이 없으면 학습에 어려움이 있을 수 있어요!

아래 3개의 포스팅은 숙지하고 옵시당!

 

■ CASE WHEN 절

2020.12.12 - [별걸다하는 IT/데이터베이스 Database] - [디비 SQL 오라클] SQL에도 IF분기문이 있다? CASE WHEN THEN 문법 알아보기, CASE구문 사용법

 

■ GROUP BY와 HAVING

2020.07.22 - [별걸다하는 IT/데이터베이스 Database] - [오라클, MySQL] 컬럼을 그룹지어 통계를 파악하자. group by와 having절 사용법, 사용 가능 대표 집계함수, 특징

 

■ ROLLUP과 GROUP BY

2020.11.24 - [별걸다하는 IT/데이터베이스 Database] - [SQL] ROLLUP과 GROUP BY. 집계 통계에 사용되는 롤업 함수 사용법을 알아보자

 

그럼 고고~

 

[목차]

1. grouping은 무엇인가

2. GROUPING 사용 상황

   - 2.1. GROUP BY ROLLUP의 문제 인식

   - 2.2. ROLLUP 집계 컬럼이 데이터가 없는 것도 아닌대 NULL로 보여지는게 문제야!

   - 2.3. NULL일 경우 데이터를 대체해주는 NVL을 썼을 때 문제

   - 2.4. GROUPING과 CASE WHEN 사용한 해결

3. 주의할점

 

1. GROUPING은 무엇인가?

GROUPING은 ROLLUP이나 CUBE, GROUPING SETS 등의 그룹함수에 의해 컬럼 값이 소계나 총합등 집계된 데이터일 경우 1을 리턴하고 만약 집계된 데이터가 아니면 0을 리턴하는 함수입니다.

반응형

2. GROUPING 사용 상황

2-1. GROUP BY ROLLUP의 문제 인식

음 무슨말이지 싶다구요?

만약에 내가 어떤 회사의 CEO라고 가정해봅시다. 회사는 꽤 커서 여러 부서가 있는데요,

부서명은 간단하게 코드로 10부서, 20부서, 30부서 이렇게 정해져 있어요

10부서에는 개발자가 있고, 20부서에는 경영에 관련된 인사팀 재무팀 기획팀이 있고, 30부서에는 영업팀, 판매팀이 있고, 40부서에는 뭐.. 이런식으로 있다고 칩시다!

근데 나는 부서별로 인건비가 어떻게 드는지,  또 부서 내에서 직무에 따라 인건비가 얼마가 나가는지 통계를 내고 싶은거예요~~

이 데이터를 통계내는 쿼리를 샘플 스키마 EMPLOYEES 테이블에서 작성해볼게요.

SELECT DEPARTMENT_ID 부서번호, JOB_ID 직무, SUM(SALARY) 인건비계
FROM EMPLOYEES e --직원 테이블
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID) --(부서, 직무)를 기준으로 통계를 내라!
ORDER BY DEPARTMENT_ID; --정렬은 부서 오름차순순으로

 

이렇게 작성하면 되겠죠?

그림1

결과는 왼쪽과 같습니다!

보면 14번째 라인은 부서 50에 대한 총 인건비(64,300+55,700+36,400 = 156,400)일거고

16번째 라인은 부서 60에 대한 총 인건비일거예요 (28,800)

 

근데 이게 NULL로 되어있으니까 소계값인지,, 실제 직무가 NULL인건지 헷갈려요

만약에 부서는 정해졌는데 직무가 아직 정해지지 않은 사람이면 똑같이 직무가 NULL로 표기될텐데 이게 한 그 직무에 대한 데이터값인지, ROLLUP에 의한 소계 값인지 어떻게 구분하나요?

 

[파란색 바탕색]

실제 파란색 바탕색 부분을 보면 (32번 라인, 33번 라인)

첫번째 컬럼도 NULL 두번째 컬럼도 NULL인데 

인건비는 7000원, 691,416으로 두 행이 다르네요!

 

이게 바로 이런 케이스죠..

신입사원이 들어와서 연봉은 책정됐으나 수습기관이라 발령될 부서와 직무가 정해지지 않았다면 32번 행처럼 기록되겠죠!

 

이럴 경우 두 데이터간 혼동이 있을 수 있어요.

어떻게 해결하면 좋을까요?

 

2-2. ROLLUP 집계 컬럼이 데이터가 없는 것도 아닌대 NULL로 보여지는게 문제야!

즉 ROLLUP 집계 결과가 집계컬럼이 아니면 모두 NULL로 표기되는 거 때문에

컬럼이 NULL인 것의 개수인지, 아님 집계 결과때 나오는 NULL인 것인지 헷갈려요.

 

부서코드는 어떤 부서인지 눈에 잘 안들어오니까 이번엔 부서명으로 부서별 인건비 쿼리를 만들어서 결과를 확인해볼까요? 부서명은 DEPARTMENTS 테이블에 있습니다. 위에 언급했듯이 신입사원일 경우 부서가 미지정일 수 있으니 레프트 조인을 해줍시다. 

SELECT d.DEPARTMENT_NAME 부서명, SUM(e.SALARY) 부서별인건비
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID(+)  -- LEFT OUTER JOIN
GROUP BY ROLLUP(d.DEPARTMENT_NAME)
ORDER BY d.DEPARTMENT_NAME;

요렇게 만들어줬어요. 조회 결과를 볼게요~

그림2

부서명 값 자체가 NULL인 레코드랑

ROLLUP 집계값인데 부서명 값이 NULL로 표기되는 레코드와 

데이터가 헷갈리기 때문에 NULL대신 각각 '부서가 없는', '총합(total)' 이렇게 빨간색글자처럼 표기해주고 싶어요. 

어떤 방법이 있을까요?

 

2-3. NULL일 경우 데이터를 대체해주는 NVL을 썼을 때 문제

데이터가 NULL이면 치환해주는 NVL함수를 쓰면 되지 않을까? 생각해볼 수 있어요.

SELECT NVL(d.DEPARTMENT_NAME, '부서가 없는') 부서명, SUM(e.SALARY) 부서별인건비
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)
GROUP BY ROLLUP(d.DEPARTMENT_NAME)
ORDER BY d.DEPARTMENT_NAME;

하지만 NVL(d.DEPARTMENT_NAME,'부서가 없는')을 사용해주면 13라인에 해당하는 총 집계 함수 부분 또한 '부서가 없는'으로 치환되는 에러가 있습니다. 12번째 행은 부서가 배정되지 않은 신입사원의 인권비가 맞지만, 13번째 행은 전체 인권잖아요 치환되면 안돼요!

그림3

2-4. GROUPING과 CASE WHEN 사용한 해결

이런 경우 그룹핑된 값이 NULL인지 아닌지 판별을 먼저 해줌으로써 해결할 수 있습니다. 

GROUPING은 소계나 총합등 집계된 데이터일 경우 1을 리턴하고 만약 집계된 데이터가 아니면 0을 리턴하기 때문입니다! 

--OK!
SELECT CASE 
	WHEN GROUPING(d.DEPARTMENT_NAME) = 1  THEN '합계 TOTAL' 
	WHEN d.DEPARTMENT_NAME IS NULL THEN '부서가 없는' ELSE d.DEPARTMENT_name
	END 부서명, SUM(e.SALARY)
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID (+)
GROUP BY ROLLUP(d.DEPARTMENT_NAME);

그룹핑된 값이 NULL이라면 합계로 그냥 컬럼 값이 NULL인거면 '부서가 없는'으로 치환해주기! 

혹시 CASE WHEN에 대한 구문 활용법이 궁금하신 분들은 이 포스팅 링크를 참고해주세요.

그림4 (성공!)

짠! 원하는 모습으로 집계가 된 것을 확인할 수 있어요.

 

3. 주의할 점

--WRONG
SELECT CASE 
	WHEN GROUPING(d.DEPARTMENT_NAME) = 1  THEN '합계 TOTAL' 
	WHEN GROUPING(d.DEPARTMENT_NAME) != 1 THEN '부서가 없는' ELSE d.DEPARTMENT_name
	END 부서명, SUM(e.SALARY)
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID (+)
GROUP BY ROLLUP(d.DEPARTMENT_NAME);

이렇게 작성하면 어떨까요? '그림4'결과와 동일하게 나올까요?

GROUPING은 집계데이터와 NULL을 구분해 주는 것이 아닌,

ROLLUP, CUBE, GROUPING_SETS 함수가 말아준 총 값(소계,집계)인지 아닌지를 이분법적으로 구분해주는 것이기 때문에,

group by로 취합된 값은 집계데이터로 판단되지 않아 NULL과 동일시 됩니다.

SELECT d.DEPARTMENT_NAME 부서명, GROUPING(d.DEPARTMENT_NAME) 부서명그룹핑값, SUM(e.SALARY) 부서별인건비
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)
GROUP BY ROLLUP(d.DEPARTMENT_NAME)
ORDER BY d.DEPARTMENT_NAME;

실제 grouping 값을 한 번 뽑아보면,

맨 마지막 집계에 해당하는 13번째 줄 빼고 모두 0의 값을 갖는 것을 확인할 수 있어요.

--1. WRONG
CASE 
WHEN GROUPING(d.DEPARTMENT_NAME) = 1  THEN '합계 TOTAL' 
WHEN GROUPING(d.DEPARTMENT_NAME) != 1 THEN '부서가 없는' ELSE d.DEPARTMENT_name
END 부서명

--2. WRONG
CASE 
WHEN GROUPING(d.DEPARTMENT_NAME) = 1  THEN '합계 TOTAL' 
WHEN GROUPING(d.DEPARTMENT_NAME) = 0 THEN '부서가 없는' ELSE d.DEPARTMENT_name
END 부서명

--3. RIGHT
SELECT CASE 
WHEN GROUPING(d.DEPARTMENT_NAME) = 1  THEN '합계 TOTAL' 
WHEN d.DEPARTMENT_NAME IS NULL THEN '부서가 없는' ELSE d.DEPARTMENT_name
END 부서명, SUM(e.SALARY)

즉 데이터가 없는 NULL과 집계의 NULL을 구분해주기 위해서는 3번 방식을 써야 옳다는 것!

 

오늘은 저번 포스팅 ROLLUP에 이어서 GROUPING에 대해 알아봤어요~~

다음 포스팅에서는 GROUPING_ID에 대해 작성해볼게요

도움이 되셨다면 공감은 어떤가요?