12. Window function

📌 WINDOW FUNCTION 개요 및 설명

윈도우 함수 (WINDOW FUNCTION)

• 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수가 윈도우 함수다. ⇒ 분석 함수나 순위 함수랑 동의어
  

WINDOW FUNCTION 문법

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

• OVER 문구가 키워드로 필수 포함
• WINDOW_FUNCTION : 윈도우 함수
• ARGUMENTS(= 인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다.
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.

📌 WINDOW FUNCTION 종류

WINDOW FUNCTION 는 크게 5가지 그룹으로 분류할 수 있다. (벤더별로 지원하는 함수 차이가 있음)

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
5. 선형 분석을 포함한 통계 분석 함수

📌 Rank 관련함수

1. 특정 항목과 파티션에 대해 순위를 계산할 수 있는 함수
   1. 📌 RANK — 순위를 계산합니다. 동일한 순위는 동일한 값이 부여됩니다.
   2. 📌 DENSE_RANK — 동일한 순위를 하나의 건수로 계산합니다
      • DENSE = 밀집한
        • RANK()는 같은 순위가 있으면(2위) 그 다음 순위(3위)가 없어집니다.
        • DENSE_RANK()는 같은 순위가 있어도 건수로 계산하기 때문에 3위가 있습니다.
          • 빽빽하게, 밀접하게 다 채운다고 생각!
   3. 📌 ROW_NUMBER — 동일한 순위에 대해 고유의 순위를 부여합니다.
      • ROW_NUMBER는 동일한 순위가 있더라도 고유의 순위를 부여합니다.
      • RANK, DENSE_RANK 가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, ROW_NUMBER 는 동일한 값이라도 고유한 순위를 부여
        

📌 AGGREGATE 집계 관련함수

• 집계 함수(AGGREGATE Function)
  • SUM, MAX, MIN, AVG, COUNT 등등
  • Group by 때 써주었던 기본 집계함수

📌 그룹 내 행 순서 관련 함수

• 행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력하게 할 수 있습니다.

• 또는 특정 위치의 행을 갖고와 출력 할 수 있습니다.

• FIRST_VALUE 함수

  • 파티션에서 가장 처음에 나오는 값
  • MIN 함수를 사용한것과 같은 결과

• LAST_VALUE 함수

  • 파티션에서 가장 마지막에 나오는 값
  • MIN 함수를 사용한것과 같은 결과

• LAG 함수

  • 이전 행(값)을 가져옵니다.
  • Lag(Sum(amount))
    OVER (ORDER BY Extract(month FROM Date(p.payment_date))) as pre_amt,

• LEAD 함수

  • 지정된 행(값)을 가져옵니다. 기본값은 1입니다.
  • LEAD(sal, 1, 0) 1을 주었기 때문에 바로 다음 행값 가져옴
    • 2를 주면 Lead 열의 첫번째 값에 sal의 세번째 값을 가져옴
    • 세번째 파라미터인 0은 null값을 처리하는 파라미터

📌 그룹 내 행 비율 관련 함수

• 비율 관련 함수는 누적 백분율, 순서별 백분율, 파티션을 N등분으로 분할한 결과 등을 조회

  1. CUME_DIST

     • CUME_DIST -- 파티션 전체에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회합니다.
     • 누적 분포상 위치는 0~1 사이에 위치
     • 즉 그냥 누적 백분율이 얼마인지 조회하는것

  2. PERCENT_RANK
     - 파티션에서 제일 먼저 나온 것을 0, 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회
     - 파티션을 나누어 Partition by 가장 먼저 나온 것을 0 마지막을 1로하여 등수의 퍼센트를 구합니다.

     

  3. NTILE

     • -- 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과
     • Ntile(4) over (partition by ~~) → 4등분한 것

     SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC ) AS QUAR_TILE 
     FROM EMP ;

     

  4. RATIO_TO_REPORT

     • -- 파티션 내 전체 SUM(컬럼)에 대한 행 별 컬럼 값의 백분율을 소수점까지 조회합니다.
       ⇒ (점유율 조회)
     • 파티션을 나누어 각 sal이 각 deptno에서 어느정도 점유율을 갖고 있는지 조회

     출처

📌 선형 분석을 포함한 통계 분석 함수

• 선형분석을 포함한 통계분석 함수
• CORR, COVARPOP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY