SQLD - SQL 활용 (윈도우 함수)

WINDOW FUNCTION

• 서로 다른 행의 비교나 연산을 위해 만든 함수
• GROUP BY를 쓰지 않고, 그룹 연산 가능
• LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK

 

PARTITION BY 절

• 출력할 총 데이터 수 변화 없이 그룹연산 수행할 GROUP BY 컬럼

 

ORDER BY 절 

• RANK 의 경우 필수 (정렬 컬럼 및 정렬 순서에 따라 순위 변화)
• SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력 시 사용

ROWS(RANGE BETWEEN A AND B)

• 연산 범위 설정
• ORDER BY 절 필수

※ PARTITION BY, ORDER BY, ROWS 절 전달 순서 중요(ORDER BY를 PARTITION BY 전에 사용 불가)

 

 

그룹 함수의 형태

• SUM, COUNT, AVG, MIN, MAX 등
• OVER 절을 사용하여 윈도우 함수로 사용 가능
• 반드시 연산할 대상을 그룹 함수의 입력 값으로 전달

1. SUM OVER()
   • 전체 총 합, 그룹별 총 합 출력 가능
2. AVG OVER()
   • SUM과 동일하게 사용
3. MIN / MAX OVER() 
   • SUM과 동일하게 사용
4. COUNT 
   • SUM과 동일하게 사용

 

※ 윈도우 함수의 연산 범위 : 집계 연산 시 행의 범위 설정 가능

 

1. ROWS, RANGE 차이
   1. ROWS : 값이 같더라도 각 행씩 연산
   2. RANGE : 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산 (DEFAULT)
2. BETWEEN A AND B
   • A) 시작점 정의
     • CURRENT ROW : 현재 행부터
     • UNBOUNDED PRECEDING : 처음부터 (DEFAULT)
     • N PRECEDING : N 이전부터
   • B) 마지막 시점 정의
     • CURRNET ROW : 현재 행까지 (DEFAULT)
     • UNBOUNDED FOLLOWING : 마지막까지
     • N FOLLOWING : N 이후까지

 

순위 관련 함수

1. RANK (순위)
   1. RANK WITHIN GROUP
      • 특정 값에 대한 순위 확인 (RANK WITHIN)
      • 윈도우 함수가 아닌 일반 함수
   2. RANK() OVER()
      • 전체 중 / 특정 그룹 중 값의 순위 확인
      • ORDER BY 절 필수
      • 순위를 구할 대상을 ORDER BY 절에 명시 (여러 개 나열 가능)
      • 그룹 내 순위 구할 시 PARTITION BY 절 사용
   3. DENCE_RANK()
      • 누적 순위
      • 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식
        ex) 1등이 5명이더라도 그 다음 순위가 2등
   4. ROW_NUMBER
      • 연속된 행 번호
      • 동일한 순위를 인정하지 않고, 단순히 순서대로 나열한 대로의 순서 값 리턴

 

LAG, LEAD

• 행 순서대로 각각 이전 값(LAG), 이후 값(LEAD) 가져오기
• ORDER BY 절 필수

 

FIRST_VALUE, LAST_VALUE

• 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
• 순서와 범위 정의에 따라 최솟값과 최댓값 리턴 가능
• PARTITION BY, ORDER BY 절 생략 가능

NTILE

• 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
• 그룹 번호가 리턴됨
• ORDER BY 필수
• PARTITION BY를 사용하여 특정 그룹을 또 원하는 수만큼 그룹 분리 가능
• 총 행의 수가 명확히 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리됨
  ex) 14명 3개 그룹 분리 시 → 5, 5, 4로 나뉨

 

비율 관련 함수

1. RATIO_TO_REPORT
   1. 각 값의 비율 리턴(전체 비율 또는 특정 그룹 내 비율 가능)
   2. ORDER BY 사용 불가
2. CUME_DIST 
   1. 각 행의 수에 대한 누적 비율
   2. 특정 값이 전체 데이터 집합에서 차지하는 위치를 백분위수로 계산하여 출력
   3. ORDER BY를 사용하여 누적 비율을 구하는 순서 정할 수 있음
   4. ORDER BY 필수
   5. 값이 3개이면 1/3 = 0.33부터 시작
3. PERCENT_RANK
   1. PERCENTILE(분위수) 출력
   2. 전체 COUNT 중 상대적 위치 출력(0 ~ 1 범위 내)
   3. ORDER BY 필요