WINDOW FUNCTION

WINDOW FUNCTION

• 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수
• 분석함수, 순위함수
• 다른 함수와 달리 중접해서 사용할 수 없지만 서브쿼리에서는 사용할 수 있음
• 종류

  • 그룹 내 순위 함수

    • RANK
    • DENSE_RANK
    • ROW_NUMBER

  • 그룹 내 집계 함수

    • SUM
    • MAX
    • MIN
    • AVG
    • COUNT

  • 그룹 내 행 순서 함수

    • FIRST_VALUE
    • LAST_VALUE
    • LAG
    • LEAD

  • 그룹 내 비율 함수

    • CUME_DIST
    • PERCENT_RANK
    • NTILE
    • RATIO_TO_REPORT

  • 통계분석 함수

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

# AGRUMENTS(인수) : 함수에 따라 0~N개 인수가 지정될 수 있음
# PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음
# ORDER BT 절 : 어떤 항목에 대해 순위를 지정할 지
# WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정

그룹 내 순위 함수

• RANK

  • 순위를 구하는 함수

  • 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에서 순위 구할 수도 있음

  • 동일 값에 대해서는 동일 순위 부여

    SELECT JOB, ENAME, SAL,
            RANK() OVER (ORDER BY SAL DESC) ALL_RANK, # 급여 높은 순
            RANK)() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK # JOB 별로 급여 높은 순
    FROM EMP;

  • PARTITION 으로 구분한 JOB_RANK는 같은 업무 범위 내에서만 순위부여

• DENSE_RANK

  • RANK 와 비슷하지만 동일한 순위를 하나의 건수로 취급
    • RANK : 1,2,3 순위 , DENSE_RANK : 1,1,3 순위

• ROW_NUMBER

  • 동일한 값이라도 고유한 순위 부여 → 유니크한 순위
  • 같은 값에 대해서 순서를 정하고 싶다면 ORDER BY 같이 기재

그룹 내 집계 함수

• SUM

  • 파티션 별 윈도우의 합을 구할 수 있음

    # 사원들의 급여와 같은 매니저를 두고 있는 사원들의 급여 합
    SELECT MGR, ENAME, SAL,
           SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
    FROM EMP;

• MAX

  • 파티션 별 윈도우의 최대값

• MIN

  • 파티션 별 윈도우의 최소값

• AVG

  • 파티션 별 통계값

    # 같은 매니저를 두고 있는 사원들의 평균 급여를 구하되 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원을 대상으로만 출력(앞 사번 + 나 + 뒷사번 / 3 인데, 앞 사번 없으면 나 + 뒷사번 / 2)
    SELECT MGR, ENAME, HIREDATE, SAL,
           ROUND (AGV(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE)
           ROW BETWEEN 1 PRECEDING AND 1 FOLLOWING) MGR_AVG
    FROM EMP;

• COUNT

그룹 내 행 순서 함수

• FIRST_VALUE

  • 파티션 별 윈도우에서 가장 먼저 나온 값
  • MIN 함수 사용해도 같은 결과
  • 동일한 값이 있을 때는 정렬지정 해야 함

• LAST_VALUE

  • 파티션 별 윈도우에서 가장 나중에 나온 값
  • MAX 함수 사용해도 같은 결과

• LAG

  • 파티션 별 윈도우에서 이전 몇 번째 행의 값

    # 직원들을 입사일자가 빠른 기준으로 정렬하고 본인보다 입사 일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력
    SELECT ENAME, HIREDATE, SAL,
           LAG(SAL) OVER (ORDER BY HIREDATE) PREV)SAL
    FROM EMP
    WHERE JOB = 'SALESMAN';

  • 3개의 인수까지 사용 가능
  • LAG(SAL, 2, 0)
    • 2 : 2번째 앞에 있는 행을 가져오는 것(디폴트 1)
    • 0 : 파티션 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 출력되는 데 이 경우 0 으로 바꿀 수 있음 → NVL, ISNULL 같은 기능

• LEAD

  • 파티션 별 윈도우에서 이후 몇 번째 행의 값
  • LAG 처럼 3개의 인수까지 사용가능

그룹 내 비율 함수

• CUME_DIST
  • 파티션 별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율
• PERCENT_RANK
  • 파티션 별 윈도우에서 가장 먼저 나오는 것을 0, 가장 늦게 나오는 것을 1 로 하여 행의 순서별 백분율
• NTILE
  • 파티션 별 전체 건수를 ARGUMENT 값으로 N등분한 결과
  • NTILE(4) → 4등분
• RATIO_TO_REPORT
  • 파티션 내 전체 컬럼 값에 대한 행 별 컬럼 값의 백분율을 소수점으로 나타낸 것
  • 0과 1사이의 값
  • 개별 ratio 의 값은 1

참고 : https://moonpiechoi.tistory.com/128