[SQLD] 그룹 함수 - ROLLUP, CUBE, GROUPING SETS

이번 포스팅에서는 SQLD를 준비하던 중 그룹 함수에 대해 공부하고 정리하고자 합니다.

 

그룹 함수 

• 그룹함수를 통해 소계/중계/합계/총합계를 구할 수 있다.
• 하나의 SQL로 테이블을 한번만 읽어서 빠르게 원하는 리포트를 작성할 수 있다. 
• ex) 매출에 대한 리포트를 작성해야 하는 경우 개발자는 그룹 함수를 이용해 쉽게 작성할 수 있습니다.

 

그룹 함수의 종류

 

ROLLUP

• Subtotal을 생성하게 위해 사용합니다.
• 그룹핑 컬럼의 수가 N일때, N+1 Level의 Subtotal이 생성됩니다.
• 계층 구조이기 때문에 순서가 바뀌면 수행 결과도 바뀌게 되기 때문에 순서에 주의해야 합니다.
• ROLLUP(A,B) != ROLLUP(B,A)

 

CUBE

• 다차원적인 소계를 계산합니다.
• 결합 가능한 모든 값에 대해 다차원 집계를 생성합니다.
• 그룹핑 컬럼의 수가 N일때 , 2의 N승 Subtotal이 생성됩니다.
• 시스템의 성능에 무리를 많이 줍니다.

 

 

GROUPING SETS

• 특정 항목에 대한 소계를 계산합니다.
• ROLLUP과 CUBE는 GROUP BY 결과에 소그룹 합계와 토탈 합계를 보여주지만, GROUPING SETS는 각 소그룹별 합계만 간단히 보여줍니다.
• UNION ALL과 같은 결과를 얻을 수 있습니다.

 

 

GROUPING 

• ROLLUP, CUBE, GROUPING SETS를 지원합니다.
• 1 : 집계가 계산된 결과
• 0 : 집계가 계산되지 않은 결과 

 

사실 정리된 내용만으로는 그룹함수에 대해 명확히 이해하기 어려울 것입니다. 다음 예시를 통해 자세히 이해해보도록 합시다.

 

 

 

예시

0) GROUP BY

 

[ SQL ]

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

 

[ 결과 ]

• 가장 기본적이고 단순한 GROUP BY 절만 사용한 결과입니다. 
• 상품ID와 월에 대해 매출액의 합계를 구했습니다.

 

 

1) ROLLUP

ex) ROLLUP(상품ID, 월) 
- 상품ID별 월별 집계
- 상품ID별 집계 (모든 월 포함)
- 전체 집계 

--> 총 2개의 그룹핑 컬럼 = 2 + 1 = 3개의 Subtotal 생성

 

[ SQL ] 

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

 

[ 결과 ]

• 상품ID와 월 컬럼에 대한 값이 NULL이 아닌 경우 : 상품ID별 월별 집계
• 월에 대한 컬럼 값이 NULL : 상품 ID별 집계 (모든 월 포함)
• 모든 그룹핑 컬럼 NULL : 전체 집계
• 맨 처음 명시한 컬럼에 대해서만 소그룹 합계를 구한것을 볼 수 있습니다. (순서 주의 !!!)

 

 

2) CUBE

ex) CUBE(상품ID, 월)
- 상품ID별 월별 집계
- 상품ID별 집계 (모든 월 포함)
- 월별 집계 (모든 상품ID 포함)
- 전체 집계 

-->  총 2개의 그룹핑 컬럼 = 2의 2승 = 4개의 Subtotal 생성 

 

[ SQL ]

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

 

[ 결과 ]

• 상품ID와 월 컬럼에 모두 NULL이 아닌 경우 : 상품ID별 월별 집계
• 월에 대한 컬럼 값이 NULL : 상품별 집계 (모든 월 포함)
• 상품ID에 대한 컬럼 값이 NULL : 월별 집계 (모든 상품 포함)
• 모든 그룹핑 컬럼 NULL : 전체 집계

 

 

3) GROUPING SETS

ex) GROUPING SETS(상품ID, 월)
- 상품별 합계
- 월별 합계

--> 특정 항목에 대한 Subtotal 생성 

 

[ SQL ]

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

 

[ 결과 ]

• 상품별 합계 : 상품에 대한 소계를 생성
• 월별 합계  : 월에 대한 소계를 생성

 

 

4) GROUPING

GROUPING은 직접적으로 그룹별 집계를 구하는 함수가 아닌 위의 집계 함수들을 서포트하는 역할을 한다고 했었습니다. 집계가 계산된 결과에 대해서는 1 , 그렇지 않은 경우에는 0의 값을 갖습니다.

 

[ SQL ]

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, 월);

 

[ 결과 ] 

• 위에서 살펴보았던 ROLLUP의 예제입니다.
• null 값이었던 컬럼들의 값이 CASE 문과 GROUPING을 통해 조건값을 할당할 수 있게 되었습니다.

 

 

< 참고 자료 >

SQL 집계함수 - ROLLUP, CUBE, GROUPING SETS

[SQLD] 3-9. 그룹 함수(Group Function) - 키워드 요약 | 정리 | SQLD 공부 | 개발자 자격증 | SQL 기본 및 활