[SQL] ROLLUP/GROUPING 깔끔한 소계/합계 보고서만들기

업무중에 재밌었던 쿼리를 하나 정리하려고 한다. 기초쿼리이긴 하지만 처음봤을 때는 뭔가 싶었던 쿼리문이다. 먼저 ROLLUP을 이해하는 것이 필요하다. ROLLUP은 함수에서 묶인 컬럼을 기준으로 소계/합계를 구할 수 있도록 도와준다.(컬럼순서중요)

SELECT COL1, COL2,
       SUM(COL3)
FROM TABLE
GROUP BY ROLLUP(COL1,COL2)

 

위 쿼리는 아래와 같이 합계를 출력할 것이다.

A-1 합계, A-2 합계, A 전체합계

B-1 합계, B-2 합계, B 전체합계

A,B 전체합계

COL1	COL2	SUM(COL3)
A	1	500
A	2	600
A		1100
B	1	300
B	2	200
B		500
		1600

ROLLUP함수는 GROUPING 함수와 함께 사용하여 깔끔하게 합계/소계 결과를 출력할 수 있다.

GROUPING 함수는 NULL여부만 체크하여 0과 1을 리턴,

GROUPING_ID 함수는 GROUPING_ID(COL1,COL2, ...) 의 NULL여부에 따라 0,1,2,3 ... 을 리턴한다.

(컬럼이 NULL의 경우 1, NULL이 아닌 경우는 0으로 표현하여 이진수값을 리턴, 두 개의 컬럼인 경우 00 01 11 )

SELECT COL1, COL2,
       SUM(COL3), GROUPING_ID(COL1, COL2)
FROM TABLE
GROUP BY ROLLUP(COL1,COL2)

COL1	COL2	SUM(COL3)	GROUPING_ID(COL1, COL2)
A	1	500	0
A	2	600	0
A		1100	1
B	1	300	0
B	2	200	0
B		500	1
		1600	3

DECODE 함수를 활용하면 ​마지막 컬럼의 값을 아래의 결과처럼 출력할 수 있다.

SELECT DECODE(GROUPING_ID(COL1, COL2), 1, 'SUBTOTAL', 3, 'TOTAL') AS "KUBUN",
       COL1, COL2,
       SUM(COL3), GROUPING_ID(COL1, COL2)
FROM TABLE
GROUP BY ROLLUP(COL1,COL2)

KUBUN	COL1	COL2	SUM(COL3)	GROUPING_ID(COL1, COL2)
	A	1	500	0
	A	2	600	0
SUBTOTAL	A		1100	1
	B	1	300	0
	B	2	200	0
SUBTOTAL	B		500	1
TOTAL			1600	3

NVL함수를 사용하여 조금 더 깔끔하게 출력형식을 만들 수 있다.

SELECT NVL(DECODE(GROUPING_ID(COL1, COL2), 0, COL1, 1, 'SUBTOTAL', 3, 'TOTAL'), COL1) AS "KUBUN",
       COL2,
       SUM(COL3)
FROM TABLE
GROUP BY ROLLUP(COL1,COL2)

KUBUN	COL2	SUM(COL3)
A	1	500
A	2	600
SUBTOTAL		1100
B	1	300
B	2	200
SUBTOTAL		500
TOTAL		1600

쿼리 정리 끝