[MSSQL] ROLLUP / CUBE / GROUPING SETS 소계 합계

 

 

 

ROLLUP

- ROLLUP 함수는 소그룹 간의 소계를 계산한다.

 

- ROLLUP은 GROUP BY의 확장 형태로 사용하기 쉬운데, GROUP BY에 있는 항목들을 오른쪽에서 왼쪽순으로 그룹으로 묶어 집계를 낸다.

 

- GROUP BY a, b, c, d 로 묶은 뒤 ROLLUP 을 적용시켜 주면

 

-> (a, b, c, d) / (a, b, c) / (a, b) / (a) / () 이런식으로 그룹을 만들어가며 집계를 낸다.

 

 

 

예제

 

원본 데이터

 

 

1단계. GROUP BY로 묶은 뒤 SUM

 

SELECT item, SUM(cost) sum
FROM sql_test_a
GROUP BY item

 

결과 데이터

 

 

3단계. ROLLUP 적용

 

-- 방법1.
SELECT item, SUM(cost) sum
FROM sql_test_a
GROUP BY item
WITH ROLLUP

-- 방법2.
SELECT item, SUM(cost) sum
FROM sql_test_a
GROUP BY ROLLUP (item)

 

ROLLUP을 통해 GROUP BY 로 묶어준 그룹의 총 합계까지 결과로 보여준다.

 

결과 데이터

 

 

 

 

심화

 

원본 데이터

 

 

1단계. GROUP BY로 묶은 뒤 SUM

 

SELECT store, item, SUM(cnt) AS all_cnt, SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY store, item

 

결과 데이터

 

 

2단계. ROLLUP 적용

 

-- 방법1.
SELECT store, item, SUM(cnt) AS all_cnt, SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY store, item
WITH ROLLUP

-- 방법2.
SELECT store, item, SUM(cnt) AS all_cnt, SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY ROLLUP (store, item)

 

ROLLUP은 GROUP BY 후 오는 항목들을 오른쪽에서 왼쪽으로 묶어 집계를 낸다.

1. store, item 묶음

2. store 묶음 (소계)

3. 전체 묶음 (총계)

의 결과를 보여준다.

 

결과 데이터

 

 

 

심화2. GROUPING 적용

 

위의 예제에서 합계 소계의 컬럼의 값이 (null)로 보여지는 것이 거슬렸다면? GROUPING을 이용해보자

GROUPING은 결과가 NULL일때 1, NULL이 아닐때 0의 값을 반환한다.

 

SELECT
    GROUPING(store) AS grp_store
  , store
  , GROUPING(item) AS grp_item
  , item
  , SUM(cnt) AS all_cnt
  , SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY store, item
WITH ROLLUP

 

결과 데이터

 

 

GROUPING의 결과를 확인했다면, CASE 조건문을 이용해서 최종 결과를 만들어보자

 

SELECT
  CASE GROUPING(store) WHEN 1 THEN 'total' ELSE store END AS store
  , CASE GROUPING(item) WHEN 1 THEN 'subtotal' ELSE item END AS store
  , SUM(cnt) AS all_cnt
  , SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY store, item
WITH ROLLUP

 

결과 데이터

 

 

 

---

 

 

CUBE

- CUBE 함수는 GROUP BY 항목들 간 모든 경우의 수로 그룹을 생성하여 집계를 낸다.

 

- CUBE는 ROLLUP보다 조금 더 상세한 결과를 낸다.

 

- CUBE는 ROLLUP과 그룹을 묶어 주는 방식이 다르다.

 

ROLLUP (a, b, c) : (a, b, c) / (a, b) / (a) / ()

CUBE (a, b, c) : (a, b, c) / (a, b) / (a, c) / (b, c) / (a) / (b) / (c) / ()

 

 

위에서 심화 예제에서의 ROLLUP 대신 CUBE를 사용하면 다음과 같은 결과를 얻을 수 있다.

 

-- 방법1.
SELECT item, SUM(cnt) AS all_cnt, SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY store, item
WITH CUBE

-- 방법2.
SELECT item, SUM(cnt) AS all_cnt, SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY CUBE (store, item)

 

ROLLUP과 달리 item의 소합계가 추가로 등장한다.

 

결과 데이터

 

 

---

 

 

GROUPING SETS

- GROUPING SETS은 특정 항목에 대한 집계를 낸다.

 

- UNION ALL과 같은 결과를 낸다.

 

ROLLUP (a, b, c) : (a, b, c) / (a, b) / (a) / ()

CUBE (a, b, c) : (a, b, c) / (a, b) / (a, c) / (b, c) / (a) / (b) / (c) / ()

GROUPING SETS(a, b, c) : (a) / (b) / (c)

 

위에서 심화 예제에서의 ROLLUP 대신 GROUPING SETS를 사용하면 다음과 같은 결과를 얻을 수 있다.

 

SELECT store, item, SUM(cnt) AS all_cnt, SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY GROUPING SETS (store, item)

 

결과 데이터

 

 

---

 

 

ROLLUP / CUBE / GROUPING SETS 비교

 

원본 데이터

 

ROLLUP 결과 데이터

 

CUBE 결과 데이터

 

GROUPING SETS 결과 데이터

 

 

 

ROLLUP (소계, 합계)