[Oracle] 오라클 GROUP BY ROLLUP, CUBE, GROUPING SETS 정리 :: 마이자몽

GROUP BY

특정 칼럼들을 기준으로 그룹화하여 합산, 평균, 최고값, 최소값 등의 수치를 확인하기 위해 GROUP BY 절을 이용합니다. 부서별 연봉 평균, 반 시험 최고 점수, 매장별 재고량과 같이 하나의 기준으로 그룹을 시킬때는 한가지 결과만 확인하면 됩니다. 하지만 두 가지 이상의 기준으로 GROUP BY 시켰을때는 표형식으로 데이터를 확인하고 싶다는 생각이 들것입니다.

 

부서별 팀별 연봉  평균, 반별 과목별 시험 최고 집수, 매장별 카테고리별 재고량처럼 2개의 기준이 있을때는 가로 세로 각각 기준으로 표형태로 출력되는 데이터가 처음 떠올랐을 것입니다. 예를 들어 부서별 직업별 연봉 합산금액을 알고 싶다면 가로에 부서, 세로에 직업을 놓은 표형태와 각각 그룹별로 따로따로 그리고 전체 합산도 자동으로 구해주면 좋겠다는 생각이 바로 들었을거라고 생각합니다. 오라클에서는 ROLLUP, CUBE, GROUPING SETS를 이용해서 전부 출력 가능합니다.

 

오라클 HR 계정으로 알아보겠습니다.

 

SELECT 
    DEPARTMENT_ID
    ,JOB_ID
    ,SUM(SALARY)
FROM
    EMPLOYEES
WHERE DEPARTMENT_ID > 80
GROUP BY 
    DEPARTMENT_ID
    ,JOB_ID
ORDER BY JOB_ID;

위의 쿼리에서는 DEPARTMENT_ID와 JOB_ID 별로 그룹화하여 결과를 출력했습니다. 두가지 기준으로 나눴기 때문에 오른쪽 표와 같이 가로에는 DEPARTMENT_ID, 세로는 JOB_ID값과 각 칸에는 해당 기준에 맞는 연봉의 합산 값이 들어있습니다.

 

 

1 : 각각 DEPARTMENT_ID의 연봉 합산 값

2 : 전체 연봉 합산 값

3 : 각각 JOB_ID의 연봉 합산 값

 

ORACLE에서는 GROUPING BY 절과 함께 ROLLUP, CUBE, GROUPING SETS를 사용하여 위 이미지의 1, 2, 3번 값들도 같이 출력해 줄 수 있습니다.

 

 

ROLLUP

1 : 각각 DEPARTMENT_ID의 연봉 합산 값

2 : 전체 연봉 합산 값

ROLLUP을 사용하면 1, 2번에 해당하는 내용을 함께 얻을 수 있습니다. 각각 DEPARTMENT_ID의 연봉 합산 값과 전체 연봉 합산 값을 얻을 수 있습니다.

 

SELECT
    DEPARTMENT_ID
    ,JOB_ID
    ,SUM(SALARY)
FROM 
    EMPLOYEES
WHERE DEPARTMENT_ID > 80
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID;

GROUP BY 절 뒤에 ROLLUP을 사용해주고 인자로 그룹화하는 칼럼을 선택해줍니다. 인자의 첫번째 컬럼의 합산 내용과 전체 합산 내용을 출력해줍니다. 만약 DEPARTMENT_ID와 JOB_ID의 인자 위치를 바꾸게 되면, JOB_ID에 대한 합산 금액과 전체 합산 내용이 출력되었을 것입니다. ROLLUP을 사용하면 인자로 넣어준 맨 앞 칼럼을 기준과 전체 데이터의 그룹함수 결과를 반환해줍니다.

 

 

CUBE

1 : 각각 DEPARTMENT_ID의 연봉 합산 값

2 : 전체 연봉 합산 값

3 : 각각 JOB_ID의 연봉 합산 값

 

SELECT
    DEPARTMENT_ID
    ,JOB_ID
    ,SUM(SALARY)
FROM
    EMPLOYEES
WHERE DEPARTMENT_ID > 80
GROUP BY CUBE(JOB_ID, DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID;

CUBE를 GROUP BY절에서 같이 사용하면 모든 경우의 수를 출력해줍니다.

 

 

GROUPING SETS

 GROUP BY절을 ROLLUP과 CUBE를 사용하여 결과를 출력해 봤습니다. ROLLUP은 맨처음에 나오는 인자 칼럼과 전체를 기준으로 한 그룹함수 결과를 반환해줬고, CUBE는 전체 경우의 수의 그룹함수 결과를 반환해 줬습니다. 그런데 만약 전체값의 결과 즉 3번만 보고 싶다거나, 3번을 제외한 나머지를 보고싶다거나, 1,2,3번의 결과를 선택해서 출력하고 싶다면 GROUPING SETS를 사용해주면 됩니다.

 

GROUPING SETS의 인자로 각각 원하는 그룹 데이터를 얻을 수 있습니다. 소괄호 안에 그룹화할 칼럼을 먼저 넣어서 하나의 그룹을 만들어주고 해당 그룹의 각각 칼럼을 이용해서 필요한 데이터만 선택해서 출력 가능합니다. "()" 괄호 문자를 이용하면 전체 함산 데이터를 받을 수 있습니다.

 

 

GROUPING SETS 예시

SELECT
    DEPARTMENT_ID
    ,JOB_ID
    ,SUM(SALARY)
FROM
    EMPLOYEES
WHERE DEPARTMENT_ID > 80
GROUP BY GROUPING SETS((DEPARTMENT_ID, JOB_ID), ());

그룹화한 칼럼에 대한 전체 합산 결과만 받아오도록 해봤습니다.