rollup, grouping sets, cube, grouping 함수

rollup grouping sets cube grouping 함수에 대해 알아본다

---

-- 샘플 테이블 및 데이터 생성

CREATE TABLE TAB10 (
ID VARCHAR2(5),
MON VARCHAR2(10),
COM VARCHAR2(10),
VAL INTEGER );

INSERT INTO  TAB10 VALUES ('P001', '2019.10', '삼성', 15000);
INSERT INTO  TAB10 VALUES ('P001', '2019.11', '삼성', 25000);
INSERT INTO  TAB10 VALUES ('P002', '2019.10', 'LG', 10000);
INSERT INTO  TAB10 VALUES ('P002', '2019.11', 'LG', 20000);
INSERT INTO  TAB10 VALUES ('P003', '2019.10', '애플', 15000);
INSERT INTO  TAB10 VALUES ('P003', '2019.11', '애플', 10000);

COMMIT;

 

 

-- 샘플 데이터 조회

SELECT * FROM TAB10

ID	MON	COM	VAL
P001	2019.10	삼성	15000
P001	2019.11	삼성	25000
P002	2019.10	LG	10000
P002	2019.11	LG	20000
P003	2019.10	애플	15000
P003	2019.11	애플	10000

 

 

ROLLUP

-- ID별 합계, 전체 합계를 출력함

-- ROLLUP() 에 들어오는 인자값에 따라 출력 그룹이 달라짐

SELECT ID, MON, SUM(VAL) AS VAL
FROM TAB10
GROUP BY ROLLUP(ID, MON)
ORDER BY ID, MON

 

ID	MON	VAL
P001	2019.10	15000
P001	2019.11	25000
P001	[NULL]	40000
P002	2019.10	10000
P002	2019.11	20000
P002	[NULL]	30000
P003	2019.10	15000
P003	2019.11	10000
P003	[NULL]	25000
[NULL]	[NULL]	95000

 

-- ID 와 MON 순서 변경

 : MON별 합계, 전체 합계를 출력함

SELECT ID, MON, SUM(VAL) AS VAL
FROM TAB10
GROUP BY ROLLUP(MON, ID)
ORDER BY ID, MON

 

ID	MON	VAL
P001	2019.10	15000
P001	2019.11	25000
P002	2019.10	10000
P002	2019.11	20000
P003	2019.10	15000
P003	2019.11	10000
[NULL]	2019.10	40000
[NULL]	2019.11	55000
[NULL]	[NULL]	95000

 

 

CUBE

-- 모든 경우의 수에 대하여 합계를 출력

-- 인자값의 순서에 영향없이 동일한 데이터 출력

SELECT ID, MON, SUM(VAL) AS VAL
FROM TAB10
GROUP BY CUBE(ID, MON)
ORDER BY ID, MON

 

ID	MON	VAL
P001	2019.10	15000
P001	2019.11	25000
P001	[NULL]	40000
P002	2019.10	10000
P002	2019.11	20000
P002	[NULL]	30000
P003	2019.10	15000
P003	2019.11	10000
P003	[NULL]	25000
[NULL]	2019.10	40000
[NULL]	2019.11	55000
[NULL]	[NULL]	95000

 

 

GROUPING SETS

-- 인자값별 합계를 출력함

-- 인자값의 순서에 영향없이 동일한 데이터 출력

SELECT ID, MON, SUM(VAL) AS VAL
FROM TAB10
GROUP BY GROUPING SETS(ID, MON)
ORDER BY ID, MON

 

ID	MON	VAL
P001	[NULL]	40000
P002	[NULL]	30000
P003	[NULL]	25000
[NULL]	2019.10	40000
[NULL]	2019.11	55000

 

-- 괄호를 사용하여 그룹을 만들어 집계할 수 있다

SELECT ID, MON, SUM(VAL) AS VAL
FROM TAB10
GROUP BY GROUPING SETS((ID, MON), COM)
ORDER BY ID, MON

 

ID	MON	VAL
P001	2019.10	15000
P001	2019.11	25000
P002	2019.10	10000
P002	2019.11	20000
P003	2019.10	15000
P003	2019.11	10000
[NULL]	[NULL]	40000
[NULL]	[NULL]	30000
[NULL]	[NULL]	25000

 

반응형

CASE 문과 GROUPING 조합하여 결과 NULL -> 원하는 값으로 치환하여 보기 좋게 출력

-- GROUPING 함수

 : grouping 되어진 컬럼은 0을 반환, 아닌경우 1을 반환 한다

SELECT
CASE GROUPING(ID) WHEN 1 THEN '합계' ELSE ID END AS ID,
GROUPING(ID),
CASE GROUPING(MON) WHEN 1 THEN '합계' ELSE MON END AS MON,
GROUPING(MON),
SUM(VAL) AS VAL
FROM TAB10
GROUP BY ROLLUP(ID, MON)
ORDER BY ID, MON

 

ID	GROUPING(ID)	MON	GROUPING(MON)	VAL
P001	0	2019.10	0	15000
P001	0	2019.11	0	25000
P001	0	합계	1	40000
P002	0	2019.10	0	10000
P002	0	2019.11	0	20000
P002	0	합계	1	30000
P003	0	2019.10	0	15000
P003	0	2019.11	0	10000
P003	0	합계	1	25000
합계	1	합계	1	95000

 

-- 아래와 같이 보기 좋게 출력 가능

SELECT
CASE GROUPING(ID) WHEN 1 THEN'합계'ELSE ID END AS ID,
CASE GROUPING(MON) WHEN 1 THEN'합계'ELSE MON END AS MON,
SUM(VAL) ASVAL
FROM TAB10
GROUP BY ROLLUP(ID, MON)
ORDER BY ID, MON

 

ID	MON	VAL
P001	2019.10	15000
P001	2019.11	25000
P001	합계	40000
P002	2019.10	10000
P002	2019.11	20000
P002	합계	30000
P003	2019.10	15000
P003	2019.11	10000
P003	합계	25000
합계	합계	95000

 

-- CASE + GROUPING + CUBE

SELECT
CASE GROUPING(ID) WHEN 1 THEN 'ID 합계' ELSE ID END AS ID,
CASE GROUPING(MON) WHEN 1 THEN 'MON 합계' ELSE MON END AS MON,
SUM(VAL) AS VAL
FROM TAB10
GROUP BY CUBE(ID, MON)
ORDER BY ID, MON

 

ID	MON	VAL
ID 합계	2019.10	40000
ID 합계	2019.11	55000
ID 합계	MON 합계	95000
P001	2019.10	15000
P001	2019.11	25000
P001	MON 합계	40000
P002	2019.10	10000
P002	2019.11	20000
P002	MON 합계	30000
P003	2019.10	15000
P003	2019.11	10000
P003	MON 합계	25000

 

-- CASE + GROUPING + GROUPING SETS

SELECT
CASE GROUPING(ID) WHEN 1 THEN 'ID 합계' ELSE ID END AS ID,
CASE GROUPING(MON) WHEN 1 THEN 'MON 합계' ELSE MON END AS MON,
CASE GROUPING(COM) WHEN 1 THEN 'COM 합계' ELSE COM END AS COM,
SUM(VAL) AS VAL
FROM TAB10
GROUP BY GROUPING SETS((ID, MON), COM)
ORDER BY ID, MON

 

ID	MON	COM	VAL
ID 합계	MON 합계	LG	30000
ID 합계	MON 합계	삼성	40000
ID 합계	MON 합계	애플	25000
P001	2019.10	COM 합계	15000
P001	2019.11	COM 합계	25000
P002	2019.10	COM 합계	10000
P002	2019.11	COM 합계	20000
P003	2019.10	COM 합계	15000
P003	2019.11	COM 합계	10000