1. 분석함수 = Window Function
▪ BUSINESS 분야에서 자주 행하여지는 여러 가지 형태의 분석에 유용하게 활용될 수 있는 함수
▪ 각 window별 집합 연산을 수행한 결과를 return하는 함수
▪ JOIN 이나 프로그램의 부하(Over Head)를 줄임
▪ 간결한 SQL로 복잡한 분석작업을 수행가능
▪ 이해 및 활용이 용이
SELECT ANALYTIC_FUNCTION(args)
OVER (
[PARTITION BY 컬럼 LIST]
[ORDER BY 컬럼LIST]
[WINDOWING절(ROWS | RANGE BETWEEN)]
)
FROM 테이블명; ANALYTIC FUNCTION : 분석함수명(입력인자)
OVER : 분석함수임을 나타내는 키워드
PARTITION BY : 계산 대상 그룹 지정 >>GROUP BY와 비슷한 역할. 이 구문이 없으면 전체 데이터에 대해 계산이 적용됨.
ORDER BY : 대상 그룹에 대한 정렬 수행
WINDOWING 절 : 분석함수의 계산 대상 범위 지정
(ORDER BY절에 종속적.
기본 생략 구문 : 정렬된 결과의 처음~현재행까지)
분석함수 수행 절차
JOIN, WHERE조건절, GROUP BY, HAVING절
→ PARTITION 생성 (각각의 PARTITION 의 ROW에 분석함수 적용)
→ ORDER BY
분석함수 종류
RANK
값의 그룹에서 값의 순위 계산
각 로우마다 순위를 매겨주는 함수
PARTITION 내에서 ORDER BY절에 명시된 대로 정렬한 후의 순위를 의미하고 1부터 시작하여 동일.
한 값은 동일한 순위를 가지며, 동일한 순위의 수만큼 다음 순위는 건너뛴다.
--사원번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위를 조회한다.
SELECT empno, ename, deptno, sal ,
RANK() over (ORDER BY sal DESC ) "RANK"
FROM EMP;DENSE_RANK
ORDER BY 절에 사용된 컬럼이나 표현식에 대하여 순위 부여하는데,
RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값 리턴.
--사원번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위를 조회한다.(dense_rank()함수 이용)
SELECT empno, ename, deptno, sal
,DENSE_RANK() over (ORDER BY sal DESC) AS "RANK"
FROM EMP;ROW_NUMBER
분할별로 정렬된 결과에 대해 순위 부여.
1로 시작하는 ORDER_BY_CLAUSE에서 지정된 행의 순위 순서로,
적용되는 각 행에 UNIQUE 순서 할당.
(ROWNUM과는 관계 없음)
--사원번호, 이름, 연봉,입사일, 순번조회 (급여가 많은 순으로, 같은 급여를 받는 경우 입사일이 빠른 사람부터 순번부여)
SELECT empno, ename, sal, hiredate
,row_number() over (ORDER BY sal DESC, hiredate asc ) AS "순번"
FROM EMP;RATIO_TO_REPORT
해당 구간에서 차지하는 비율을 리턴
--사원의 총 월급을 50000으로 증가했을 때, 기존 월급 비율로 적용했을 경우 각 사원은 얼마씩 받게 되는지 조회한다.
SELECT ename,sal
,ratio_to_report (sal) over () AS "비율"
,TRUNC(ratio_to_report (sal) over () * 50000 ) AS "받게 될 급여"
FROM EMP;LAG/LEAD
특정 로우가 속한 파티션내에서 상대적 상하 위치에 있는 특정 로우의 컬럼 값을 참조하거나
호 비교하고자 할 때 사용할 수 있는 function들의 집합.
LAG : 현재 행 기준으로 이전 값 참조
LEAD : 현재 행을 기준으로 이후의 값 참조
**LAG | LEAD** (EXPR [,OFFSET[P,DEFAULT])
**OVER** ([PARTITION_BY_CLAUSE] ORDER_BY_CLAUSE)SELECT empno, ename, job, sal
,LAG(empno) OVER(ORDER BY empno) AS empno_prev,
,LEAD(empno) OVER(ORDER BY empno) AS empno_next
FROM emp
WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN');
SELECT empno , ename, job, sal
,LAG(empno, 2, 9999) OVER(ORDER BY empno) AS empno_prev
FROM emp
WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN');
SELECT empno, ename, job, sal,
,LAG(job) OVER(PARTITION BY job ORDER BY empno) AS empno_prev
FROM emp
WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN');EXPR : 대상 컬럼명
OFFSET : 값을 가져올 행의 위치 기본값은 1, 생략 가능
DEFAULT : 값이 없을경우 기본값(=NULL), 생략가능
PARTITION_BY_CLAUSE : 그룹 컬럼명, 생략가능
ORDER_BY_CLAUSE : 정렬 컬럼명, 필수
FIRST_VALUE
윈도우에서 정렬된 값 중에서 첫번째 값 리턴
LAST_VALUE
윈도우에서 정렬된 값 중 마지막 값 리턴
2. 집계함수
윈도우 집계 유형 (Window Aggregate Family)
윈도우를 근간으로, 정렬된 로우들의 집합과 각각의 로우들에 대한 집계 값을 반환한다.
보고용 집계 유형 (Reporting Aggregate Family)
한 집합 레벨에 대한 집계 값과 다른 집합 레벨에 대한 집계 값의 비교를 통해
분석작업을 하고자 하는 경우가 많다.
예제 1) 사원이름, 부서번호, 급여, 전체급여합계, 부서별급여합계를 조회한다.
select ename, deptno, sal,
sum(sal) OVER() AS "전체급여합계", --파티셔닝 되지 않음(전체 데이터 대상)
sum(sal) OVER(PARTITION BY deptno) AS "부서급여합계"
from emp; 예제5) 사원이름, 업무, 급여, 업무별 급여평균, 해당업무의 최대급여를 조회한다.
select ename, job, sal
,avg(sal) OVER(PARTITION BY job) AS "업무 평균급여"
,max(sal) OVER(PARTITION BY job) AS "업무 최대급여"
from emp; 예제6) 사원이름,부서번호,급여합계를 3줄씩 더한 결과, 누적합계 조회한다.
SELECT ename, deptno, sal
,SUM(sal) over( ORDER BY sal
rows BETWEEN 1 preceding AND 1 following) "sum1" --현재 행을 기준으로 바로 앞, 뒤의 행 1개씩을 합한 값
,SUM(sal) over (ORDER BY sal rows unbounded preceding) "sum2" --처음부터 현재까지의 행을 합한 값
FROM EMP;