4-6장. SQL 활용(윈도우 함수[WINDOW 함수])

 

SQLD 단원별 목록으로

---

1. 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)
  - 선형 분석을 포함한 통계 분석 관련 함수(CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP 등등)
  
  
• WINDOW FUNCTION SYNTAX
  - WINDOW_FUNCTION: 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.
  - ARGUMENTS (인수): 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
  - PARTITION BY 절: 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
  - ORDER BY 절: 어떤 항목에 대해 순위를 지정할지 ORDER BY 절을 기술.
  - WINDOWING 절: 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

 

 

---

2. 그룹 내 순위 함수

가) RANK 함수 : ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수

• 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다.
• 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

하나의 SQL 문장에 ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건이 충돌이 났 기 때문에 JOB 별로는 정렬이 되지 않고, ORDER BY SAL DESC 조건으로 정렬이 되었다.

---

나) DENSE_RANK 함수

• DENSE_RANK 함수는 RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것 이 틀린 점이다.

FORD와 SCOTT, WARD와 MARTIN은 동일한 SALARY이므로 RANK와 DENSE_RANK 칼럼에서 모두 같은 순위를 부여한다.

---

다) ROW_NUMBER 함수

• RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.

 

 

---

3. 일반 집계 함수

가) SUM 함수

• SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.

---

나) MAX 함수

• 파티션별 윈도우의 최댓값을 구할 수 있다.

---

다) MIN 함수

• MIN 함수를 이용해 파티션별 윈도우의 최솟값을 구할 수 있다.

---

라) AVG 함수

• AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있다.

---

마) COUNT 함수

• COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계 값을 구할 수 있다.

 

 

---

4. 그룹 내 행 순서 함수

가) FIRST_VALUE 함수

• FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
• SQL Server에서는 지원하지 않는 함수이다.
• MIN 함수를 활용하여 같은 결과를 얻을 수도 있다.

---

나) LAST_VALUE 함수

• LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
• SQL Server에서는 지원하지 않는 함수이다.
• MAX 함수를 활용하여 같은 결과를 얻을 수도 있다.

---

다) LAG 함수

• LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
• SQL Server에서는 지원하지 않는 함수이다.

---

라) LEAD 함수

• LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.
• 참고로 SQL Server에서는 지원하지 않는 함수이다.

 

 

---

6. 그룹 내 비율 함수

가) RATIO_TO_REPORT 함수

• RATIO_TO_REPORT 함수를 이용해 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.
• 결과 값은 >0 &<= 1 의 범위를 가진다. 그리고 개별 RATIO의 합을 구하면 1이 된다.
• SQL Server에서는 지원하지 않는 함수이다.

---

나) PERCENT_RANK 함수

• PERCENT_RANK 함수를 이용해 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다.
• 결과 값은 >= 0 &<= 1 의 범위를 가진다.
• 참고로 SQL Server에서는 지원하지 않는 함수이다.

---

다) CUME_DIST 함수

• CUME_DIST 함수를 이용해 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건 수에 대한 누적백분율을 구한다.
• 결과 값은 >0 &<= 1 의 범위를 가진다. 참고로 SQL Server에서는 지원하지 않는 함수이다.

---

라) NTILE 함수

• NTILE 함수를 이용해 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있다.