[oracle]ROLLUP 활용

ROLLUP 활용

테이블 생성

조직별 회원별 판매실적 테이블 생성

CREATE TABLE SALE( 
	ITEM_CD VARCHAR2(10) , 
	MEMBER_ID NUMBER(10),
	SAL NUMBER(20)
);

데이터준비

INSERT ALL
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',1,10000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',1,20000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',4,40000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',1,560000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',2,30000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',5,70000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',4,870000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',2,330000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',2,260000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',1,110000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',5,60000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',3,770000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',3,1700000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',1,320000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',2,220000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',1,890000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',1,750000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',1,740000)
SELECT 1 FROM DUAL;

아이템별 판매량 합계

기본 GROUP BY 절 이용한다.

SELECT ITEM_CD, SUM(SAL)
FROM SALE 
GROUP BY ITEM_CD;

결과

ITEM_CD SUM(SAL)
1 I001 1,030,000
2 I002 2,410,000
3 I003 2,810,000
4 I004 1,140,000
5 I005 360,000

아이템별 판매량 합계및 총계

SELECT DECODE(ITEM_CD,NULL,'총계(원)',ITEM_CD) AS ITEM_CD, SUM(SAL)
FROM SALE 
GROUP BY ROLLUP(ITEM_CD);

결과

ITEM_CD SUM(SAL)
1 I001 1,030,000
2 I002 2,410,000
3 I003 2,810,000
4 I004 1,140,000
5 I005 360,000
6 총계(원) 7,750,000

아이템별 판매량및 총계및 회원별 소계

SELECT ITEM_CD, DECODE(GROUPING(ITEM_CD), 1, '총계') AS TOT , MEMBER_ID, DECODE (GROUPING(MEMBER_ID), 1,'소계') AS PSN ,SUM(SAL)
FROM SALE 
GROUP BY ROLLUP(ITEM_CD,MEMBER_ID)
ORDER BY ITEM_CD ASC , MEMBER_ID ASC;

결과

		ITEM_CD			TOT			MEMBER_ID			PSN			SUM(SAL)

1 I001 [NULL] 1 [NULL] 900,000
2 I001 [NULL] 5 [NULL] 130,000
3 I001 [NULL] [NULL] 소계 1,030,000
4 I002 [NULL] 1 [NULL] 770,000
5 I002 [NULL] 3 [NULL] 770,000
6 I002 [NULL] 4 [NULL] 870,000
7 I002 [NULL] [NULL] 소계 2,410,000
8 I003 [NULL] 1 [NULL] 740,000
9 I003 [NULL] 2 [NULL] 330,000
10 I003 [NULL] 3 [NULL] 1,700,000
11 I003 [NULL] 4 [NULL] 40,000
12 I003 [NULL] [NULL] 소계 2,810,000
13 I004 [NULL] 1 [NULL] 880,000
14 I004 [NULL] 2 [NULL] 260,000
15 I004 [NULL] [NULL] 소계 1,140,000
16 I005 [NULL] 1 [NULL] 110,000
17 I005 [NULL] 2 [NULL] 250,000
18 I005 [NULL] [NULL] 소계 360,000
19 [NULL] 총계 [NULL] 소계 7,750,000

GROUPING SETS 활용

조직별 판매량 총계 , 회원별 판매량 총계를 각각 따로 구한다.

SELECT ITEM_CD, MEMBER_ID, SUM(SAL)
FROM SALE 
GROUP BY GROUPING SETS(ITEM_CD,MEMBER_ID);

결과

ITEM_CD MEMBER_ID SUM(SAL)
1 [NULL] 1 3,400,000
2 [NULL] 4 910,000
3 [NULL] 2 840,000
4 [NULL] 5 130,000
5 [NULL] 3 2,470,000
6 I002 [NULL] 2,410,000
7 I003 [NULL] 2,810,000
8 I004 [NULL] 1,140,000
9 I005 [NULL] 360,000
10 I001 [NULL] 1,030,000

CUBE 활용

집계 가능한 모든 조합의 합계를 구한다.

SELECT ITEM_CD, MEMBER_ID, SUM(SAL)
FROM SALE 
GROUP BY CUBE(ITEM_CD,MEMBER_ID);

		ITEM_CD			MEMBER_ID			SUM(SAL)

1 [NULL] [NULL] 7,750,000
2 [NULL] 1 3,400,000
3 [NULL] 2 840,000
4 [NULL] 3 2,470,000
5 [NULL] 4 910,000
6 [NULL] 5 130,000
7 I001 [NULL] 1,030,000
8 I001 1 900,000
9 I001 5 130,000
10 I002 [NULL] 2,410,000
11 I002 1 770,000
12 I002 3 770,000
13 I002 4 870,000
14 I003 [NULL] 2,810,000
15 I003 1 740,000
16 I003 2 330,000
17 I003 3 1,700,000
18 I003 4 40,000
19 I004 [NULL] 1,140,000
20 I004 1 880,000
21 I004 2 260,000
22 I005 [NULL] 360,000
23 I005 1 110,000
24 I005 2 250,000