오라클SQL 윈도우함수

윈도우 함수

윈도우 함수는 각 윈도우 별 집합 연산을 수행한 결과를 반환한다. BUSINESS 분야에서 자주 행하는 여러 가지 형태 분석을 사용한다.

join 이나 프로그램 Overhead를 줄인다.

윈도우 함수 구문

SELECT ANALYTIC_FUNCTION(arguments)
	   OVER([Partition by 컬럼List]
       		[ORDER BY 컬럼List]
            [Windowing 절 (Rows | Range Between)]
           )
FROM 테이블 명; 

Analytic Function

분석 함수명 (입력인자)

OVER	분석함수임을 나타내는 키워드, OVER 함수가 있으면 WINDOW 함수!!

Partition By

계산 대상 그룹 결정

Order By

대상 그룹에 대한 정렬 수행

Windowing 절

분석함수의 계산 대상 범위 지정

ORDER BY 절에 종속적이다. 기본 생략 구문 정렬된 결과의 처음부터 현재형까지

윈도우 함수 처리 단계 “JOINS, WHERE 조건절, GROUP BY, HAVING -> 윈도우 함수 처리 -> ORDER BY”

대상집합 Analytic Function이 적용되어야 할 각 GROUP으로 나눔

윈도우 함수에 지정한 ORDER BY 절에 기준 자료를 정렬한다.

Pointer와 OFF-SET 개념을 적용하여 각 ROW간에 필요한 계산을 수행한다.

윈도우 함수 종류

RANK

• 각 순위를 매겨주는 함수
• PARTITION 내에서 ORDER BY 절에 명시된 댇로 정렬한 후에 순위를 매긴다.

여기서 PARTITION은 특정 칼럼의 부분집합이다.

--사원번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위 조회하기
SELECT empno, ename, deptno, sal,
	   RANK() OVER (ORDER BY sal DESC) "RANK"
FROM emp;

--사원번호, 이름, 부서번호, 급여, 부서 내에서 급여가 많은 사원부터 순위 조회
SELECT empno, ename, deptno, sal,
	   RANK() OVER
	   (PARTITION BY deptno ORDER BY sal DESC) "RANK"
FROM emp;

DENSE_RANK

RANK와 비슷한 함수이지만, RANK는 동일한 RANK개수 만큼 다음 RANK가 나오지만, DENSE_RANK는 동일한 등수의 개수와 상관없이 다음 RANK값이 나온다.

ex)

RANK DENSE_RANK

100 -> 1 100 -> 1

100 -> 1 100 -> 1

95 -> 3 95 -> 2

--사원번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위 조회
SELECT empno, ename, deptno, sal,
	   DENSE_RANK() OVER (ORDER BY sal DESC) "DENSE_RANK"
FROM emp;

--사원번호, 이름, 부서번호, 급여, 부서 내에서 급여가 많은 사원부터 순위 조회
SELECT empno, ename, deptno, sal,
	   DENSE_RANK() OVER
	   (PARTITION BY deptno ORDER BY sal DESC) "DENSE_RANK"
FROM emp;

ROW_NUMBER

• ROW_NUMBER() 는 각 PARTITION 내에서 ORDER BY 절에 의해 정렬된 순서로 유일한 값을 들려주는 함수이다.
• ROWNUM함수와 혼동하지 않아야 한다.

-- 사원번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위 조회
SELECT empno, ename, deptno, sal,
	ROW_NUMBER() OVER (ORDER BY sal DESC) "DRANK"
FROM emp;

-- 사원번호, 이름, 연봉, 입사일, 순번 조회
-- (급여가 많은 순으로, 같은 급여를 받는 경우 입사일이 빠른 사람부터 앞 번호 부여)
SELECT empno, ename, sal, hiredate,
	ROW_NUMBER() OVER
	(ORDER BY sal DESC, hiredate) 순번
FROM emp;

NTILE

PARTITION을 BUCKET이라 불리는 그룹별로 나누고 PARTITION내에 각 ROW 등을 BUCKET에 배치하는 함수이다.

각 BUCKET에는 동일한 수의 ROW가 배치된다.

예를 들어 PARTITION내에 100개의 ROW를 가지고 있고 4개의 BUCKET으로 나누는 NTILE(4)를 사용하면 1개의 BUCKET당 25개의 ROW가 배정된다. 만약 103개의 ROW에 대하여 NTILE(5)를 적용하면 첫 번째 BUCKET부터 세 번째까지는 21개의 ROW가 배정되고 나머지는 20개의 ROW가 배친된다.

-- 급여가 적은 사원부터 4개로 분류해서 조회
SELECT ename, sal, NTILE(4) OVER (ORDER BY sal)
FROM emp;

Window Aggregate Family

윈도우 함수로 하여 각각의 로우들에 대한 집계 값을 반환한다.

-- 사원이름, 부서번호, 급여, 전체 급여합계, 부서별 급여합계 조회
SELECT ename, deptno, sal,
	SUM(sal) OVER() "total_sum",
	SUM(sal) OVER(PARTITION BY deptno) "dept_num"
FROM emp;

-- 사원이름, 업무, 급여, 업무별 급여평균, 해당 업무의 최대급여 조회
SELECT ename, job, sal,
	AVG(sal) OVER (PARTITION BY job) "job_avg",
	MAX(sal) OVER (PARTITION BY job) "job_max"
FROM emp;

--ROWS 옵션을 사용해 윈도우 함수 계산 범위를 지정한다.
--사원이름, 부서번호, 급여합계를 앞줄 1개부터 뒷줄 1개까지 3줄씩 더한 결과, 이전 ROW의 누적합계 조회
-- UNBOUNDED 누적!!
SELECT ename, deptno, sal,
	SUM(sal) OVER (ORDER BY sal ROWS
                  BETWEEN 1 preceding AND
                  1 following) "sum1",
    SUM(sal) OVER (ORDER BY sal
                  ROWS unbounded preceding) "sum2"
FROM emp;

LAG

파티션 내에 offset에 지정된 값(default = 1) 만큼 상대적으로 상위 위치한 로우(ROW)를 참조하기 위해 사용된다. (오름 차순의 경우 기준 로우의 정렬 컬럼 값보다 작은 값을 갖는 로우, 내림차순의 경우 기준 로우의 정렬 컬럼 값보다 큰 값을 갖는 로우)

--사원이름, 부서번호, 연봉, 본인 이전의 연봉 값 조회
SELECT ename, deptno, sal,
	LAG(sal, 1, 0) oVER (ORDER BY sal) AS next_sal,
	LAG(sal, 1, sal) OVER (ORDER BY sal) AS sal2
FROM emp;

LEAD

Lead()는 Lag()오 유사한 함수다.

offset에 지정된 값(default = 1)만큼 상대적으로 하위에 위치한 로우(row)를 참조하기 위해 사용한다.

--사원이름, 부서번호, 연봉, 본인 다음의 연봉 값 조회
SELECT ename, deptno, sal,
	LEAD(sal, 1, 0) OVER (ORDER BY sal) "next_sal",
	LEAD(sal, 1, sal) OVER (ORDER BY sal) "sal2"
FROM emp;