[SQLD] 윈도우 함수

윈도우 함수

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

윈도우 함수 문장

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 차이

• ROWS: 값이 같더라도 각 행 씩 연산
• RANGE: 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산(DEFAULT)

2) BETWEEN A AND B

A: 시작점 정의

• CURRENT ROW : 현재 행부터
• UNBOUNDED PRECEDING: 처음부터 (default)
• N PRECEDING: N 이전부터

B: 마지막 시점 정의

• CURRENT ROW: 현재 행까지 (default)
• UNBOUNDED FOLLOWING: 마지막까지
• N FOLLOWING: N 이후까지

예시) UNBOUNDED PRECEDING AND 1 FOLLOWING: 각 행마다 누적 합 계산 시 처음부터 다음 행까지 연산

 

순위 관련 함수

RANK 함수는 ORDER BY를 포함한 QUERY 문에서 특정 항목(컬럼)에 대한 순위를 구하는 함수이며, 동일한 값에 대해서는 동일한 순위를 부여한다.

1. RANK WITHIN GROUP
   1. 특정 값에 대한 순위 확인
   2. 윈도우 함수가 아닌 일반 함수RANK 함수 문장
2. RANK() OVER()
   1. 전체/특정 그룹 중 값의 순위 확인
   2. ORDER BY절 필수
   3. 순위를 구할 대상을 ORDER BY절에 명시 (여러 개 나열 가능)
   4. 그룹 내 순위를 구할 경우 PARTITION BY 절 사용RANK() OVER() 문장
3. DENSE_RANK
   1. 누적 순위
   2. 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식
      1. 1등이 5명이더라도 그 다음 순위가 2등으로 부여됨
      2. RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 다른 점이다.
4. ROW_NUMBER
   1. 연속된 행 번호
   2. 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리턴
      1. RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는 반면, ROW_NUMBER은 동일한 값이라도 고유한 순위를 부여한다.
5. LAG, LEAD
   
   1. 행 순서대로 각각 이전 값(LAG), 이후 값(LEAD) 가져오기
      1. LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. 
      2. 이후 몇 번째 행의 값을 가져오는 것은 LEAD 함수이며, SQL Server에서는 지원하지 않는 함수이다.
   2. ORDER BY 절 필수

   3. 

      LAG, LEAD 문장

6. FIRST_VALUE, LAST_VALUE
   1. 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
   2. 순서와 범위 정의에 따라 최솟값/최댓값 리턴 가능
7. NTILE
   1. 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
   2. N등분 한 뒤 부여된 그룹 번호가 리턴됨
   3. ORDER BY 필수
   4. PARTITION BY를 사용하여 특저 그룹을 추가로 원하는 수 만큼 그룹 분리 가능

   5. 

      NTILE 문장

비율 관련 함수

1. RATIO_TO_REPORT
   1. 각 값의 비율 리턴 (전체 비율 또는 특정 그룹 내 비율 가능)
   2. ORDER BY 사용 불가RATIO_TO_REPORT 문장
2. CUME_DIST
   1. 각 값의 누적 비율 리턴 (전체 비율 또는 특정 그룹 내 비율 가능
   2. ORDER BY 필수 (누적 비율을 구하는 순서를 정할 수 있음)CUME_DIST() 문장
3. PERCENT_RANK
   1. PERCENTILE (분위수) 출력
   2. 전체 COUNT 중 상대적 위치 출력 (0~1 범위 내)
   3. ORDER BY 필수PERCENT_RANK() 문장