그룹별 소계(합계) 구하기 ( GROUP BY / ROLLUP / GROUPING_ID / CUBE /GROUPING SETS )
#집계
그룹별로 합계의 총합계 구하기
그룹별로 합계를 구하고, 그에 따른 총합을 구하기
※ 아래는 이름에대한 합계를 구하고, 그 총합계를 구하는 쿼리가 된다.
WITH TBL AS (
SELECT '호랑이' AS NM, 'AA' AS TYPE, 1000 AS PRICE FROM DUAL UNION ALL
SELECT '호랑이' AS NM, 'AA' AS TYPE, 1000 AS PRICE FROM DUAL UNION ALL
SELECT '송아지' AS NM, 'AA' AS TYPE, 2000 AS PRICE FROM DUAL UNION ALL
SELECT '송아지' AS NM, 'AA' AS TYPE, 2500 AS PRICE FROM DUAL UNION ALL
SELECT '송아지' AS NM, 'AB' AS TYPE, 1000 AS PRICE FROM DUAL
)
SELECT NM, SUM(PRICE) FROM TBL
GROUP BY ROLLUP(NM);
그룹별 합계의 소계 및 총합계를 구하기
그룹별 소계를 구하고, 또한 소계들에 대한 총 합계를 취득한다.
WITH TBL AS (
SELECT '호랑이' AS NM, 'AA' AS TYPE, 1000 AS PRICE FROM DUAL UNION ALL
SELECT '호랑이' AS NM, 'AA' AS TYPE, 1000 AS PRICE FROM DUAL UNION ALL
SELECT '송아지' AS NM, 'AA' AS TYPE, 2000 AS PRICE FROM DUAL UNION ALL
SELECT '송아지' AS NM, 'AA' AS TYPE, 2500 AS PRICE FROM DUAL UNION ALL
SELECT '송아지' AS NM, 'AB' AS TYPE, 1000 AS PRICE FROM DUAL
)
SELECT NM, TYPE, SUM(PRICE) FROM TBL
GROUP BY ROLLUP(NM, TYPE);
GROUPING 과 GROUPING_ID
1) grouping 은 1개의 컬럼만 변수로 사용할 수 있다.
2) grouping_id 는 여러개의 컬럼을 변수로 사용할 수 있다.
3) grouping 지정된 컬럼의 경우 NULL 이면 값이 1 이고, NULL이 아니면 값이 0이 된다.
4) grouping_id 는 지정된 컬럼의 순서대로 2진수->10진수로 변환된 값을 갖는다.
ex) grouping_id (a,b,c) 일경우
a,b,c 순서대로
001 -> 1 : 최하위 소계
011 -> 3 : 중간 소계
111 -> 7 : 전체통계
※ grouping 함수는 select 구문 또는 having 구문에 바로 사용가능하다.
=>
1) select decode(grouping_id(a,b,c), 7, '합계', nm)
2) .....
group by rollup (a,b,c)
having grouping_id(a,b,c) in (0, 7)
전체 합계만 출력하고 싶을때 (소계는 출력안함)
※ ROLLUP 에 괄호()를 한번더 감싼다.
SELECT NM, TYPE, SUM(PRICE) FROM TBL
GROUP BY ROLLUP ((NM, TYPE));
소계만 출력하고 싶을때 (전체는 출력안함)
※ group by 앞에 소계기준 컬럼을 표시한다.
SELECT NM, TYPE, SUM(PRICE) FROM TBL
GROUP BY NM, ROLLUP (TYPE);
전체 또는 소계를 구할때, HAVING GROUPING_ID(COL1,2,3...) IN (0,1,...) 로 구하기
※ 그룹 컬럼이 여러개 있을경우 원하는 소계만을 출력하고 싶을때 사용하면 더욱편리하다.
SELECT NM, TYPE, SUM(PRICE) FROM TBL
GROUP BY ROLLUP (NM, TYPE);
HAVING GROUPING_ID(NM,TYPE) IN (0, 3) : 전체합계만
HAVING GROUPING_ID(NM,TYPE) IN (0, 1) : 소계만
CUBE 와 GROUPING SETS
a) CUBE
※ 나열한 컬럼에 대한 모든 집계를 나타낸다. ( N² )
SELECT
X,Y,Z, SUM(PRICE)
FROM TBL
GROUP BY CUBE (X,Y,Z)
b) GROUPING SETS
※ 나열한 컬럼에 대한 소계에 속하는 부분만 추출
SELECT
X,Y,Z, SUM(PRICE)
FROM TBL
GROUP BY GROUPING SETS (X,Y,Z)