MSSQL - ROLLUP / CUBE
SQL표준 함수 분류
1. AGGREGATE FUNCTION : count , sum , avg , max , min 등 각종 집계 함수
2. GROUP FUNCTION : 결산 개념의 업무, 소계, 중계, 합계, 총 합계등 보고서를 만드는 기능
3. WINDOW FUNCTION : 분석함수나 순위함수 같은 데이터 웨어하우스에서 발전한 기능요약
- ROLLUP : 소그룹간 소계 출력
- CUBE : GROUP BY 항목들과 다차원 소계 출력
- GROUPING SETS : 특정항목에 대한 소계 출력
* DECODE함수는 CASE문이랑 동일.
* ROLLUP 연산자
- GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성, 각 그룹에 계산함수를 적용
- GROUP BY절의 결과는 누적 계산 결과
* CUBE 연산자
- GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성.
* GROUPING 함수
- 각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용
- 해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환
- GROUP BY절에 나타나는 컬럼에 적용
ROLLUP
GROUP BY에 있는 컬럼들을 오른쪽에서 왼쪽순으로 그룹을 생성
a,b,c,d를 집계할 경우
a, b, c, d 로 묶이는 그룹
a, b, c 로 묶이는 그룹
a, b 로 묶이는 그룹
a 로 묶이는 그룹
전체
이렇게 조합 결과를 보여준다.
[예제] ROLLUP
|
1 2 3 4 5 6 7 8 9 10 |
WITH A AS ( SELECT '학용품' AS '분류', '연필' AS '내용', 1 AS '개수', 400 AS '금액' UNION ALL SELECT '음식', '김밥', 1, 2000 UNION ALL SELECT '음식', '제육덮밥', 1, 4800 UNION ALL SELECT '학용품', '지우개', 3, 1200 UNION ALL SELECT '학용품', '샤프', 2, 800 UNION ALL SELECT '기타', '게임', 1, 100 ) SELECT * FROM A |
|
1 2 3 |
SELECT 분류, SUM(개수) AS '개수', SUM(금액) AS '금액' FROM A GROUP BY 분류 |
|
1 2 3 4 |
SELECT 분류, SUM(개수) AS '개수', SUM(금액) AS '금액' FROM A GROUP BY 분류 WITH ROLLUP |
GROUP BY을 사용하면, 분류컬럼을 기준으로 그룹으로 묶어 개수와 금액의 합계를 구할 수 있다.
ROLLUP을 추가해주면, GROUP BY로 묶어준 그룹의 합계들에 대한 총 합계까지 구할 수 있다.
[예제2]
|
1 2 3 4 5 6 7 8 9 10 11 |
WITH A AS ( SELECT '학용품' AS '분류', '연필' AS '내용', 1 AS '개수', 400 AS '금액' UNION ALL SELECT '음식', '김밥', 1, 2000 UNION ALL SELECT '음식', '제육덮밥', 1, 4800 UNION ALL SELECT '음식', '제육덮밥', 2, 9600 UNION ALL SELECT '학용품', '지우개', 3, 1200 UNION ALL SELECT '학용품', '샤프', 2, 800 UNION ALL SELECT '기타', '게임', 1, 100 ) SELECT * FROM A |
|
1 2 3 |
SELECT 분류, 내용, SUM(개수) AS '개수', SUM(금액) AS '금액' FROM A GROUP BY 분류, 내용 |
|
1 2 3 4 |
SELECT 분류, 내용, SUM(개수) AS '개수', SUM(금액) AS '금액' FROM A GROUP BY 분류, 내용 WITH ROLLUP |
GROUP BY을 사용하면, 분류컬럼과 내용컬럼을 기준으로 그룹으로 묶어 개수와 금액의 합계를 구할 수 있다.
ROLLUP을 추가해주면 GROUP BY의 첫번째에 해당하는 분류컬럼 그룹 값들의 소계,
그 소계들의 합에 해당하는 총합계를 구할 수 있다.
[예제3]
GROUP BY로 개수까지 묶어서 그룹을 만들어 준다면??
|
1 2 3 |
SELECT 분류, 내용, 개수, SUM(금액) AS '금액' FROM A GROUP BY 분류, 내용, 개수 |
|
1 2 3 4 |
SELECT 분류, 내용, 개수, SUM(금액) AS '금액' FROM A GROUP BY 분류, 내용, 개수 WITH ROLLUP |
GROUP BY을 사용해도 분류, 내용, 개수로 묶이는 그룹이 없기 때문에 테이블A와 결과가 동일하다.
하지만, ROLLUP을 추가해주면
GROUP BY의 첫번째와 두번째에 해당하는 분류컬럼과 내용컬럼의 값이 같은 그룹 값들의 소계,
첫번째에 해당하는 분류컬럼의 값과 같은 그룹 값들의 소계2,
그 소계들의 합에 해당하는 총합계를 구할 수 있다.
[예제] ROLLUP, CUBE를 비교
|
1 2 3 4 5 |
SELECT 분류, 내용, SUM(개수) AS '개수', SUM(금액) AS '금액' FROM A GROUP BY 분류, 내용 --WITH ROLLUP --WITH CUBE |
ROLLUP
(GROUP BY에 있는 컬럼들을 오른쪽에서 왼쪽순으로 그룹 생성)
a, b 로 묶이는 그룹의 값
a 로 묶이는 그룹의 소계
전체합계
CUBE
(나올 수 있는 모든 경우의 수로 그룹 생성)
a, b 로 묶이는 그룹의 값
a 로 묶이는 그룹의 소계
b 로 묶이는 그룹의 소계
전체합계
이렇게 조합 결과를 보여준다. 즉, ROLLUP보다 CUBE가 좀 더 상세하게 소계를 낸다.