분석함수 전달인자 1, 전달인자 2, 전달인자 3])
OVER ( [ 쿼리 Partition 절
[Order by 절
[Window 절
FROM 테이블명
같은 순위가 있을 때는 다음 순위값이 건너뜀
같은 순위일 때도 순위값 건너뛰지 않음
partition by에 의해 나누어진 그룹별로 각 행을 order by절에 명시된 순서로 정렬후
partition을 bucket이라 불리는 그룹별로 나누고 partition내의 행을 bucket에 배치
각 partition 내의 값들을 order by절으로 정렬 후 순서대로 순번 부여
------------------행 누적SUM
--employee 테이블로 부터 부서코드가 30 인 직원들의
--사번, 급여, 해당 부서그룹(window)의 사번을 오름차순 정렬하고
--급여의 합계를 첫행부터 마지막행까지 구해서 win1에
SELECT empno, sal, SUM(sal) OVER (PARTITION BY deptno
ORDER BY empno
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) "win1",
--rows : 부분그룹인 윈도우의 크기를 물리적인 단위로 행집합을 지정
--unbounded preceding : 윈도우의 첫행
--unbounded following : 윈도우의 마지막행
SUM(sal) OVER (PARTITION BY deptno ORDER BY empno
ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW) "win2",
--윈도우의 시작행에서 현재 위치(current row)까지의 합계를 구해서 win2 에
SUM(sal) OVER (PARTITION BY deptno ORDER BY empno
ROWS BETWEEN CURRENT ROW AND
UNBOUNDED FOLLOWING) "win3"
--현재 위치에서 윈도우의 마지막행까지의 합계를 구해서 win3 에
FROM emp
WHERE deptno = '30';
-------------------- 행끼리 SUM
SELECT empno, sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY empno
ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) "win1",
--현재 행을 중심으로 이전행과 다음행의 급여합계
SUM(sal) OVER (PARTITION BY deptno ORDER BY empno
ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW) "win2",
--이전행과 현재행의 급여 합계
SUM(sal) OVER (PARTITION BY deptno ORDER BY empno
ROWS BETWEEN CURRENT ROW AND
1 FOLLOWING) "win3"
--현재 행과 다음행의 합계
FROM emp
WHERE deptno = '30';
---1차이
해당 구간에서 차지하는 비율 리턴
--직원들의 총급여를 20,000,000 원 증가 시킬 때 , 기존 월급비율을 적용해서
--각 직원이 받게될 급여의 증가액은
SELECT ename, sal, LPAD(TRUNC(RATIO_TO_REPORT(sal) OVER() * 100, 0), 5) ||' %' 비율,
TO_CHAR(TRUNC(RATIO_TO_REPORT(sal) OVER() * 20000000, 0), 'L00,999,999') "추가로 받게될 급여"
FROM emp;
지정하는 컬럼의 현재 행을 기준으로 이전 행 값 조회
SELECT ename, deptno, sal,
--1 : 위의 행값 , 0 : 이전행이 없으면 0 처리함
LAG(sal, 1, 0) OVER (ORDER BY sal) 이전값,
--이전행이 없으면 현재 행의 값을 출력
LAG(sal, 1, sal) OVER (ORDER BY sal) "조회 2",
--부서 그룹안에서의 이전 행값 출력
LAG(sal, 1, sal) OVER (PARTITION BY deptno ORDER BY sal) "조회 3"
FROM emp;
다음 행의 값 조회
SELECT ename, deptno, sal,
--1 : 다음 행값 , 0 : 다음행이 없으면 0 처리함
LEAD(sal, 1, 0) OVER (ORDER BY sal) 다음값,
--다음행이 없으면 현재 행의 값을 출력
LEAD(sal, 1, sal) OVER (ORDER BY sal) "조회 2",
--부서 그룹안에서의 다음 행값 출력
LEAD(sal, 1, sal) OVER (PARTITION BY deptno ORDER BY sal) "조회 3"
FROM emp;
partition별 window에서 가장 먼저 나온 값을 구함
SELECT deptno, ename, sal
, FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY sal DESC
ROWS UNBOUNDED PRECEDING) as DEPT_RICH
FROM emp;
partition별 window에서 가장 나중 값
SELECT deptno, ename, sal
, LAST_VALUE(ename) OVER ( PARTITION BY deptno ORDER BY sal DESC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR
FROM emp;