[Oracle] 윈도우 함수(Window Funtion)_그룹 내 집계 함수

윈도우 함수 종류

1.그룹 내 순위(RANK) 관련 함수

 - RANK, DENSE_RANK, ROW_NUMBER 함수

2.그룹 내 집계(AGGREGATE) 관련 함수

 - SUM, MAX, MIN, AVG, COUNT 함수 

3.그룹 내 행 순서 관련 함수

 - FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수 

4.그룹 내 비율 관련 함수

 - CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수

윈도우 함수 문법 

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ([PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절])

  FROM 테이블명; 

  

WINDOW_FUNCTION

 - 함수명

ARGUMENTS (인수) 

 - 함수에 따라 0 ~ N개의 인수가 지정될 수 있습니다.

PARTITION BY 절

 - 전체 집합을 기준에 의해 소그룹으로 나눌 수 있습니다.

ORDER BY 절

 - 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술합니다.

WINDOWING 절

 - 함수의 대상이 되는 행 기준의 범위를 지정할 수 있습니다.

 

SUM 함수

 SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있습니다.

SELECT MGR
     , ENAME
     , SAL
     , SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM                                                                                                                         
  FROM EMP;

  

SELECT MGR
     , ENAME
     , SAL
     , SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS MGR_SUM                                             
  FROM EMP;  

 

RANGE UNBOUNDED PRECEDING

 - 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정합니다.  

  

MAX 함수

 MAX 함수를 이용해 파티션별 윈도우의 최대값을 구할 수 있습니다.

SELECT MGR
     , ENAME
     , SAL
     , MAX(SAL) OVER (PARTITION BY MGR) MGR_MAX                                                                                                                         
  FROM EMP;

  

MIN 함수

 MIN 함수를 이용해 파티션별로 윈도우의 최소값을 구할 수 있습니다.

SELECT MGR
     , ENAME
     , SAL
     , MIN(SAL) OVER (PARTITION BY MGR) MGR_MAX                                                                                                                         
  FROM EMP;  

 

AVG 함수

 AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있습니다.

SELECT MGR
     , ENAME
     , HIREDATE
     , SAL
     , ROUND(AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS MGR_AVG
  FROM EMP;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

 - 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정합니다.

 

COUNT 함수

 COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있습니다.

SELECT ENAME
     , SAL
     , COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS SIM_CNT                                           
  FROM EMP; 

  

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING

 - 현재 행의 급여값을 기준으로 급여가 -50부터 +150의 범위 내에 포함된 모든 행이 대상이 됩니다.