[오라클/ORACLE] WINDOW FUNCTION 윈도우 함수와 WINDOWING 절 / 순위함수,집계함수, 행 순서 관련 함수

WINDOW FUNCTION의 개요와 종류

---

WINDOW FUNCTION : 분석함수 중에서 윈도우절을 사용하는 함수를 뜻함.

• 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER  ( 순위 함수란 ? )
• 집계함수 : SUM, MAX, MIN, AVG, COUNT           ( 집계 함수란 ? )
• 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD 

* 관련 포스팅 내용은 위 링크를 클릭하면 이동됩니다.

WINDOW FUNCTION의 구조

---

SELECT WINDOW_FUNCTION ( 인수 )
       OVER ( [ PARTITION BY 컬럼명 ] [ ORDER BY 절 ] [ WINDOWING 절 ] )
FROM 테이블명 ;

• WINDOW_FUNCTION : 함수명 ( SUN, MAX, MIN, RANK .... ) 
• OVER : 분석함수임을 표시 필수이다.
• PARTITION : 대상을 그룹화할 기준
• ORDER BY : 그룹에 대한 정렬 기준
• WINDOWING 절 : 분석함수의 대상이 되는 범위를 지정, ORDER BY 절에 종속적, 
                          [ 기본 생략/DEFAULT ] 로 range between unbounded preceding and current row 실행
                            -> 정렬된 결과의 처음부터 현재 행 까지

WINDOWING 절의 구조

---

-- BETWEEN 사용시 
SELECT SUM(컬럼) 
OVER ( PARTITION BY 컬럼 ORDER BY 컬럼 [ ASC || DESC ]
       { ROWS || RANGE } BETWEEN UNBOUNDED PROCEDING || CURRENT ROW || VALUE_EXPR PRECEDING/FOLLOWING
                         AND UNBOUNDED FOLLOWING || CURRENT ROW || VALUE_EXPR PRECEDING/FOLLOWING
 
-- BETWEEN 미 사용시
SELECT SUM(컬럼) 
OVER ( PARTITION BY 컬럼 ORDER BY 컬럼 [ ASC || DESC ]
       { ROWS || RANGE } UNBOUNDED PROCEDING || CURRENT ROW || VALUE_EXPR PROECEDING 
                        

WINDOWING절에서는 범위를 지정할 구분으로 [ ROWS / RANGE ] 가 사용이 가능한데

ROWS 는 물리적인 결과 행을 기준으로

RANGE 는 논리적인 범위를 지정한다. 또한 ORDER BY 절에서 사용한 컬럼 값에 대해 상수로 범위 지정이 가능

사용 예제

---

실습에 사용될 데이터 

 

---

• 부서 별 급여 합계 조회

-- 급여합계를 DEPT_NO ( 부서 별 ) 로 분류를 나눠서 구함
SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
       SUM(SAL) OVER ( PARTITION BY DEPT_NO ORDER BY EMP_NO) AS TOTAL_SUM
FROM VIEW_EMPINFO;
 
-- 위 커리와 같은 결과 위 쿼리가 아래 BETWEEN ~~ AND ~~ 구문이 자동으로 추가 됨
SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
       SUM(SAL) OVER 
       ( PARTITION BY DEPT_NO ORDER BY 
         EMP_NO RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS TOTAL_SUM
FROM VIEW_EMPINFO;

 

부서별 합계 구하기

---

• 부서 별 급여 누계 조회 ( UNBOUNDED PRECENDING / FOLLOWING ) 

-- ROWS를 활용한 부서 별 급여 누계
SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
       SUM(SAL) OVER 
       ( PARTITION BY DEPT_NO 
         ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING 
         AND CURRENT ROW ) SUM
FROM VIEW_EMPINFO;
 
-- RANGE를 활용한 부서 별 급여 누계
SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
       SUM(SAL) OVER 
       ( PARTITION BY DEPT_NO 
         ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING 
         AND CURRENT ROW ) SUM
FROM VIEW_EMPINFO;

왼쪽 : ROWS의 실행결과 / 오른쪽 : RANGE의 실행 결과 SAL이 같을 경우 누계의 차이점이 보인다

 

 

---

• 부서 별 급여 누계 조회 ( VALUE_EXPR PRECEDING / FOLLOWING )

각각 전단계 1개행과 자신 행, 전 1개행과 다음 1개행 총( 최대 3개 ), 자신 행과 다음 행을 기준으로 조회

 

SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
SUM(SAL) OVER ( PARTITION BY DEPT_NO ORDER BY SAL 
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) SUM
FROM VIEW_EMPINFO;
 
SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
SUM(SAL) OVER ( PARTITION BY DEPT_NO ORDER BY SAL 
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) SUM
FROM VIEW_EMPINFO;
 
SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
SUM(SAL) OVER ( PARTITION BY DEPT_NO ORDER BY SAL 
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) SUM
FROM VIEW_EMPINFO;

 

1번째 쿼리 실행 결과                   /                2번째 쿼리 실행 결과                /                3번째 쿼리 실행 결과

 

---

• 부서 별 급여 합계 조회 ( RANGE를 통한 상수로 범위 지정 )

SELECT EMP_NO, EMP_NAME, DEPT_NO, SAL,
       SUM(SAL) OVER 
       ( PARTITION BY DEPT_NO 
         ORDER BY SAL RANGE BETWEEN 300 PRECEDING AND 100 FOLLOWING ) SUM
FROM VIEW_EMPINFO;

 

쿼리 실행 결과 300 <= 기준행  <= 100 의 범위를 기준