(Oracle) 그룹 기능(ROLLUP, CUBE, GROUPING 등)

이들은 Oracle의 다양한 그룹 기능입니다.

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. 롤업 기능

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

위의 SQL 문을 실행하면 다음과 같은 결과가 나온다.

GROUP BY 절을 실행할 때와 달리 소계와 총계가 출력되는 것을 볼 수 있다.

ROLLUP 함수 사용
첫 번째 요소(제품 ID) 이후의 두 번째 요소(월) + 첫 번째 요소(제품 ID) 이후의 두 번째 요소(월)의 소계 + 총계
항복

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

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

(차이점을 쉽게 볼 수 있도록 열의 순서를 변경했습니다.

)

이전과 달리
두 번째 요소(제품 ID)를 첫 번째 요소(월)로 나눈 값 + 소계(SUBTOTAL) 두 번째 요소(제품 ID)를 첫 번째 요소(월)로 나눈 값 + 총계(GRAND TOTAL)
항복

3. 큐브 기능

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

ROLLUP 기능을 사용했을 때보다 결과의 수가 늘어난 것을 느낄 수 있을 것입니다.

CUBE 함수는 가능한 모든 그룹화 열 수에 대해 SUBTOTAL 및 GRAND TOTAL을 생성합니다.

따라서 ROLLUP 함수와 달리 인자의 순서가 달라도 결과는 같다.


또한 위와 달리 단순 월별 소계가 생성되며, by-column의 개수가 N일 때 2의 N승 소계가 생성된다.

4. 그룹화 집합 기능

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

소계는 첫 번째 요소(제품 ID) 뒤에 나타나고 소계는 두 번째 요소(월) 뒤에 나타납니다.


ROLLUP 및 CUBE와 달리 계층 구조가 나타나지 않으므로 인수의 순서가 달라도 결과는 동일합니다.

또한 GROUPING SETS 함수는 괄호로 묶인 각 집합에 대한 집계를 얻을 수 있습니다.

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

그룹화된 열이 단일 요인으로 처리되어 (제품 ID, 월)별 소계와 회사별 소계가 표시되는 것을 확인할 수 있습니다.

5. 그룹화 기능

GROUPING 함수는 그룹별 집계를 직접 가져오는 것이 아니라 위에서 언급한 ROLLUP, CUBE 및 GROUPING SETS를 지원하기 위한 것입니다.

GROUPING(expression) = 집계가 계산된 결과에 대해 1이고, 그렇지 않으면 GROUPING(expression) = 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, 월), 회사);

이는 CUBE 기능과 GROUPING SETS 기능에도 적용할 수 있습니다.

6. 참조.

데이터 전문지식포털 DBGuide.net, http://www.dbguide.net/db.db?cmd=view&boardUid=148204&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1