본문 바로가기

별걸다하는 IT/데이터베이스 Database

[SQL] ROLLUP과 GROUP BY. 집계 통계에 사용되는 롤업 함수 사용법을 알아보자. 롤업 합계 필드명 NULL 이름 변경

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

 

안녕하세요 양햄찌입니당

저번 포스팅에서 기본적인 그룹핑 조건절 GROUP BY와 having에 대해서 살펴보았는데 혹시 기억하시는지요~!?

그 때 마지막에 간단하게 ROLLUP에 대해 언급하고 지나갔었죠 ㅎㅎ

오늘은 그때에 이어서 ROLLUP에 대해 좀 더 알아보는 시간을 가지려고 합니다.

 

그 전에 당연히! group by에 대한 개념은 익히고 있어야 해요.

 

group by와 having절: jhnyang.tistory.com/304

 

[오라클, MySQL] 컬럼을 그룹지어 통계를 파악하자. group by와 having절 사용법, 사용 가능 대표 집계

[데이터베이스 SQL 완전정복 목차] 안녕하세요 양햄찌 블로거입니다. 오늘 살펴볼 쿼리는 group by와 having 절이예요. ㅎㅎ 업무에서 자주 사용하는 기본적인 키워드죠? GROUP BY 와 HAVING 절 사용 상황

jhnyang.tistory.com

모르시는 분은 위 링크를 참고해주세요.

ORACLE SQL - ROLLUP함수 

group by가 그루핑을 하기 위한 커맨드라면, ROLLUP은 이렇게 그룹핑 된 것을 집계하기 위해 발달한(?) 함수입니다.

그래서 rollup은 group by랑 같이 쓰여요.

group by절만 사용하면 그룹핑만 해주는데 ROLLUP까지 하면 그 그룹된 결과를 집계해줍니다. 그루핑 각각의 집계 함수 말고 전체건수라던가,, 총 합이라던가,,

이해하는데 가장 좋은것은 직접 해보는거죠!

rollup사용했을 때와 안사용했을 때 각각 결과를 확인해봅시다. 

--1. ROLLUP 없을 때
SELECT JOB_ID , SUM(SALARY) 
FROM EMPLOYEES e 
GROUP BY JOB_ID
ORDER BY JOB_ID;

--2. ROLLUP 썼을 때
SELECT JOB_ID , SUM(SALARY) 
FROM EMPLOYEES e 
GROUP BY ROLLUP (JOB_ID)
ORDER BY JOB_ID;

실습으로 이전에 다운받았던 EMPLOYEE 테이블을 사용했어요 ㅎㅎ 

왼쪽 1. 단일 GROUP BY, 오른쪽 2. GROUP BY와 ROLLUP

ROLLUP을 사용하면 결과가 한 행 더 늘어난 것을 볼 수 있어요! 20번째 줄에 보이는 저 값은 SUM(SALARY)들의 총 합입니다.

이미 GROUP BY를 통해 각 JOB_ID그룹에 따른 급여(SALARY)합을 계산했는데, ROLLUP은 이 그룹된 것들의 총 집계 결과를 보여줘요 ㅎㅎ 

ROLLUP 컬럼 어디에 하느냐에 따른 결과 / 다수컬럼에 적용했을 때 결과

물론 다수의 컬럼이 GROUP BY로 그룹핑 되었을 때도 적용 가능합니다. 

SELECT DEPARTMENT_ID 부서번호, JOB_ID 직무, COUNT(*) 직원수
FROM EMPLOYEES e 
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID ;

자 기본 그룹바이 SQL입니다. '부서번호+직무'  별로 그룹핑을 했어요.

즉 (부서번호+직무) 묶음으로 총 직원수를 세줬습니다. 20번째 줄을 보면 부서가 정해지지 않았으니 직무는 SA_REP인 직원도 한 명 있는 것을 확인할 수 있네요. (파란색박스)

 

(빨간색박스)부서번호가 80일 때를 봅시다. 부서번호가 80이면서 SH_MAN인 직원 수는 5명, 부서번호가 80이면서 SA_REP인 직원수는 총 29명인 것을 알 수 있어요.

그럼 부서번호가 80에 속하는 직원수는 모두 몇 명일까요? 물론 5+29 = 34 해서 우리가 계산할 수도 있지만, 데이터가 많을 수록, 값이 클수록 수기로 처리하는데에는 한계가 있겠죠.

 

이럴 경우 부서그룹핑은 냅두는데 직무는 신경쓰지 않고 모두 말아서 더한다라는 개념으로 그루핑절에대가 ROLLUP(직무)를 사용하면 추가적인 집계 정보를 구할 수 있습니다.

--1.
SELECT DEPARTMENT_ID 부서번호, JOB_ID 직무, COUNT(*) 직원수
FROM EMPLOYEES e 
GROUP BY DEPARTMENT_ID, ROLLUP(JOB_ID)
ORDER BY DEPARTMENT_ID ;

--2.
SELECT DEPARTMENT_ID 부서번호, JOB_ID 직무, COUNT(*) 직원수
FROM EMPLOYEES e 
GROUP BY ROLLUP(DEPARTMENT_ID), JOB_ID
ORDER BY DEPARTMENT_ID ;

--3.
SELECT DEPARTMENT_ID 부서번호, JOB_ID 직무, COUNT(*) 직원수
FROM EMPLOYEES e 
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID ;

롤업을 job_id에 말았을 때, department_id에만 말았을 때, 두 컬럼 모두에 말았을 때를 각각 비교해볼게요.

 

각각 하나씩 결과를 보여드릴테니 비교해보세요!

 

1. GROUP BY DEPARTMENT_ID, ROLLUP(JOB_ID)

--> JOB_ID 관계없이 다 말아! 즉 부서명에 따른 총 집계 내역만 보임.

GROUP BY DEPARTMENT_ID, ROLLUP(JOB_ID)

2.  GROUP BY ROLLUP(DEPARTMENT_ID), JOB_ID

-> 부서번호 관계없이 말아!라고 해석해서 총 거래 건수만 보여주는거 아닌가? 라고 생각하실 수 있으나, 

GROUP BY ROLLUP(DEPARTMENT_ID), JOB_ID

GROUP BY ROLLUP절은 뒤에서부터 차례대로 작용합니다.

그래서 DEPARTMENT_ID와 JOB_ID에 대해 그룹핑 하고, 부서를 신경 안써서 뒤에 부서 없이 JOB_ID에 따른 총합을 추가적으로 보여주게 되는 것이죠.

 

결국 부서가 NULL인 부분은 보여주는 순서는 다를 수 있어도 아래 쿼리 결과와 같습니다. (21번라인부터 해당)

SELECT JOB_ID 직무, COUNT(*) 직원수 
FROM EMPLOYEES e  
GROUP BY JOB_ID;

 

3.  GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)

GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID) 일 때

세 번째 ROLLUP은 부서번호와 직무 두 컬럼 모두를 감싸고 있어요.

이 말은 부서별 총건수와 전체 총 건수 두 개를 보여달라는 것과 동일하겠죠??ㅎㅎ

 

GROUP BY + ROLLUP 조합은 조인테이블에서도 물론 가능

근데 부서번호로 하니까 어떤 부서인지 헷갈리죠 ㅎㅎ

부서번호 - 부서이름 이렇게 매핑되어있는 DEPARTMENTS테이블과 아웃터조인을 시켜서 깔끔하게 정리해봅시다.

이번에는 부서별 인권비가 얼마나 나가고 있는지 확인하는 쿼리를 만들어볼게요.

SELECT d.DEPARTMENT_NAME 부서명, SUM(e.SALARY) 부서별인권비
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID (+)
GROUP BY ROLLUP(d.DEPARTMENT_NAME);

요렇게 하면 부서별인권비와 회사 총 인권비를 확인할 수 있겠죠

ROLLUP 결과가 NULL로 보여지는게 번거로워! -GROUPING 사용

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

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

둘다 부서명 값이 null로 표기돼!

부서명 값 자체가 NULL인 레코드랑 ROLLUP결과로 부서명 값이 NULL로 표기되는 레코드와 헷갈려서 빨간색처럼 표기해주고 싶어요. 

--잘못된 쿼리 
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);

첫번째로 NULL일 경우 값을 대체해주는 NVL함수를 생각할 수 있겠죠! 하지만 NVL(d.DEPARTMENT_NAME,'부서가 없는')을 사용해주면 13라인에 해당하는 총 집계 함수 부분 또한 '부서가 없는'으로 표기되는 에러가 있습니다.

GROUPING과 CASE WHEN 사용하자.

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

--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에 대한 구문 활용법이 궁금하신 분들은 이 포스팅 링크를 참고해주세요.

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

총합계만 보고 소계 안보는 법 - GROUPING_ID

GROUPING_ID을 통해 조건을 걸러내면 불필요한 소계를 출력하지 않을 수 있어요 ㅎㅎ

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

GROUPING과 GROUPING_ID에 대해서는 따로 포스팅을 추후 하기로 하고 

그런방법이 있구나~정도로만 알고 넘어가줍시다.

HAVING GROUPING_ID (d.DEPARTMENT_NAME , e.JOB_ID ) != 1 없을때(좌) 추가했을때(우)

훨씬 깔끔하쥬?

 

오늘은 간단하게 ROLLUP 사용법과, 보기 좋게 쿼리문을 작성(?)하는 법에 대해 살펴보았어요.

공감/댓글/광고보답은 정보공유에 힘쓰는 블로거에게 큰 동기부여가 됩니다 

오늘도 고생하셨어요 다음 포스팅에서 찾아뵐게요 :)