본문 바로가기

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

[SQL] GROUPING_ID 함수 사용법, GROUP BY ROLLUP 소계 구분하기

반응형

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

안녕하세요

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

지난 번에 GROUP BY, ROLLUP, GROUPING 함수 들을 알아봤는데요~~!

이번에는 이 함수에 이어서 GROUPING_ID 함수에 관해 작성해보려고 합니다.

GROUPING을 잘 이해하셨으면 사실 GROUPING_ID는 유사하기 때문에 쉬울거예요

 

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

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

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

 

■ GROUP BY와 HAVING

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

 

■ ROLLUP과 GROUP BY

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

 

■ GROUPING에 대해

2021.09.30 - [별걸다하는 IT/데이터베이스 Database] - [SQL] GROUPING 완벽히 이해하고 사용하기, NULL값 '총합'으로 변경

 

그럼 고고~

1. GROUPING_ID는 무엇인가?

GROUPING과 GROUPING_ID 고놈들 참 비슷하게 생겼어요. 뒤에 ID가 붙은거 차이일 뿐이죠.

 

GROUPING은 컬럼 값이 소계나 총합등 집계된 데이터일 경우 1을 리턴하고 만약 집계된 데이터가 아니면 0을 리턴하는 함수인데요.

그래서 보통 사용할떄 GROUPING(컬럼명) 이렇게 사용합니다. GROUPING(컬럼명)이 1이면 이게 집계된 데이터라는 뜻이고, 0이면 NULL이거나 일반데이터거나 GROUP BY로 그룹지어진 데이터 등 집계된 데이터가 아닌 값을 의미하죠.

그림1

GROUPING은 괄호 안에 하나의 컬럼만 올 수 있어요. 다수의 인자가 올 수 없고 한개의 인자만 받는다!

그런데 사용하다보면, 다수의 인자에 대한 GROUPING값이 모두 필요한 경우가 있습니다.

GROUPING(컬럼1), GROUPING(컬럼2), GROUPING(컬럼3) 등.. 요런 값들이 필요한거죠.

 

이거를 GROUPING(컬럼1) = 1 AND GROUPING(컬럼2) = 0 AND GROUPING(컬럼3) = 1 .... 이렇게 늘려쓰느니 한번에 값을 확인할 수 있으면 얼마나 좋을까!

그것을 나타낸게 GROUPING_ID 예요. GROUPING(컬럼1, 컬럼2, 컬럼3)!

 

GROUPING_ID는 다수의 컬럼에 대한 GROUPING 값을 알 수 있게 해줍니다.

하나의 함수로 다수의 결과 데이터를 얻을 수 있도록 GROUPING_ID는 이진법을 이용합니다.

GROUPING 결과값이 ON(1), OFF(0) 두가지로만 표현될 수 있기 때문에 비트방식으로 표현할 수 있는거죠

그림2

예를 들어 GROUPING(컬럼1) 값이 1이고, GROUPING(컬럼2)의 값이 1이고 GROUPING(컬럼3)의 값이 0일 때

GROUPING_ID는 이진수로 110이 되고 이를 10진수로 전환하면 6이 됩니다.

반대로 6을 이진법으로 전환하면 110이므로 우리는 "아 첫번째 두번째 컬럼이 GROUPING 했을 때 1이구나" 알 수 있는거죠.

반응형

2. GROUPING_ID 값 GROUPING이랑 비교하면서 확인하기

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

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

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

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

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

고럼 이렇게 쿼리를 짤 수 있겠죠?

그림3

결과는 이렇게 됩니다!

근데 우리가 확인하고 싶은건 GROUPING_ID 값이죠?

GROUP BY의 기준이 되었던 컬럼 부서명(DEPARTMENT_NAME)과 직무(JOB_ID)를 각각 그룹핑(GROUNPING)해보고

GROUPING_ID의 값이 해당 이진 데이터를 10진화 한게 맞는지 체크해봅시다.

SELECT d.DEPARTMENT_NAME 부서명, e.JOB_ID 직무, SUM(e.SALARY) 부서별인건비,
	GROUPING(d.DEPARTMENT_NAME) || GROUPING (e.JOB_ID ) AS 컬럼별그룹핑값, 
    GROUPING_ID(d.DEPARTMENT_NAME, e.JOB_ID) AS 그룹핑아이디값 
FROM EMPLOYEES e , DEPARTMENTS d 
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)
GROUP BY ROLLUP(d.DEPARTMENT_NAME, e.JOB_ID)
ORDER BY d.DEPARTMENT_NAME;

요렇게 SELECT 절 뒤에 GROUPING과 GROUPING_ID를 조회할 수 있도록 추가해줬어요.

그림4

[초록색 네모]

컬럼별 그룹핑 값의 첫 번째 비트는 GROUPING(부서명)이고 두 번째 비트는 GROUPING(직무) 값이예요.

[빨간색 네모]

GROUPING_ID(부서명, 직무) 값은

GROUPING_ID 함수 괄호안의 인수들을 각각 GROUPING한 후 10진수로 변환한 값이랑 동일하다는 것을 확인할 수 있습니다. 대표적인 예로 33번째 행을 보면, 이진수 11의 값은 10진수로 변환하면 3에 해당!

 

3. GROUPING_ID 사용 예시

총합계와 소계 명시하기

그림3번 도표를 보세요. NULL이 너무 많아서 지저분하죠?

이렇게 소계는 소계로! 총합은 총합으로! 표기할 수 있으면 얼마나 깔끔해질까요!

소계는 grouping(부서)가 0이고 grouping(job_id)가 1일 경우 소계이고,

총합은 grouping(부서)가 1이고 grouping(job_id)가 1일 때가 됩니다.

SELECT DEPARTMENT_ID 부서번호, 
      CASE 
      WHEN GROUPING_ID(e.DEPARTMENT_ID ,e.JOB_ID) = 1 THEN '소계'
      WHEN GROUPING_ID(e.DEPARTMENT_ID ,e.JOB_ID) = 3 THEN '총합'
      ELSE e.JOB_ID 
      END AS 직무, SUM(SALARY) 인권비 
FROM EMPLOYEES e --직원 테이블 
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID) --(부서, 직무)를 기준으로 통계를 내라! 
ORDER BY DEPARTMENT_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; -- 소계부분 거르기!

 

그룹된 결과에서 소계부분만 제외해주기 위해 HAVING GROUPING_ID 절을 이용했어요 ㅎㅎ

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

훨씬 깔끔하쥬?

 

오늘은 오라클의 GROUPING_ID 함수를 자세히 알아봤습니다.

공감, 광고클릭, 댓글을 포스팅 작성에 큰 힘이 됩니다 :) 

다음에 봐요~~

반응형