[SQLD] ROLLUP

스타벅스 테이블을 예로 들면,

 

-- 스타벅스 테이블 조회
SELECT * FROM TAB_STARBUCKS;

-- 스타벅스 음료수 그룹 조회
SELECT ORDER_ITEMS, COUNT(*)
FROM TAB_STARBUCKS
GROUP BY ORDER_ITEMS;

-- 결과 음료수 이름으로 그룹이된 값이 나온다.

-- 스타벅스 날짜 그룹 조회
SELECT ORDER_DT, COUNT(*)
FROM TAB_STARBUCKS
GROUP BY ORDER_DT;

-- 결과 날짜 별로 그룹이 된 값이 나온다.

-- 스타벅스 음료수 그룹, 날짜 그룹 같이 조회
SELECT ORDER_ITEMS, ORDER_DT, COUNT(*)
FROM TAB_STARBUCKS
GROUP BY ORDER_DT, ORDER_ITEMS
ORDER BY ORDER_DT ASC;

-- 결과 02 12일 아메리카노 , 02 12일 카페라떼 의 개수가 나온다
-- 여기서 이제 ROLLUP 함수를 쓰게 되면

SELECT ORDER_ITEMS, ORDER_DT, COUNT(*)
FROM TAB_STARBUCKS
GROUP BY ROLLUP(ORDER_DT, ORDER_ITEMS)
ORDER BY ORDER_DT ASC;

-- 결과값으로 02 12일의 총 COUNT도 나오고 13일 총 COUNT 나오면서 마지막에는 총 COUNT값이 나온다

---

108p에 있는 테이블과 데이터를 oracle로 만들어서 직접 실행해본다

 

서비스 테이블 

CREATE TABLE 서비스 (
	서비스ID		VARCHAR2(3) PRIMARY KEY,
	서비스명		VARCHAR2(10)
);

CREATE TABLE 서비스가입 (
	회원번호		VARCHAR2(1),
	서비스ID		VARCHAR2(3),
	가입일자		VARCHAR2(10),
	CONSTRAINTS 서비스가입PK PRIMARY KEY (회원번호, 서비스ID)
);


INSERT INTO 서비스 VALUES('001','서비스1');
INSERT INTO 서비스 VALUES('002','서비스2');
INSERT INTO 서비스 VALUES('003','서비스3');
INSERT INTO 서비스 VALUES('004','서비스4');

INSERT INTO 서비스가입 VALUES('1','001','2013-01-01');
INSERT INTO 서비스가입 VALUES('1','002','2013-01-02');
INSERT INTO 서비스가입 VALUES('2','001','2013-01-01');
INSERT INTO 서비스가입 VALUES('2','002','2013-01-02');
INSERT INTO 서비스가입 VALUES('2','003','2013-01-03');
INSERT INTO 서비스가입 VALUES('3','001','2013-01-01');
INSERT INTO 서비스가입 VALUES('3','002','2013-01-02');
INSERT INTO 서비스가입 VALUES('3','003','2013-01-03');

SELECT 
   CASE WHEN GROUPING(A.서비스ID) = 0 THEN A.서비스ID ELSE '합계' END AS 서비스ID
 , CASE WHEN GROUPING(B.가입일자) = 0 THEN NVL(B.가입일자, '-') ELSE '소계' END AS 가입일자
 , COUNT(B.회원번호) AS 가입건수
FROM 서비스 A LEFT OUTER JOIN 서비스가입 B 
ON (A.서비스ID = B.서비스ID AND B.가입일자 BETWEEN '2013-01-01' AND '2013-01-31')
GROUP BY ROLLUP( A.서비스ID, B.가입일자);

미리 결과 해석

서비스 A에 조인해서

아이디 같은거 그리고 가입일자가 13년1월1일 부터 13년1월31일 까지

ROLLUP을 하게 되면 서비스 ID도 그룹이 되고, 가입일자로도 그룹이 되어서 같이 출력

그러면 001,002,003이 하나만 나오는게 아니고 일자별, 그리고 소계까지 나와줘야함

 

SELECT 절로 가서

서비스ID 그루핑한 부분이 0이면? NULL? 004번은 없으니 

THEN 서비스ID는 '합계'로 바꾸고,

두번째 CASE문에서 만약 가입일자에 NULL이면 '-'로 표시한다

 

---

SELECT *
FROM 서비스 A LEFT OUTER JOIN 서비스가입 B
    ON(A.서비스ID = B.서비스ID);

먼저 조인한 결과 값이 어떻게 나오는지 확인한다.

LEFT OUTER JOIN을 했으므로 서비스에 있는 값은 다나옴.

SELECT A.서비스ID , B.가입일자 , COUNT(B.회원번호) AS 가입건수
FROM 서비스 A LEFT OUTER JOIN 서비스가입 B
    ON(A.서비스ID = B.서비스ID)
GROUP BY A.서비스ID, B.가입일자
ORDER BY A.서비스ID;

SELECT A.서비스ID , B.가입일자 , COUNT(B.회원번호) AS 가입건수
FROM 서비스 A LEFT OUTER JOIN 서비스가입 B
    ON(A.서비스ID = B.서비스ID)
GROUP BY ROLLUP(A.서비스ID, B.가입일자)
ORDER BY A.서비스ID;

ROLLUP을 하게 되면 집계함수처럼 그룹별로 집계 통계가 상세하게 나온다.

 

SELECT 
   CASE WHEN GROUPING(A.서비스ID) = 0 THEN A.서비스ID ELSE '합계' END AS 서비스ID
 , CASE WHEN GROUPING(B.가입일자) = 0 THEN NVL(B.가입일자, '-') ELSE '소계' END AS 가입일자
 , COUNT(B.회원번호) AS 가입건수
FROM 서비스 A LEFT OUTER JOIN 서비스가입 B 
ON (A.서비스ID = B.서비스ID AND B.가입일자 BETWEEN '2013-01-01' AND '2013-01-31')
GROUP BY ROLLUP( A.서비스ID, B.가입일자);

그래서 이렇게 하게되면

일단 GROUPING 함수로 A.서비스ID 값이 0이면 서비스ID값을 보여주게 하고 NULL이면 합계를 보여주는 것

밑에도 동일하고 NVL함수를 쓰게 되면 이제 가입일자가 NULL이면 '-'를 보여주고 아니면 소계를 보여준다