소계에 대해서 알아보자 (feat. ROLLUP, CUBE, GROUPING SETS, GROUPING)

안녕하세요, 끙정입니다.

 

오늘은 분석의 시작인 소계에 대해서 알아보겠습니다.

문법으로는 ROLLUP, CUBE, GROUPING SETS가 있습니다.

대부분의 DBMS에서 호환성 걱정 없이 사용할 수 있고, 기능은 거의 비슷합니다.

 

하나씩 살펴보겠습니다.

 

1. ROLLUP (Oracle, SQL Server) , WITH ROLLUP(MariaDB, SQL Server)

가장 기본적인 통계 명령은 그룹별로 집계를 구하는 GROUP BY 입니다.

여러 개의 기준 필드를 지정하여 각 기준별로 그룹을 나누어 합계나 평균을 구할 수 있습니다.

 

SELECT gender, SUM(salary) AS sum_salary FROM tStaff GROUP BY gender;
SELECT depart, SUM(salary) AS sum_salary FROM tStaff GROUP BY depart;
SELECT depart, gender, SUM(salary) AS sum_salary FROM tStaff GROUP BY depart, gender;

 

 

그러나 총 월급 합계나 중간 소계는 알 수 없다는 단점이 있습니다.

그래서 GROUP BY에 소계를 출력하는 확장 함수를 제공합니다.

 

ROLLUP 함수는 그룹별 소계와 총계를 같이 계산합니다.

그룹핑 기준 필드를 ROLLUP( ) 괄호 안에 감싸 나열하며 기준 필드가 여러 개이면 콤마로 구분합니다.

 

/* GROUP BY 뒤에 ROLLUP() 를 붙인다. */
SELECT depart, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart);

 

NULL이라는 이름으로 총계를 출력한다!

 

부서별 월급 합계를 먼저 출력하고 마지막 줄의 NULL 레코드에 전체 총계를 같이 출력합니다.

 

그룹핑 기준이 둘 이상이면 각 그룹에 대한 소계까지 포함하여 조금 복잡해집니다.

 

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart, gender);

 

 

부서별, 성별로 그룹핑하되 각 부서의 소계를 추가합니다.

기준 필드가 2개일 때 ROLLUP은 1차 기준 필드에 대한 소계를 계산합니다.

이 경우 1차 부서의 월급 소계만 구하며 성별 소계는 없습니다.

그룹핑 순서를 바꾸면 소계를 구하는 기준 필드가 달라집니다.

 

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY ROLLUP(gender, depart);

 

 

남직원의 각 부서별 월급 총합과 남직원 전체의 소계를 출력하고

이어서 여직원 부서별 월급 총합과 여직원 전체 소계를 출력합니다.

성별 소계만 있고 부서별 소계는 없습니다.

 

그룹핑 기준 필드를 모두 ROLLUP에 포함할 필요는 없으며 소계가 필요한 필드만 포함합니다.

롤업 대상이 아닌 기준 필드는 GROUP BY 다음에 적고 롤업 대상 필드만 ROLLUP 괄호 안에 적습니다.

 

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY depart, ROLLUP(gender);
SELECT gender, depart, SUM(salary) FROM tStaff GROUP BY gender, ROLLUP(depart);

 

 

성별만 ROLLUP하면 성별 소계만 구하고 부서만 ROLLUP하면 부서별 소계만 구합니다.

두 기준 필드 모두에 대한 소계인 전체 총합은 구하지 않습니다.

마지막에 NULL NULL이 없는 것을 알 수 있습니다.

 

오라클과 SQL Server가 위와 같은 문법인 것과 달리,

MariaDB는 기준 필드 뒤에 WITH ROLLUP을 붙입니다.

 

/* MariaDB는 WITH ROLLUP, SQL Server도 허용된다. */
SELECT depart, SUM(salray) FROM tStaff GROUP BY depart WITH ROLLUP;

 

 

2. CUBE (Oracle, SQL Server Only)

ROLLUP은 1차 그룹핑 기준 필드에 대해서만 소계를 구합니다.

그룹핑 순서에 따라 결과셋이 달라지며 소계를 구할 필드를 먼저 그룹핑해야 합니다.

이에 비해 CUBE 함수는 모든 기준 필드에 대한 소계를 다 구합니다.

 

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY CUBE(depart, gender);

 

 

부서별, 성별로 그룹핑하여 둘 다 CUBE 함수로 감쌌습니다.

각 부서별 월급 소계, 각 성별 월급 소계를 모두 구하며 회사 전체의 월급 총합도 구합니다.

그룹핑 기준의 모든 값에 대해 반대쪽이 NULL인 행을 다 생성합니다.

 

CUBE는 기준 필드를 바꿔 가며 ROLLUP한 결과셋의 합집합이며,

내부적으로도 두 결과셋의 중복을 제거한 합집합을 구하는 방식으로 구현되어 있습니다.

아래 쿼리는 위와 같은 결과를 냅니다.

 

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart, gender)
UNION
SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY ROLLUP(gender, depart);

 

 

CUBE는 여러 단계를 거쳐 조합한 결과셋을 생성하기 때문에

대량의 데이터에 대한 소계를 구할 때는 CUBE가 ROLLUP보다 느립니다.

CUBE는 모든 기준 필드의 소계를 다 계산하므로 그룹핑 순서를 바꾸어도 같은 결과셋을 만듭니다.

 

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY CUBE(gender, depart);

 

 

3. GROUPING SETS (Oracle, SQL Server Only)

GROUPING SETS 함수는 개별 그룹에 대한 집계는 생략하고 그룹별 소계만 계산합니다.

CUBE의 결과셋에서 부서와 성별이 모두 있는 행과 전체 합계는 빼고

성별, 부서별 중간 소계만 출력합니다.

 

SELECT depart, gender, SUM(salary) FROM tStaff GROUP BY GROUPING SETS(depart, gender);

 

 

한쪽 필드가 NULL인 것만 요약적으로 출력합니다.

그룹핑 기준의 개수에 따라 각 함수가 출력하는 결과셋이 다릅니다.

기준이 하나뿐일 때 GROUPING SETS는 쓰나 마나이며,

ROLLUP, CUBE의 출력 결과가 같습니다.

결국 이 함수들은 그룹핑 기준이 2개 이상일 때 실용적입니다.

 

ROLLUP(a, b) : (a, b)의 소계 -> (a) 의 소계 -> 전체 소계
CUBE(a, b) : (a, b)의 소계 -> (a)의 소계, (b)의 소계 -> 전체 소계
GROUPING SETS(a, b) : (a)의 소계, (b)의 소계

 

4. GROUPING 소계의 캡션

ROLLUP, CUBE의 동작은 어렵지 않지만 결과셋이 직관적이지 않습니다.

NULL로 표시한 행은 소계라는 뜻인데,

직관적으로 알아보기 어렵습니다.

 

GROUPING 함수는 소계행이면 1을 리턴하며 그렇지 않으면 0을 리턴합니다.

부서별 소계에서 이 함수가 무엇을 리턴하는지 출력해보겠습니다.

 

SELECT GROUPING(depart), depart, SUM(salary) FROM tStaff GROUP BY ROLLUP(depart);

 

앞에 열이 하나 생겼다-!

 

이 값을 활용하여 CASE 문으로 명시해줍니다.

 

SELECT CASE GROUPING(depart) WHEN 1 THEN '전체부서' ELSE depart END AS depart,
SUM(salary) FROM tStaff GROUP BY ROLLUP(depart);

 

 

GROUPING(depart)가 1이면 "전체부서"를 출력하고 0이면 depart 필드 값을 그대로 출력합니다.

 

기준 필드가 2개일 때도 마찬가지입니다.

 

SELECT CASE GROUPING(depart) WHEN 1 THEN '전체부서' ELSE depart END AS depart,
CASE GROUPING(gender) WHEN 1 THEN '전체성별' ELSE gender END AS gender,
SUM(salary) FROM tStaff GROUP BY CUBE(depart, gender)
ORDER BY depart, gender;

 

 

 

 

그만 알아보겠습니다.

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24