[SQL 3-2강 (2)] GROUP BY, GROUPING SET, ROLLUP, CUBE

[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반

[수강목록]
26. 조인과 집계 데이터 - 10. Grouping Set절
27. 조인과 집계 데이터 - 11. Roll up절
28. 조인과 집계 데이터 - 12. Cube절

[강의내용]

26. 조인과 집계 데이터 - 10. Grouping Set절

GROUPING SET 절을 사용하여 여러 개의 UNION ALL을 이용한 SQL과 같은 결과를 만들 수 있다.

먼저 그룹함수 실습을 위한 샘플데이터를 준비한다.

[샘플데이터 확인]

[실습1]

먼저 brand별, segment별 GROUP BY를 걸어본다.

SELECT BRAND, SEGMENT, SUM(QUANTITY)
FROM SALES
GROUP BY BRAND, SEGMENT;

+ 이제는 brand별로만 GROUP BY를 걸어본다

SELECT BRAND, SUM(QUANTITY)
FROM SALES
GROUP BY BRAND;

ABC의 Basic(200) + Premium(100) = 300

XYZ의 Basic(300) + Premium(100) = 400

이상과 같은 합계가 잘 구해졌다.

+ 이제는 segment별로만 GROUP BY를 걸어본다

SELECT SEGMENT, SUM(QUANTITY)
FROM SALES
GROUP BY SEGMENT;

Basic의 ABC(200) + XYZ(300) = 500

Premium의 ABC(100) + XYZ(100) = 200

이상과 같은 합계가 잘 구해졌다.

+ 전체 수량의 합계도 구해보자

SELECT SUM(QUANTITY)
FROM SALES;

+ 마지막으로 이 모든 쿼리들을 UNION ALL로 합쳐보면

SELECT BRAND, SEGMENT, SUM(QUANTITY)
FROM SALES
GROUP BY BRAND, SEGMENT

UNION ALL 

SELECT BRAND, NULL, SUM(QUANTITY)
FROM SALES
GROUP BY BRAND

UNION ALL 

SELECT NULL, SEGMENT, SUM (QUANTITY)
FROM SALES
GROUP BY SEGMENT

UNION ALL 

SELECT NULL, NULL, SUM (QUANTITY)
FROM SALES;

원래 데이터(brand+segment기준), only brand기준, only segment기준, 전체기준 합계를 구할 수 있다.

하지만 단점이 있다.

1) 동일한 테이블(sales 테이블)을 4번이나 읽어버림 -> 성능 저하 가능성 발생

2) SQL문이 너무 길어짐 -> 추후 유지보수 어려움

따라서 UNION ALL 대신 그룹함수를 사용하는 편이 좋다

[문법]

GROUPING SET절의 문법은 다음과 같다.

SELECT C1, C2, 집계함수(C3)
FROM TABLE_NAME
GROUP BY GROUPING SETS (
         (C1, C2),
         (C1),
         (C2),
         ()
);

()은 그룹지을 컬럼이 없는 상태(전체합계)를 나타낸다.

이제 샘플데이터를 가지고 실습해본다.

[실습2]

SELECT BRAND, SEGMENT, SUM (QUANTITY)
FROM SALES
GROUP BY GROUPING SETS ( 
         (BRAND, SEGMENT),
         (BRAND),
         (SEGMENT),
         () 
);

order가 섞였지만, UNION ALL을 사용한 결과와 동일하다.

[실습3]

GROUPING함수를 사용하면, 해당 컬럼이 집계에 사용되었는지 여부를 알 수 있다.

SELECT GROUPING(BRAND) GROUPING_BRAND,
       GROUPING(SEGMENT) GROUPING_SEGEMENT,
       BRAND, SEGMENT, SUM (QUANTITY)
FROM SALES
GROUP BY GROUPING SETS ( 
         (BRAND, SEGMENT),
         (BRAND),
         (SEGMENT),
         ()
)
ORDER BY BRAND, SEGMENT;

GROUPING(컬럼)은 해당 컬럼이 집계에 사용되었으면 0, 그렇지 않으면 1을 리턴한다.

+ CASE WHEN과 함께 쓰면 아래와 같이 가독성 있게 나타낼 수도 있다.

SELECT CASE WHEN GROUPING(BRAND) = 0 AND GROUPING(SEGMENT) = 0 THEN '브랜드별+등급별'
            WHEN GROUPING(BRAND) = 0 AND GROUPING(SEGMENT) = 1 THEN '브랜드별'
            WHEN GROUPING(BRAND) = 1 AND GROUPING(SEGMENT) = 0 THEN '등급별'
            WHEN GROUPING(BRAND) = 1 AND GROUPING(SEGMENT) = 1 THEN '전체합계'
            ELSE '' 
            END AS "집계기준",
       BRAND, SEGMENT, SUM (QUANTITY)
FROM SALES
GROUP BY GROUPING SETS ( 
         (BRAND, SEGMENT),
         (BRAND),
         (SEGMENT),
         ()
)
ORDER BY BRAND, SEGMENT;

27. 조인과 집계 데이터 - 11. Roll up절

ROLLUP절은 지정된 GROUPING 컬럼의 소계를 생성하는데 사용된다.

간단한 문법으로 다양한 소계를 출력할 수 있다.

[문법]

기본 문법(전체 ROLLUP)은 아래와 같다.

SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY ROLLUP (C1, C2, C3);

맨 첫번째의 C1을 기준으로 소계를 구해준다.

컬럼 지정 순서에 따라 결과값이 달라질 수 있다.

특정 컬럼은 제외한 부분 ROLLUP도 가능하다.

SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY C1,
ROLLUP (C2, C3);

[실습1]

전체 컬럼을 ROLLUP한다.

SELECT BRAND,
       SEGMENT,
       SUM (QUANTITY)
FROM SALES
GROUP BY ROLLUP (BRAND, SEGMENT)
ORDER BY BRAND, SEGMENT;

전체 ROLLUP의 경우

1) GROUP BY별 합계 +

2) 맨 앞에 쓴 컬럼 기준의 합계 +

3) 전체 합계

를 모두 출력한다.

[실습2]

이번에는 부분 ROLLUP을 실행해본다.

SELECT BRAND,
       SEGMENT,
       SUM (QUANTITY)
FROM SALES
GROUP BY BRAND,
ROLLUP (SEGMENT)
ORDER BY BRAND, SEGMENT;

전체 합계를 제외하고 brand별 모든 결과값을 도출한다.

전체 합계는 brand, segment별 합계가 아닌 () 상태라서 그렇다.

부분 ROLLUP에서는 GROUP BY 컬럼 기준에서 ROLLUP 컬럼의 합계만 계산해주고,

전체합계는 구하지 않는다.

28. 조인과 집계 데이터 - 12. Cube절

CUBE는 지정된 GROUPING 컬럼의 다차원 소계를 생성하는데 사용된다.

간단한 문법으로 다차원 소계를 출력할 수 있다.

문법은 ROLLUP과 동일하다.

[문법]

기본 문법(전체 CUBE)

SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY CUBE (C1, C2, C3);

CUBE절에서 다차원 소계를 실행할 열을 지정한다.

지정한 그룹의 모든 경우의 수에 대한 소계와 총계를 구한다.

부분 CUBE

SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY C1,
CUBE (C2, C3);

특정 컬럼만 분리하여 CUBE 지정을 할 수 있다.

[실습1]

전체 CUBE를 실행해본다

SELECT BRAND, SEGMENT, SUM (QUANTITY)
FROM SALES
GROUP BY CUBE (BRAND, SEGMENT)
ORDER BY BRAND, SEGMENT;

GROUP BY별 합계(원래 데이터) + brand별 + segment별 + 전체 합계를 모두 출력한다.

GROUPING SETS와 결과는 동일하나, 일일히 모든 집합의 경우의 수를 입력할 필요가 없다.

또한 ROLLUP과 다른점은

맨 앞 컬럼별(brand별) 합계만 출력하는 것이 아니라, 뒤의 컬럼(segment별) 합계도 출력해준다.

[실습2]

부분 CUBE도 실행해본다.

SELECT BRAND, SEGMENT, SUM (QUANTITY)
FROM SALES
GROUP BY BRAND,
CUBE (SEGMENT)
ORDER BY BRAND, SEGMENT;

부분 ROLLUP과 동일하게, GROUP BY 컬럼 기준만(brand별만) 합계를 출력해준다.

뒤의 컬럼(segment별), 전체() 합계는 출력하지 않는다.

티스토리툴바