[MS-SQL] ROLLUP 과 CUBE 의 차이

[MS-SQL] ROLLUP 과 CUBE 의 차이

 

USE TEMPDB

IF EXISTS (SELECT * FROM SYSOBJECTS O WHERE O.NAME LIKE '#T_INCOME%') 
BEGIN
 DROP TABLE #T_INCOME
END 
ELSE
BEGIN 
CREATE TABLE #T_INCOME(
 INC_JISA   CHAR(4) NOT NULL 
 , INC_JIJUM   CHAR(4) NOT NULL
 , INC_CENTER   CHAR(5) NOT NULL
 , INC_DATE   CHAR(8) NOT NULL
 , INC_AMT   INT

 CONSTRAINT  PK_INCOME   
 PRIMARY KEY(INC_JISA  , INC_JIJUM , INC_CENTER , INC_DATE)

)
INSERT INTO #T_INCOME 
VALUES ( 
 '0001', '0001', 'AB340', '20070901', 45000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0001', '0001', 'AB340', '20070902', 32000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0002', '0001', 'AB340', '20070901', 75000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0002', '0001', 'AB340', '20070902', 15000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0001', '0002', 'AB340', '20070901', 40000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0001', '0002', 'AB340', '20070902', 35000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0001', '0001', 'AB240', '20070901', 48000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0001', '0001', 'AB240', '20070902', 95000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0002', '0001', 'AB240', '20070901', 25000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0002', '0001', 'AB240', '20070902', 75000
) 
INSERT INTO #T_INCOME 
VALUES ( 
 '0002', '0002', 'AB240', '20070901', 65000
)

INSERT INTO #T_INCOME 
VALUES ( 
 '0002', '0002', 'AB240', '20070902', 85000
)

END

SELECT 
 CASE 
  WHEN GROUPING(INC_JISA)  =  1 THEN '지사별합계' 
  ELSE INC_JISA 
  END AS INC_JISA
 ,CASE 
  WHEN GROUPING(INC_JIJUM) = 1 THEN '지점별합계' 
  ELSE INC_JIJUM
  END AS INC_JIJUM
 ,CASE 
  WHEN GROUPING(INC_CENTER) = 1 THEN '센터별합계' 
  ELSE INC_CENTER
  END AS INC_CENTER
 ,CASE 
  WHEN GROUPING(INC_DATE) = 1 THEN '일자별합계' 
  ELSE INC_DATE
  END AS INC_DATE
 ,SUM(INC_AMT) AS AMT
FROM #T_INCOME
GROUP BY INC_JISA, INC_JIJUM, INC_CENTER, INC_DATE 
--WITH ROLLUP 
WITH CUBE

각각 조회된 결과를 이미지를 통하여 보면

ROLLUP 과 CUBE에서의 공통된 데이터 -- 

CUBE에서의 새로이 추가된 데이터--

ROLLUP과 CUBE에서의 공통된 데이터가 존재하는것을 알 수 있다.

하지만 CUBE는 GROUP BY 된 필드들만의 합계외에도 각각의 GROUP 기준 필드들 사이에서의 집계 값을 구한다

CUBE 가 계산한 각 GROUP BY 에서의 필드간의 합계된 데이터들 중에서 
필요로 하고자 하는 값만 뽑고자 한다면 
인라인 뷰를 이용하여 
조건절에 원하는 형태를 넣어서 조회가 가능하다

SELECT * 
FROM 
(
SELECT 
 CASE 
  WHEN GROUPING(INC_JISA)  =  1 THEN '지사별합계' 
  ELSE INC_JISA 
  END AS INC_JISA
 ,CASE 
  WHEN GROUPING(INC_JIJUM) = 1 THEN '지점별합계' 
  ELSE INC_JIJUM
  END AS INC_JIJUM
 ,CASE 
  WHEN GROUPING(INC_CENTER) = 1 THEN '센터별합계' 
  ELSE INC_CENTER
  END AS INC_CENTER
 ,CASE 
  WHEN GROUPING(INC_DATE) = 1 THEN '일자별합계' 
  ELSE INC_DATE
  END AS INC_DATE
 ,SUM(INC_AMT) AS AMT
FROM #T_INCOME
GROUP BY INC_JISA, INC_JIJUM, INC_CENTER, INC_DATE 
WITH CUBE
) R

WHERE (R.INC_JISA = '지사별합계' AND R.INC_JIJUM <> '지점별합계' AND R.INC_CENTER = '센터별합계'  AND R.INC_DATE <> '일자별합계') 
OR (R.INC_JISA <> '지사별합계' AND R.INC_JIJUM <> '지점별합계' AND R.INC_CENTER <> '센터별합계' AND R.INC_DATE <> '일자별합계')  
OR (R.INC_JISA <> '지사별합계' AND R.INC_JIJUM = '지점별합계' AND R.INC_CENTER = '센터별합계' AND R.INC_DATE <> '일자별합계')

결과 값

CUBE를 사용하지 않고 위와 같은 결과를 얻으려면 각 GROUP BY기준을 달리하여 여러개의 쿼리를

UNION 을 이용하여 조회하여야 하는 번거로움이 있다.

CUBE를 적절하게 이용하면 많은 도움이 될 것 같다

그러나 UNION 을 사용하게 될 경우의 장점은 쿼리 실행 계획으로 보면 좀 더 적은 비용으로 원하는

값을 추출할 수 있다는 것을 알 수 있다. 이유는 CUBE가 모든 GROUP 에 대한 경우를 반환하기

때문이다.

먼저 UNION 을 이용한 다중 쿼리 실행을 통한 동일한 결과를 조회하는 쿼리이다.

SELECT INC_JISA , INC_JIJUM , INC_CENTER, INC_DATE, SUM(INC_AMT) AS INC_AMT
FROM #T_INCOME 
GROUP BY INC_JISA, INC_JIJUM, INC_CENTER, INC_DATE

UNION ALL

SELECT INC_JISA, '지점별합계' AS INC_JIJUM , '센터별합계' AS INC_CENTER, INC_DATE, SUM(INC_AMT)  AS INC_AMT
FROM #T_INCOME
GROUP BY INC_JISA, INC_DATE

UNION ALL

SELECT '지사별합계' AS INC_JISA , INC_JIJUM, '센터별합계' AS INC_CENTER, INC_DATE, SUM(INC_AMT) AS INC_AMT 
FROM #T_INCOME
GROUP BY INC_JIJUM, INC_DATE

다음으로 CUBE와 UNION 의 쿼리 실행 계획을 보면

CUBE를 사용한 쿼리 실행 계획 -- 

UNION 을 사용한 쿼리 실행 계획 -- 

두개를 비교해보면 CUBE를 사용한 것이 더 복잡한 실행계획을 가지고 있을을 알 수 있다.