* 그림처럼 색이 칠해져 이쓴 부분의 결과를 얻고 싶은 쿼리를 작성하고자 할때 사용한다.
* 내부적으로
ROLLUP
사용예 ) ... GROUP BY ROLLUP(COURSE_CODE,YEAR);
각 과정별 년도별 요약열이 추가된다.
(년도가 공백으로 표현된 열이 요약열 -예:SUM-
과정과 년도가 모두 공백인 열은 전체의 요약열이다. )
과정 년도 NVL(SUM(DEPOSIT_AMOUNT),0)
---------- ---- --------------------------
14 2000 270000
14 270000
28 1999 0
28 2000 20560000
28 20560000
29 1998 2250000
29 1999 0
29 2000 16469800
29 2001 2250000
29 20969800
41799800
-----------------------------------------------------
CUBE 연산자
사용예) GROUP BY CUBE(COURSE_CODE,YEAR);
ROLLUP과 같은 결과와 같이 보이지만 년도별 요약이 추가되었다.
과정 년도 NVL(SUM(DEPOSIT_AMOUNT),0)
---------- ---- --------------------------
14 2000 270000
14 270000
28 1999 0
28 2000 20560000
28 20560000
29 1998 2250000
29 1999 0
29 2000 16469800
29 2001 2250000
29 20969800
1998 2250000
1999 0
2000 37299800
2001 2250000
41799800
---------------------------------------------------------------------------
* SELECT 문의 GROUP BY 절에 사용
* ROLLUP 보다 CUBE의 계산량이 많다.
* v8.1.x, PL/SQL 에서는 CUBE, ROLLUP이 지원되지 않지만,
Native Dynamic SQL을 사용함으로써 사용 가능함
* 카테시안 곱 랑고리즘
* GROUPING() 함수와 많이 사용함
-------------------------------------------------------------------------------------
1)ROLLUP
--------------------------------------------------------------------------------------
[쥔장] =================================
나름 유용한 쿼리이다. 잘 활용해보길..
========================================
select
decode(row_number() over(partition by proc_date order by user_no), 1, proc_date, ' ') proc_date,
nvl(decode(grouping_id(proc_date, user_no), 1, '소계', user_no), '합계') user_no,
sum(decode(terminal_type, '1', 1, 0)) machine_data,
sum(decode(terminal_type, '2', 1, 0)) localpc_data
from user_terminal_use_list
where 1=1
-- and dlns_dt between '20110501' and '20110515'
-- and dlns_dt between '20110516' and '20110529'
-- and dlns_dt between '20110530' and '20110531'
and status_type = '0'
group by rollup(proc_date, user_no)
order by proc_date, user_no
;
여기서 포인트를 보자면..
1) decode(row_number() over(partition by proc_date order by user_no), 1, proc_date, ' ') proc_date 와
2) group by rollup(proc_date, user_no) 이다.
1) 은 user_no에 의한 proc_date를 뽑아내는 것.
row_number() : 무조건 순서대로 순번을 뽑아낸다.
over (partition by proc_date...) proc_date 기준으로 분류를 해서...
즉, proc_date 기준으로 분류를 하는데 무조건 순번을 뽑아내고, proc_date 기준으로 순번이 1이라면 proc_date 값을 넣고, 아니면 빈값을 넣어주라...
2) 는 proc_date, user_no 에 의한 그룹화시켜 이에 대응되는 데이터의 합을 내준다.
결과는...
| proc_date | user_no | machine_date | localpc_data |
| 20110302 | 100 | 1 | 2 |
| 101 | 0 | 2 | |
| 102 | 0 | 6 | |
| 103 | 0 | 4 | |
| 104 | 0 | 24 | |
| 105 | 0 | 3 | |
| 106 | 0 | 5 | |
| 107 | 0 | 6 | |
| 소계 | 1 | 52 | |
| 20110303 | 100 | 0 | 39 |
| 101 | 1 | 37 | |
| 102 | 0 | 88 | |
| 104 | 7 | 32 | |
| 105 | 20 | 16 | |
| 106 | 0 | 15 | |
| 107 | 0 | 17 | |
| 소계 | 28 | 224 | |
| 합계 | 29 | 276 |
이와 같은 형태로 나온다.
ROLLUP
|
GROUP BY 절에 사용되어 추가적인 그룹핑 정보를 표현할 수 있는 함수이다. 그룹핑된 결과에 그룹별 합계 정보를 추가한다. |
|
SELECT Row_Number() over(PARTITION BY b.DTL_RGN_CODE order by GUBUN), 1,b.DTL_RGN_CODE,' ') rn2, |
[결과]