[Do it SQL] Chapter 07-5

1. ROLLUP

ROLLUP은 여러 수준의 소계(subtotal)를 생성하는 함수다. 마치 엑셀에서 '소계'를 자동으로 계산해주는 기능과 비슷하다.

개념 설명:

• 지정된 그룹화 열의 계층적 구조에 따라 소계를 생성한다.
• 오른쪽에서 왼쪽으로 계층을 만들어 집계한다.
• 마지막에는 전체 합계(grand total)도 포함된다.

사용 예시: 지역별, 제품별 매출을 분석하고 싶다면

SELECT 지역, 제품, SUM(매출액) AS 총매출
FROM 판매테이블
GROUP BY ROLLUP(지역, 제품)
ORDER BY 지역, 제품;

 

이 쿼리는 다음과 같은 결과를 제공한다:

• 각 지역의 각 제품별 매출
• 각 지역의 전체 매출(지역별 소계)
• 전체 매출(총계)

2. CUBE

CUBE는 ROLLUP보다 더 많은 소계를 제공하는 함수다. 모든 가능한 조합의 소계를 생성한다.

개념 설명:

• 지정된 모든 열의 가능한 조합에 대한 소계를 생성한다.
• n개의 열이 있다면 2ⁿ개의 그룹화 조합이 생성된다.
• 다차원 분석에 매우 유용하다.

사용 예시: 지역별, 제품별, 시기별 매출을 모든 조합으로 분석하고 싶다면

SELECT 지역, 제품, 분기, SUM(매출액) AS 총매출
FROM 판매테이블
GROUP BY CUBE(지역, 제품, 분기)
ORDER BY 지역, 제품, 분기;

 

이 쿼리는 다음과 같은 결과를 제공한다:

• 지역, 제품, 분기별 매출
• 지역, 제품별 매출(분기 무시)
• 지역, 분기별 매출(제품 무시)
• 제품, 분기별 매출(지역 무시)
• 지역별 매출(제품, 분기 무시)
• 제품별 매출(지역, 분기 무시)
• 분기별 매출(지역, 제품 무시)
• 전체 매출(총계)

3. GROUPING SETS

GROUPING SETS는 원하는 그룹화 조합만 선택적으로 지정할 수 있는 함수다.

개념 설명:

• 명시적으로 지정한 그룹화 조합에 대해서만 소계를 생성한다.
• CUBE나 ROLLUP보다 더 세밀한 제어가 가능하다.
• 필요한 집계만 계산하므로 효율적이다.

사용 예시: 지역별 매출과 제품별 매출만 필요하고 지역-제품 조합별 매출은 필요 없다면

SELECT 지역, 제품, SUM(매출액) AS 총매출
FROM 판매테이블
GROUP BY GROUPING SETS((지역), (제품), ())
ORDER BY 지역, 제품;

 

이 쿼리는 다음과 같은 결과를 제공한다:

• 지역별 매출(제품 무시)
• 제품별 매출(지역 무시)
• 전체 매출(총계) - 빈 괄호 ()는 전체 합계를 의미한다

4. LISTAGG

LISTAGG는 그룹별로 여러 행의 값을 하나의 문자열로 연결해주는 함수다.

개념 설명:

• 그룹 내의 여러 행 데이터를 하나의 문자열로 결합한다.
• 구분자를 지정할 수 있다.
• 결과를 정렬할 수도 있다.

사용 예시: 각 부서별로 소속 직원 이름을 쉼표로 구분하여 나열하고 싶다면

SELECT 부서명, 
       LISTAGG(직원명, ', ') WITHIN GROUP (ORDER BY 직원명) AS 직원목록
FROM 직원테이블
GROUP BY 부서명;

 

이 쿼리는 다음과 같은 결과를 제공한다:

• 인사팀: 김철수, 박영희, 이지은
• 개발팀: 강민수, 정다혜, 홍길동
• 마케팅팀: 신영수, 윤미라, 최준호

5. PIVOT

PIVOT은 행 데이터를 열 형태로 변환하는 함수다. 마치 엑셀의 피벗 테이블처럼 데이터를 재구성한다.

개념 설명:

• 행으로 저장된 데이터를 열(컬럼) 형태로 변환한다.
• 크로스탭(crosstab) 형태의 보고서를 쉽게 생성할 수 있다.
• 집계 함수(SUM, COUNT, AVG 등)와 함께 사용된다.

사용 예시: 분기별, 제품별 매출을 제품은 행으로, 분기는 열로 표시하고 싶다면

SELECT *
FROM (SELECT 제품, 분기, 매출액 FROM 판매테이블)
PIVOT (
    SUM(매출액)
    FOR 분기 IN ('Q1', 'Q2', 'Q3', 'Q4')
)
ORDER BY 제품;

 

이 쿼리는 다음과 같은 결과를 제공한다:

제품	Q1	Q2	Q3	Q4
노트북	5000	6200	4800	7500
스마트폰	8500	7800	9200	10500
태블릿	3200	2900	3500	4100

6. UNPIVOT

UNPIVOT은 PIVOT의 반대 기능을 하는 함수로, 열 데이터를 행 형태로 변환한다.

개념 설명:

• 열(컬럼) 형태의 데이터를 행 형태로 변환한다.
• 여러 열에 분산된 값들을 하나의 열로 모은다.
• 데이터 정규화에 유용하다.

사용 예시: 제품별로 분기별 매출이 각각 열로 저장된 테이블을 행 형태로 변환하고 싶다면

SELECT 제품, 분기, 매출액
FROM 분기별매출테이블
UNPIVOT (
    매출액
    FOR 분기 IN (Q1, Q2, Q3, Q4)
)
ORDER BY 제품, 분기;

 

이 쿼리는 다음과 같은 결과를 제공한다:

제품	분기	매출액
노트북	Q1	5000
노트북	Q2	6200
노트북	Q3	4800
노트북	Q4	7500
스마트폰	Q1	8500
...	...	...