DB/SQL-고급집계함수 ROLLUP, GROUPING SETS, CUBE

집계를 구할 때 ROLLUP, GROUPING SETS, CUBE 집계함수를 활용하는 방법을 예제를 통해서 알아보겠습니다. 내용이 틀리거나 내용에 관한 추가 정보가 있다면 댓글로 남겨주시면 감사하겠습니다.

 

목차

 

---

GROUP BY 이용한 집계

GROUP BY를 이용하여 SUM(), AVG(), COUNT() 등 집계를 이용하기도 합니다. 고급집계함수들을 이해하기에 앞서 GROUP BY를 이용하여 SQL 쿼리 예제 문장을 작성해 보겠습니다.

다음은 상품판매 테이블입니다. 상품, 판매수량, 일자 칼럼을 가지고 있고 어떤 상품이 언제 몇 개가 팔렸는지에 대한 데이터들이 있습니다. ( 예시를 위한 테이블이며 실제 테이블로는 사용할 수 없습니다. ) 

 

위 테이블을 가지고 일자, 상품별 판매수량을 집계하는 SQL 쿼리문을 작성해 보았습니다.

SELECT 일자, 상품, SUM(판매수량) 
FROM 상품판매
GROUP BY 일자, 상품
ORDER BY 일자;

다음과 같이 결과가 나오게 됩니다. GROUP BY를 통해서 나오게 되면 GROUP BY로 묶인 (일자, 상품)로 판매수량의 합계가 표시가 됩니다. 하지만 일자별로 판매수량 총합은 보이지 않습니다. 다시 SQL을 일자별로 GROUP BY를 묶어야지 나올 수 있습니다.

 

---

ROLLUP

지정된 GROUPING 칼럼들의 소계를 추가하여 리턴합니다. 앞서 사용했던 상품판매 테이블을 이용하여 ROLLUP 결과가 어떻게 나오는지 확인해 보겠습니다.

SELECT 일자, 상품, SUM(판매수량)
FORM 상품판매
GROUP BY ROLLUP(일자,상품)
ORDER BY 일자;

결과를 확인해 보면 GROUP BY만으로 집계를 했을 때와 다르게 추가된 열들을 확인할 수 있습니다. 일자별로 총판매수량과 전체 판매수량까지 확인할 수 있습니다. ROLLUP은 단계별 소계를 나타냅니다. 단계를 보면 ROLLUP(A칼럼,B칼럼,C컬럼) 3개의 컬럼으로 집계를 한다했을 시  우측 항목을 하나씩 제거하면서 단계별로 진행됩니다. 즉 (A컬럼,B컬럼,C컬럼) 그룹 -> ( A컬럼, B컬럼 ) 그룹 -> ( A칼럼) -> () 그룹 순으로 됩니다. 위 예제를 통해 보면 (일자, 상품) 그룹 -> (일자) 그룹 -> () 그룹순으로 진행되었습니다. 

 

결과테이블을 보면 NULL 값으로 인해 보기가 불편할 수 있습니다. 대부분 실무에서는 NULL을 그대로 두지 않고 다른 문자로 치환합니다. NULL함수를 이용하여 처리하는 방법과 GROUPING 함수를 사용하여 처리하는 방법이 있습니다.

SQLSERVER DBMS
SELECT ISNULL(일자,'전체일자') AS 일자, ISNULL(상품,'소계') AS 상품, SUM(판매수량)
FORM 상품판매
GROUP BY ROLLUP(일자,상품)
ORDER BY 일자;

ORCLE DBMS
SELECT NVL(일자,'전체일자') AS 일자, NVL(상품,'소계') AS 상품, SUM(판매수량)
FORM 상품판매
GROUP BY ROLLUP(일자,상품)
ORDER BY 일자;

NULL함수 NVL 또는 ISNULL을 사용하여 NULL일 경우 다른 문자로 치환하였습니다. GROUP 집계를 사용하게 되면 GROUPING을 이용하여 처리할 수 도 있습니다.

 

SELECT 일자, 상품, SUM(판매수량), GROUPING(일자) AS '그룹핑일자', GROUPING(상품) AS '그룹핑상품'
FORM 상품판매
GROUP BY ROLLUP(일자,상품)
ORDER BY 일자;

결과 창을 보면 NULL이 들어간 소계 행에 대해서는 1이 표시가 되고 소계 행이 아닐 때는 0이 표시가 되는 것을 확인할 수 있습니다. 따라서 WHEN CASE 또는 DECODE를 사용하여 출력방법을 바꿀 수 있습니다. 

SELECT CASE WHEN GROUPING(일자)=1 THEN '합계' ESLE 일자 END AS '일자'
	   CASE WHEN GROUPING(상품)=1 THEN '소계' 
       		WHEN GROUPING(일자)=1 THEN ''
			ELSE 상품 
       END AS '상품'
       , SUM(판매수량)
FORM 상품판매
GROUP BY ROLLUP(일자,상품)
ORDER BY 일자;

위처럼 GROUPING 결과를 사용하여 합계와 소계를 나타내었습니다.

---

GROUPING SETS

GROUPING SETS는 어떤 그룹으로 집계할지 지정한 조합별 합계를 나타냅니다. 위와 같은 예시를 사용하여 결과를 확인해 보도록 하겠습니다.

 

SELECT 일자, 상품, SUM(판매수량)
FORM 상품판매
GROUP BY GROUPING SETS(일자,상품)
ORDER BY 일자;

GROUPING SETS 함수를 사용하면 GROUPING SETS에 넣은 칼럼만 집계를 합니다. 단계는 ( 칼럼 A, 칼럼 B, (칼럼 A,칼럼 B) 를 예시로 할 때 3개의 그룹이 있다고 볼 수 있습니다 마지막의 (칼럼A,칼럼B)는 묶여있으므로 1개로 판단합니다. (칼럼A) 그룹 -> (칼럼B) 그룹 -> (칼럼A, 칼럼B) 그룹 단계로 처리를 합니다. 따라서 위 결과처럼 (일자, 상품) 두 개로 묶어 (일자) 그룹 -> (상품) 그룹으로 묶인 것을 확인할 수 있습니다.

 

GROUPING SETS을 이용하여 ROLLUP과 같은 출력이 되게 할 수 있습니다.

--ROLLUP 이용
SELECT 일자, 상품, SUM(판매수량), GROUPING(일자) AS '그룹핑일자', GROUPING(상품) AS '그룹핑상품'
FORM 상품판매
GROUP BY ROLLUP(일자,상품)
ORDER BY 일자;

--GROUPING SETS 이용
SELECT 일자, 상품, SUM(판매수량)
FORM 상품판매
GROUP BY GROUPING SETS((일자,상품),일자,())
ORDER BY 일자;

---

 

CUBE

CUBE는 가능한 모든 조합을 가지고 합계를 나타냅니다.

 

SELECT 일자, 상품, SUM(판매수량)
FORM 상품판매
GROUP BY CUBE(일자,상품)
ORDER BY 일자;

위 결과를 보면 상품별로도 묶여서 집계가 된 것을 확인할 수 있습니다. CUBE는 (칼럼 1, 칼럼 2) 일때 모든 조합인 (칼럼1,칼럼2) 그룹 -> (칼럼 1) -> (칼럼 2) -> () 묶여서 집계합니다. GROUPING SETS을 이용하여 다음과 같이 CUBE와 같은 결과를 만들어 낼 수 있습니다.

 

--GROUPING SETS 이용
SELECT 일자, 상품, SUM(판매수량)
FORM 상품판매
GROUP BY GROUPING SETS((일자,상품),일자,상품,())
ORDER BY 일자;