여러 그룹핑 쿼리를 UNION ALL 한 것과 같은 결과를 만들 수 있다.
0. GROUPING SETS의 원리
패턴만 익히면 어렵지 않다.
SELECT 지역, 제품, SUM(sales)
FROM sales
GROUP BY GROUPING SETS(지역, 제품);
1. 지역별 합계
2. 제품별 합계
GROUP BY GROUPING SETS(A, B);
1. A
2. B
1. 예제 테이블
ID REGION PRODUCT RETAIL SALES
---------- --------------- --------------- --------------- ----------
1 East Apples E-Mart 100
2 East Apples Homeplus 200
3 East Oranges E-Mart 250
4 East Oranges Homeplus 150
5 West Apples E-Mart 300
6 West Apples Homeplus 400
7 West Oranges E-Mart 150
8 West Oranges Homeplus 350
2. 지역별 매출 합계
SELECT region, SUM(sales)
FROM sales
GROUP BY GROUPING SETS (region);
REGION SUM(SALES)
--------------- ----------
East 700 -- 지역별 총합계
West 1200
-- 패턴
GROUP BY GROUPING SETS(A);
1. A
3. 지역과 제품별 매출 합계
SELECT region, product, SUM(sales)
FROM sales
GROUP BY GROUPING SETS (region, product);
REGION PRODUCT SUM(SALES)
--------------- --------------- ----------
East 700 -- 지역별 총합계
West 1200
Oranges 900 -- 제품별 총합계
Apples 1000
-- 패턴
GROUP BY GROUPING SETS(A, B);
1. A
2. B
4. 지역, 제품, 유통업체별 매출 합계
SELECT region, product, retail, SUM(sales)
FROM sales
GROUP BY GROUPING SETS (region, product, retail);
REGION PRODUCT RETAIL SUM(SALES)
--------------- --------------- --------------- ----------
East 700 -- 지역별 총합계
West 1200
Oranges 900 -- 제품별 총합계
Apples 1000
Homeplus 1100 -- 유통업체별 총합계
E-Mart 800
-- 패턴
GROUP BY GROUPING SETS(A, B, C);
1. A
2. B
3. C
5. 지역, 제품, 유통업체별, 전체 매출 합계
SELECT region, product, retail, SUM(sales)
FROM sales
GROUP BY GROUPING SETS (region, product, retail, ());
REGION PRODUCT RETAIL SUM(SALES)
--------------- --------------- --------------- ----------
East 700 -- 지역별 총합계
West 1200
Oranges 900 -- 제품별 총합계
Apples 1000
E-Mart 800 -- 유통업체별 총합계
Homeplus 1100
1900 -- 전체 총합계
-- 패턴
GROUP BY GROUPING SETS(A, B, C, ());
1. A
2. B
3. C
4. ()
// ()의 의미는 전체에 대한 결과를 출력한다는 의미이다.
6. 응용 패턴
-- GROUPING SETS
GROUP BY GROUPING SETS(A)
1. A
GROUP BY GROUPING SETS(A, B)
1. A
2. B
GROUP BY GROUPING SETS(A, B, C)
1. A
2. B
3. C
GROUP BY GROUPING SETS((A, B))
1. A B
GROUP BY GROUPING SETS((A, B), C)
1. A B
2. C
GROUP BY GROUPING SETS((A, B, C),())
1. A B C
2. ()
GROUP BY GROUPING SETS((A, B),(B, C))
1. A B
2. B C
// 괄호로 묶여져 있는 컬럼은 하나로 본다.
7. 응용 패턴 예시
-- GROUPING SETS((A, B))
SELECT region, product, SUM(sales)
FROM sales
GROUP BY GROUPING SETS ((region, product));
REGION PRODUCT SUM(SALES)
--------------- --------------- ----------
West Oranges 500 -- 지역과 제품별 총합계
East Apples 300
East Oranges 400
West Apples 700
-- 패턴
GROUP BY GROUPING SETS((A, B))
1. A B-- GROUPING SETS((A, B), C)
SELECT region, product, retail, SUM(sales)
FROM sales
GROUP BY GROUPING SETS ((region, product), retail);
REGION PRODUCT RETAIL SUM(SALES)
--------------- --------------- --------------- ----------
West Oranges 500 -- 지역과 제품별 총합계
East Apples 300
East Oranges 400
West Apples 700
Homeplus 1100 -- 유통업체별 총합계
E-Mart 800
-- 패턴
GROUP BY GROUPING SETS((A, B), C)
1. A B
2. C-- GROUPING SETS((A, B, C),())
SELECT region, product, retail, SUM(sales)
FROM sales
GROUP BY GROUPING SETS ((region, product, retail),());
REGION PRODUCT RETAIL SUM(SALES)
--------------- --------------- --------------- ----------
East Apples E-Mart 100 -- 지역, 제품, 유통업체별 총합계
East Apples Homeplus 200
East Oranges E-Mart 250
East Oranges Homeplus 150
West Apples E-Mart 300
West Apples Homeplus 400
West Oranges E-Mart 150
West Oranges Homeplus 350
1900 -- 전체 총합계
-- 패턴
GROUP BY GROUPING SETS((A, B, C),())
1. A B C
2. ()-- GROUPING SETS((A, B),(B, C))
SELECT region, product, retail, SUM(sales)
FROM sales
GROUP BY GROUPING SETS ((region, product),(product, retail));
REGION PRODUCT RETAIL SUM(SALES)
--------------- --------------- --------------- ----------
West Oranges 500 -- 지역과 제품별 총합계
East Apples 300
East Oranges 400
West Apples 700
Apples Homeplus 600 -- 제품과 유통업체별 총합계
Oranges Homeplus 500
Oranges E-Mart 400
Apples E-Mart 400
-- 패턴
GROUP BY GROUPING SETS((A, B),(B, C))
1. A B
2. B C
[oracle] 그룹함수 ROLLUP - 이해하기 쉽게
[oracle] 그룹함수 CUBE - 이해하기 쉽게
[oracle] 그룹함수 비교 ROLLUP / CUBE / GROUPING SETS