[오라클(Oracle)] 그룹 함수 (ROLLUP, CUBE, GROUPING 등)

동현·2020년 9월 4일

오라클에 있는 다양한 그룹함수에 대한 내용이다.

CREATE TABLE 월별매출 (
    상품ID VARCHAR2(5),
    월 VARCHAR2(10),
    회사 VARCHAR2(10),
    매출액 INTEGER );
    
INSERT INTO  월별매출 VALUES ('P001', '2019.10', '삼성', 15000);
INSERT INTO  월별매출 VALUES ('P001', '2019.11', '삼성', 25000);
INSERT INTO  월별매출 VALUES ('P002', '2019.10', 'LG', 10000);
INSERT INTO  월별매출 VALUES ('P002', '2019.11', 'LG', 20000);
INSERT INTO  월별매출 VALUES ('P003', '2019.10', '애플', 15000);
INSERT INTO  월별매출 VALUES ('P003', '2019.11', '애플', 10000);

SELECT * FROM 월별매출;

우선 그룹함수를 쓸 예시 테이블을 하나 만들어주도록 하자.

1. GROUP BY절

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY 상품ID, 월;

그룹 함수를 쓰기에 앞서 단순한 GROUP BY절을 실행해보자.

상품ID, 월 기준으로 그룹화가 되는 것을 볼 수 있다.

2. ROLLUP 함수

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);

위의 sql문을 실행해보면 다음과 같은 결과가 나오게 될 것이다. GROUP BY절을 실행했을 때와는 다르게 그에 대한 소계(SUBTOTAL), 총계(GRAND TOTAL)이 나오는 것을 알 수 있다.

ROLLUP함수를 쓰면
첫번째 인자(상품 ID)별 두번째 인자(월) + 첫번째 인자(상품 ID)별 두번째 인자(월)의 소계(SUBTOTAL) + 총계(GRAND TOTAL)
의 결과가 나온다.

따라서 ROLLUP함수는 인수의 순서에도 영향을 받게 된다.

SELECT 월, 상품ID, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(월, 상품ID);

(차이를 알아보기 쉽도록 컬럼의 순서는 변경하였다.)

아까와는 반대로
첫번째 인자(월)별 두번째 인자(상품 ID) + 첫번째 인자(월)별 두번째 인자(상품ID)의 소계(SUBTOTAL) + 총계(GRAND TOTAL)
의 결과가 나온다.

3. CUBE 함수

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(상품ID, 월);

ROLLUP 함수를 쓸 때보다도 결과 건수가 많아진 걸 느낄 수 있을 것이다. CUBE 함수는 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계(SUBTOTAL)과 총계(GRAND TOTAL)을 생성한다. 따라서 ROLLUP 함수와는 다르게 인자의 순서가 달라도 결과는 같다.
위와 다르게 단순한 월별 소계(SUBTOTAL)도 생성되었으며, 그룹핑 컬럼의 개수를 N이라고 한다면 2의 N승의 소계(SUBTOTAL)을 생성한다.

4. GROUPING SETS 함수

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS(상품ID, 월);

첫번째 인자(상품ID)별 소계(SUBTOTAL), 두번째 인자(월)별 소계(SUBTOTAL)이 나오는 것을 확인할 수 있다.
ROLLUP과 CUBE와 달리 계층 구조가 나타나지 않으며 따라서 인자의 순서가 달라도 결과는 똑같다.

또한, GROUPING SETS 함수는 괄호로 묶은 집합별로도 집계를 구할 수 있다.

SELECT 상품ID, 월, 회사, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS((상품ID, 월), 회사);

묶여진 컬럼을 하나의 인자로 취급해 (상품ID, 월)별 소계(SUBTOTAL)과 회사별 소계(SUBTOTAL)이 나오는 걸 확인할 수 있다.

5. GROUPING 함수

GROUPING 함수는 직접 그룹별 집계를 구하지는 않지만 앞서 말한 ROLLUP, CUBE, GROUPING SETS를 지원하는 역할을 한다. 집계가 계산된 결과에 GROUPING(표현식) = 1이 되며, 그 외에는 GROUPING(표현식) = 0이 된다.

SELECT 
    CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품ID' ELSE 상품ID END AS 상품ID,
    CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월, 
    SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);

CASE 함수와 ROLLUP 함수를 응용해서 다음과 같은 표현도 가능하다.

SELECT 
    CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품ID' ELSE 상품ID END AS 상품ID,
    CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월, 
    SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(상품ID, 월);

SELECT 
    CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품ID' ELSE 상품ID END AS 상품ID,
    CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월, 
    CASE GROUPING(회사) WHEN 1 THEN '모든 회사' ELSE 회사 END AS 회사,
    SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS((상품ID, 월), 회사);