본문 바로가기

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

[SQL] ROLLUP과 GROUP BY. 집계 통계에 사용되는 롤업 함수 사용법을 알아보자

반응형

[데이터베이스 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

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

 

[목차]

1. ROLLUP 함수는 언제 사용하는가

2. ROLLUP 함수 사용법 (문법)

3. GROUP BY와 ROLLUP 비교

4. ROLLUP 다수컬럼에 적용했을 때 결과

5. ROLLUP을 어느 컬럼을 감싸느냐 따른 결과

ROLLUP 함수는 무엇인가 - 언제 사용하는가?

group by가 그루핑을 하기 위한 커맨드라면,

ROLLUP은 이렇게 그룹핑 된 것을 최종 집계하기 위해 발달한(?) 함수입니다.

ROLLUP이 말아올리다 라는 뜻이잖아요. 분류된걸 말아서 모두 합계를 낸다는거죠 뭐~

 

예를 들어 그룹바이는 'GROUP BY 성별' 이렇게 작성하면,

성별 기준으로 묶어서, 남자 총 몇명, 여자 총 몇명 이렇게 알려줄 수 있잖아요.

그런데 rollup은 여기서 한 번 더 나아가서, 남자 총 몇명, 여자 총 몇명 이렇게 그룹된 데이터뿐만 아니라 이 둘을 더해서 모두 몇명 이렇게 분류전체 모집단(?) 개수를 보여줍니다.

 

이러한 관계로 rollup은 group by랑 같이 쓰여요.

ROLLUP 함수 문법

group by절만 사용하면 나눠진 데이터에 대한 결과 조회를 해주는데

ROLLUP까지 하면 그 나눠진거까지 묶어서 결과를 집계해줍니다.

그루핑 각각의 집계 함수 말고 전체건수라던가,, 총 합이라던가,, 그런것이죠!

 

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로 그룹핑 되었을 때도 ROLLUP 적용 가능합니다. 

[GROUP BY만 했을 때]

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

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

GROUP BY에 조건으로 두 컬럼이 들어가있죠?

즉 (부서번호+직무) 묶음으로 총 직원수를 세줬습니다.

20번째 줄을 보면 부서가 정해지지 않았으니 직무는 SA_REP인 직원도 한 명 있는 것을 확인할 수 있네요. (파란색박스)

 

(빨간색박스)

부서번호가 80이면서 SH_MAN인 직원 수는 5명,

부서번호가 80이면서 SA_REP인 직원수는 총 29명인 것을 알 수 있어요.

 

그럼 직무 상관 없이 부서번호가 80에 속하는 직원수는 모두 몇 명일까요?

물론 5+29 = 34 해서 우리가 계산할 수도 있지만, 데이터가 많을 수록, 값이 클수록 수기로 처리하는데에는 한계가 있겠죠.

 

[GROUP BY ROLLUP(다수컬럼)]

ROLLUP을 사용해볼까요?

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

 

GROUP BY에 들어갔던 컬럼들을 ROLLUP으로 감싸주었습니다.

파란색 바탕 행들이 기존 GROUP BY에는 없던 ROLLUP으로 새로 생겨난 행들이예요 ㅎㅎ

보시면 (부서번호, 직무) 기준으로 취합된 직원수 외에 

부서번호별 총 직원수, 전체 직원수 이렇게 생긴 것을 알 수 있습니다. 

 

[빨간테두리]

아까 '그럼 직무 상관 없이 부서번호가 80에 속하는 직원수는 모두 몇 명일까요?'

이 질문에서 group by는 직접 29+5 이렇게 계산을 했었어야 했는데요.

ROLLUP을 사용하니까 빨간색 테두리처럼 80에 해당하는 총 인원수는 34라고 자동적으로 합계를 내주네요!

 

[노란테두리]

노란색 부분은 부서 직무 상관없이 모든 직원수가 107명임을 말해줘요.

ROLLUP을 어느 컬럼을 감싸느냐 따른 결과

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에만 ROLLUP이 있을 경우

JOB_ID 관계없이 다 말아! 즉 앞의 컬럼인 DEPARTMENT_ID 분류는 고정인채로 JOB_ID는 분류 안된채로 말아진 집계 내역이 보여짐.

GROUP BY DEPARTMENT_ID, ROLLUP(JOB_ID)

2.  GROUP BY ROLLUP(DEPARTMENT_ID), JOB_ID

--> 뒤의 컬럼 JOB_ID에는 ROLLUP이 없고 앞의 컬럼 DEPARTMENT_ID에만 ROLLUP이 있을 경우

GROUP BY ROLLUP(DEPARTMENT_ID), JOB_ID

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

그래서 DEPARTMENT_ID와 JOB_ID에 대해 그룹핑 하고,

ROLLUP할 때 직무(JOB_ID)는 분류되었는대 DEPARTMENT_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절은 뒤에서부터 차례대로 작용한다 했었죠?

GROUP BY 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로 표기되는 레코드와 헷갈려서 빨간색처럼 표기해주고 싶어요. 이게 데이터가 진짜 NULL인지 아니면 '계'를 의미하는지 확인하고 싶을 때 GROUPING_ID 함수를 사용할 수 있습니다. 다음포스팅에서는 이에 대해 알아보도록 해요.

 

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

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

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

반응형