WINDOW FUNCTION

JIYOUNG KIM·2022년 5월 9일
0

SQL

목록 보기
4/6
post-thumbnail

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

profile
데이터분석가

0개의 댓글