window function

window function

SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER (
    <PARTITION BY절> -- GROUP BY 같은 역할. 전체 집합을 기준에 의해 소그룹으로 나눔
    <ORDER BY절> -- ORDER BY 역할. 어떤 항목에 대해서 정렬
    <WINDOWING절> -- WHERE 역할. 행 기준의 범위를 정함. ROWS: 물리적 결과의 행 수, RANGE: 논리적인 값에 의한 범위
)
FROM 테이블명;

 

window function의 구조

SELECT empno, ename, sal, SUM(sal)
OVER (
    ORDER BY sal
    ROWS BETWEEN UNBOUNDED PRECDING AND CURRENT ROW
) totsal
FROM emp; -- 행 별로 누적 합계를 구함

• PARTITION BY절
  - GROUP BY 같은 역할
  - 전체 집합을 기준에 의해 소그룹으로 나눔
• ORDER BY절
  - ORDER BY 역할
  - 어떤 항목에 대해서 정렬
• WINDOWING절
  - WHERE 역할
  - 행 기준의 범위를 정함
  - ROWS: 물리적 결과의 행 수. 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
  - RANGE: 논리적인 값에 의한 범위. 논리적인 주소에 의해 행 집합을 지정
  - BETWEEN ~ AND: 윈도우의 시작과 끝의 위치를 지정
  - UNBOUNDED PRECDING: 윈도우의 시작 위치가 첫 번째 행임
  - UNBOUNDED FOLLOWING: 윈도우의 마지막 위치가 마지막 행임
  - CURRENT ROW: 윈도우 시작 위치가 현재 행임

 

순위 함수

• RANK
  - 특정 항목 및 파티션에 대해서 순위를 계산
  - 동일한 순위는 동일한 값이 부여됨 ( 1 2 2 4 )
• DENSE_RANK
  - 동일한 순위를 하나의 건수로 계산 ( 1 2 2 3 )
• ROW_NUMBER
  - 동일한 순위에 대해서 고유의 순위를 부여 ( 1 2 3 4 )

 

집계 함수

• SUM
  - 파티션 별로 합계 계산
• AVG
  - 파티션 별로 평균 계산
• COUNT
  - 파티션 별로 행 수 계산
• MAX, MIN
  - 파티션 별로 최댓값, 최솟값 계산

 

행 순서 관련 함수

• FIRST_VALUE
  - 파티션에서 가장 처음에 나오는 값
  - MIN 함수를 사용해서 같은 결과를 구할 수 있음
•  LAST_VALUE
  - 파티션에서 가장 나중에 나오는 값
  -  MAX 함수를 사용해서 같은 결과를 구할 수 있음
• LAG
  - 이전 행을 가지고 옴
• LEAD
  - 윈도우에서 특정 위치의 행을 가지고 옴
  - 기본값은 1

 

비율 관련 함수

• CUME_DIST
  - 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회
  - 누적 분포상에 위치를 0~1 사이의 값을 가짐
• PERCENT_RANK
  - 파티션에서 제일 먼저 나온 것을 0으로, 제일 늦게 나온 것을 1로 함
  - 값이 아닌 행의 순서별 백분율을 조회
• NTILE
  - 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회
• RATIO_TO_REPORT
  - 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회