[SQL]분석함수(analytic function)_QUERY PARTITION절, ORDER BY절, WINDOWING절

1. 기본 문법

 

• 분석함수는 OVER 키워드를 사용한다. OVER 키워드에 ANALYTIC 절을 기술할 수 있다.

analytic_function ( [ arguments ] ) OVER ( analytic_clause )

 

• ANALYTIC 절은 QUERY PARTITION 절, ORDER BY 절, WINDOWING 절로 구성된다.

[ query_partition_clause ] [order_by_clause [ windowing_clause ] ]

 

1.1 QUERY PARTITION 절

 

• QUERY PARTITION 절은 파티션을 지정할 수 있으며, GROUP BY 절과 유사하게 동작한다.

SELECT empno, job, sal
      ,① SUM(sal) OVER(PARTITION BY job) AS c1
      ,② SUM(sal) OVER() AS c2
 FROM emp
WHERE deptno = 30
ORDER BY 2, 1;

 

① SUM(sal) OVER (PARTITION BY job) AS c1 : CLERK, MANAGER, SALESMAN 3개의 파티션으로 집계되도록 함.

② SUM(sal) OVER () AS c2 : 전체 행이 하나의 파티션으로 집계되도록 함.

 

1.2 ORDER BY 절

 

• ORDER BY 절로 파티션 내의 정렬 순서를 지정할 수 있다.

SELECT empno, sal
      ,① SUM(sal) OEVR(ORDER BY sal, empno) AS c1
 FROM emp
WHERE deptno = 30
ORDER BY 2, 1;

 

 

① SUM(sal) OVER (ORDER BY sal, empno) AS c1 : sal, empno 오름차순으로 정렬하며 누적 합계 값을 반환함.

 

1.3 WINDOWING 절

 

• WINDOWING 절로 파티션의 윈도우를 지정할 수 있다. 윈도우는 파티션 내의 동적 그룹으로 생각할 수 있다.

 

* 윈도우는 ROWS 방식과 RANGE 방식으로 지정할 수 있으며, 꼭 ORDER BY절과 같이 와야한다.

 

윈도우 기준동일한 정렬 값value_expr 계산

ROWS	물리적 행	다른 값을 반환	정렬 행의 위치
RANGE	논리적 범위	같은 값을 반환	정렬 값

 

* 윈도우 범위를 지정하는 키워드

 

범위설명

BETWEEN ... AND ...	윈도우의 시작과 끝
UNBOUNDED PRECEDING	앞쪽 끝
UNBOUNDED FOLLOWING	뒤쪽 끝
CURRENT ROW	현재 행
value_expr PRECEDING ROWS	현재 행에서 앞쪽으로 value_expr만큼 이동
value_expr PRECEDING RANGE	현재 값에서 value_expr을 가감
value_expr FOLLOWING ROWS	현재 행에서 뒤쪽으로 value_expr만큼 이동
value_expr FOLLOWING RANGE	현재 값에서 value_expr을 가감

 

• ROWS, RANGE 방식의 UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING

ex 1 ) 사원 번호, 사원 급여, 사원 급여순으로 행 별로 앞쪽 끝부터 누적합계를 구해라.

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c1
 FROM emp;

 

① SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c1 : sal을 오름차순으로 정렬한 것에 대해 행 별로 UNBOUNDED PRECEDING(앞쪽 끝)에서부터 CURRENT ROW(현재 행)까지 누적합계를 구한다.

 

* CURRENT ROW를 쓰지 않아도 위 쿼리와 동일한 결과값을 출력할 수 있음.

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal ROWS UNBOUNDED PRECEDING) AS c1
 FROM emp;

 

① SUM(sal) OVER(ORDER BY sal ROWS UNBOUNDED PRECEDING) AS c1 : sal을 오름차순으로 정렬한 것에 대해 행 별로 UNBOUNDED PRECEDING(앞쪽 끝)에서 부터 누적합계를 구한다.

 

ex 2 ) 사원번호, 사원급여, 사원급여 순으로 행 별로 뒤쪽 끝부터 누적합계를 구해라.

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c1
 FROM emp;

 

 

① SUM(sal) OVER(ORDER BY sal ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c1 : sal을 오름차순으로 정렬한 것에 대해 행 별로 UNBOUNDED FOLLOWING(뒤쪽 끝)에서 부터 누적합계를 구한다.

 

* 아래 쿼리와 같이 FOLLOWING은 PRECEDING과 다르게 홀로 쓰일 수 없다.

SELECT empno, sal
      , SUM(sal) OVER(ORDER BY sal ROWS UNBOUNDED FOLLOWING) AS c1
 FROM emp;

 

 

ex 3 ) 사원 번호, 사원 급여, 사원 급여순으로 범위 별로 앞쪽 끝부터 누적합계를 구해라.

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c1
 FROM emp;

 

 

① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS c1 : sal을 오름차순으로 정렬한 것에 대해 범위 별로 UNBOUNDED PRECEDING(앞쪽 끝)에서 부터 누적합계를 구한다.

 

ex 4 ) 사원 번호, 사원 급여, 사원 급여순으로 범위 별로 뒤쪽 끝부터 누적합계를 구해라.

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c1
 FROM emp;

 

 

① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS c1 : sal을 오름차순으로 정렬한 것에 대해 범위 별로 UNBOUNDED FOLLOWING(뒤쪽 끝)에서 부터 누적합계를 구한다.

 

• ROWS 방식의 value_expr PRECEDING, value_expr FOLLOWING

 

• value_expr PRECEDING = 앞쪽으로 value_expr 만큼 행이동

  value_expr FOLLOWING = 뒤쪽으로 value_expr 만큼 행이동

 

ex 1 ) ROWS 방식, 2 PRECEDING = 앞쪽으로 2 만큼 행이동

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS c1
 FROM emp;

 

 

① SUM(sal) OVER(ORDER BY sal ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) c1 : sal을 오름차순으로 정렬한 것에 대해 앞쪽 2개 행부터 현재 행까지 누적합계를 구한다.

 

ex 2 ) ROWS 방식, 3 FOLLOWING = 뒤쪽으로 3 만큼 행이동

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS c1
 FROM emp;

 

 

① SUM(sal) OVER(ORDER BY sal ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) c1 : sal을 오름차순으로 정렬한 것에 대해 현재 행부터 뒤쪽 3개까지 누적합계를 구한다.

 

• RANGE 방식 value_expr PRECEDING, value_expr FOLLOWING

  value_expr PRECEDING = 앞쪽으로 value_expr 값만큼 범위 확장

  value_expr FOLLOWING = 뒤쪽으로 value_expr 값만큼 범위 확장

 

ex 1 ) RANGE 방식, 150 PRECEDING = 앞쪽으로 150 값만큼 범위 확장

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN 150 PRECEDING AND CURRENT ROW) AS c1
 FROM emp;

 

 

① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN 150 PRECEDING AND CURRENT ROW) c1 : sal을 오름차순으로 정렬한 것에 대해 현재 행에서 앞쪽으로 150 값만큼 범위 확장하여 누적합계를 구한다.

(현재 행의 값이 950이라면 앞쪽으로 150 값만큼 확장한 800까지의 누적합계를 구한다. )

 

ex 2 ) RANGE 방식, 50 FOLLOWING = 뒤쪽으로 50 값만큼 범위 확장

SELECT empno, sal
      ,① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN CURRENT ROW AND 50 FOLLOWING) AS c1
 FROM emp;

 

① SUM(sal) OVER(ORDER BY sal RANGE BETWEEN CURRENT ROW AND 50 FOLLOWING) c1 : sal을 오름차순으로 정렬한 것에 대해 현재 행에서 뒤쪽으로 50 값만큼 범위 확장하여 누적합계를 구한다.

(현재 행의 값이 1250이라면 뒤쪽 50으로 값만큼 확장한 1300까지의 누적합계를 구한다.)