[SQL] GROUP BY 확장 : ROLLUP, CUBE, GROUPING SET, 분석함수
1.ROLLUP
: 하위 총계와 같은 누적 집계를 산출할 수 있다.
>부서마다 집계를 하고 싶을 때 ROLLUP 사용
SELECT
FROM
GROUP BY ROLLUP(컬럼1, 컬럼2, ...) -- 뒤에 있는 컬럼이 먼저 지워지며 롤업 진행!
>>>>>괄호 안에 들어오는 컬럼의 순서에 따라 결과가 달라진다!
2.CUBE
:하나의 SELECT문으로 CROSS TABULATION 값을 산출 할 수 있다.
SELECT
FROM
GROUP BY CUBE(컬럼1, 컬럼2, ...) --가능한 집계를 전부함.
>>컬럼1,컬럼2 집계
>>컬럼1 집계
>>컬럼2 집계
>>전체 집계
3.GROUPING 함수
:ROLLUP,CUBE 연산자와 함께 사용한다.
-ROLLUP과 CUBE에 소계가 계산된 결과에는 1이 표시가 된다.
SELECT GROUPING(컬럼1), GROUPING(컬럼2)
FROM
GROUP BY [ROLLUP | CUBE] (컬럼1, 컬럼2)
4.GROUPING SETS
:GROUP BY의 확장 기능, 내가 사용할 그룹을 만들 수 있음.
SELECT
FROM
GROUP BY GROUPING SETS ((컬럼1,컬럼2,컬럼3),(컬럼3,컬럼6),(컬럼2,컬럼5)) --이런식으로 원하는 그룹 생성.
5.분석함수
:프로그램의 오버헤드를 줄이고 복잡한 분석작업을 간결하게 수행가능하다.
SELECT 함수
OVER( [PARTITION BY 컬럼 리스트] //계산 대상 그룹을 정한다
[ORDER BY 컬럼 리스트] //정렬
[WINDOW 절 (ROWS|RANGE BETWEEN)] //분석함수 계산 대상 범위 지정 (어디를 기준으로 앞 뒤)
)
FROM
-RANK
:값의 그룹에서 순위를 계산
: 동일한 점수의 동일한 순위가 있다면 그 다음순위는 동일한 점수가 있는 수만큼 증가된다.
-DENSE_RANK
:동일한 점수의 동일 순위의 다음 순위는 수와 상관 없이 1 증가된 값을 돌려준다
:순위를 건너 뛰지 않음
-ROW_NUMBER
:선발하는 것.
<PARTITION BY>
select 컬럼, 컬럼, 컬럼
,sum(sal) OVER() AS "전체급여합계" //그룹 바이를 안 써도 집계함수를 사용 가능하다.
, sum(sal) OVER(PARTITION BY deptno) AS "부서급여합계" from emp; //파티션 바이로 대상 그룹을 지정해준다.
<WINDOWING 절>
,SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "SUM1" --앞에 1행 뒤에 1행 //ORDER BY절로 먼저 필터링 후에 연산! ORDER BY절 없으면 오류!
,SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) "SUM2" --앞에 행 계속 누적
<RATIO_TO_REPORT 함수>
:해당구간에서 차지하는 비율을 리턴하는 함수.
:>> 범위내 전체 SUM 값에서 비율을 계산함.
SELECT ename,sal
, ratio_to_report (sal) over () AS "비율"
, TRUNC(ratio_to_report (sal) over () * 50000 ) AS "받게 될 급여"
FROM EMP;
<LAG / LEAD>
LAG :이전 행의 값을 리턴 //행 기준 위에 있는거
LEAD : 다음 행의 값을 리턴 //행 기준 아래 있는거
EXPR : 대상 컬럼명
OFFSET : 값을 가져올 행의 위치 기본값은 1, 생략가능
DEFAULT : 값이 없을 경우 기본값(지정 안 하면 NULL), 생략가능
PARTITION BY : 그룹 컬럼명, 생략가능
ORDER BY : 정렬 컬럼명, 필수 (정렬을 한 뒤에 그 결과에서 값을 가져오기 때문!)
LAG(EXPR [,OFFSET][,DEFAULT] OVER ( [PARTITION BY]ORDER BY )
LEAD(EXPR [,OFFSET][,DEFAULT] OVER ( [PARTITION BY]ORDER BY )
