GROUPING SET절
GROUPING SET절을 사용하여 여러 개의 UNION ALL을 이용한 SQL과 같은 결과를 도출할 수 있다.
실습 환경
<실습>
SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY BRAND, SEGMENT;
그룹을 한 결과 각각 마다 그룹을 형성하여 그룹화의 의미가 없다.
이제 BRAND를 기준으로 그루핑을 해보자
SELECT brand, sum(quantity)
FROM sales
GROUP BY BRAND;
ABC premium 과 ABC segment값이 더해졌다는 것을 확인
XYZ premium 과 XYZ segment값이 더해졌다는 것을 확인
SEGMENT 기준으로
SELECT SEGMENT, sum(quantity)
FROM sales
GROUP BY SEGMENT;
이제는 모든 것을 기준으로 합계 구하기
SELECT sum(quantity)
FROM sales;
이제는 모든 것을 모아서 한번 표현해보자
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;
이러한 경우 동일한 테이블을 4번이나 읽었다. 성능 저하 가능성이 있다.
SQL이 너무 길어졌다 -> 복잡해진다 -> 유지보수가 용이하지 않다.
그래서 이러한 것을 해결하기 위하여 GROUPING SET절이 있다.
GROUPING SET 절을 사용하여 여러 개의 UNION ALL을 이용한 SQL과 같은 결과를 도출할 수 있다.
GROUPING SET 절 문법
SELECT C1, C2, 집계함수(C3)
FROM TABLE_NAME
GROUP BY
GROUPING SETS
(
(C1, C2)
, (C1)
, (C2)
, ()
);이것을 위의 예제에 맞춰서 해주었다.
SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY
GROUPING SETS
(
(brand, segment)
, (brand)
, (segment)
, ()
)
ORDER BY brand, segment;
추가적으로 자주 사용하는 함수 GROUPING 함수
그룹핑 결과를 보았을때 이것이 그룹의 결과에 적용되었는지 안 되었는지 여부를 확인할 수 있는 함수이다.
실제 사용은 다음과 같다.
<실습>
SELECT GROUPING(BRAND) as GB
, GROUPING(SEGMENT) as GS
, brand
, segment
, sum(quantity)
FROM sales
GROUP BY ROLLUP (brand, segment)
ORDER BY brand, segment
집계에 사용되었으면 1이고 NULL로 표현이 된다.
이것을 실무에서 가독성 있도록 바꾸면 다음과 같다.
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;
GROUPING SET이라는 함수를 잘 사용하지 않지만 매우 실용적이고 좋다. 그러므로 자주 사용하기 위하여 노력을 할 것
ROLL UP절
지정된 GROUPING 컬럼의 소계를 생성하는데 사용된다. 간단한 문법으로 다양한 소계를 출력할 수 있다.
ROLLUP절 문법
SELECT
C1, C2, C3
, 집계함수(C4)
FROM TABLE_NAME
GROUP BY
ROLLUP (C1, C2, C3);소계를 생성할 컬럼을 지정하고 컬럼 지정 순서에 따라 결과값이 달라질 수 있다. (제일 앞에 놓인 것에 소계를 구함)
SELECT
C1, C2, C3
, 집계함수(C4)
FROM TABLE_NAME
GROUP BY C1
ROLLUP (C2, C3);C1만 그룹화를 진행하였다. 특정 컬럼을 제외한 부분적인 ROLLUP도 가능하다.
<실습>
SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY ROLLUP (brand, segment)
ORDER BY brand, segment;
앞에 있는 것을 기준으로 하였다. 뒤에 있는 기준으로는 합계를 구하지 않는다.
ROLLUP은 맨 앞에 기재한 컬럼을 기준으로 하여 집계를 진행 한다.
<실습>
SELECT segment, sum(quantity)
FROM sales
GROUP BY segment;
여기서 만약 전체적인 합을 구하고 싶다면 ROLLUP을 사용한다.
SELECT segment, sum(quantity)
FROM sales
GROUP BY ROLLUP (segment);
전체합이 나온 것을 확인 할 수 있다.
이제 전체 경우들에 대해서 보자
<실습>
그냥 group by
SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY (brand, segment)
ORDER BY brand, segment;
전체 ROLLUP
SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY ROLLUP (brand, segment)
ORDER BY brand, segment;
GROUP BY별 합계 + rollup절에 맨 앞에 쓴 컬럼 기준의 합계도 나오고 + 전체 합계도 나온다.
SEGMENT를 GROUP BY
SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY segment,
ROLLUP (brand)
ORDER BY brand, segment;
부분 ROLLUP시 전체 합계는 구하지 않는다!
부분 ROLLUP = GROUP BY별 합계 + ROLLUP절에 맨 앞에 쓴 컬럼 기준의 합계도 나오고 전체 합는 나오지 않는다.
CUBE 절
지정된 GROUPING 컬럼의 다찬원 소계를 생성하는데 사용된다. 간단한 문법으로 다차원 소계를 출력할 수 있다.
CUBE절 문법
SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY CUBE (C1, C2, C3);CUBE절에서 다차원 소계를 할 열을 지정한다. (지정한 그룹의 모든 경우에 수에 대한 소계와 총계를 구한다.)
SELECT C1, C2, C3, 집계함수(C4)
FROM TABLE_NAME
GROUP BY C1,
CUBE (C2, C3);특정 컬럼만 분리하여 CUBE 지정을 할 수 있다.
CUBE(C1,C2,C3)
GROUPING SETS(
(C1,C2,C3),
(C1,C2),
(C1,C3),
(C2,C3),
(C1),
(C2),
(C3),
()
)위에 두개는 같은 역활이다. CUBE를 이용하여 간략하게 표현이 가능하다.
<실습>
SELECT brand, segment, sum(quantity)
FROM sales
GROUP BY CUBE (brand, segment)
ORDER BY brand, segment;
(brand, segment),
(brand),
(segment),
()
가 다 나온 것을 확인 할 수 있다.
