[SQLD] 2-15. 윈도우함수

윈도우 함수 (Window Function)

- 중첩해서 사용 X / 서브쿼리에서는 사용 O

 

종류

- 그룹 내 순위 함수 : RANK() / DENSE_RANK() / ROW_NUMBER()

- 그룹 내 집계 함수 : SUM / MAX / MIN / AVG / COUNT

- 그룹 내 행 순서 함수 : FIRST_VALUE(컬럼) / LAST_VALUE(컬럼) / LAG(컬럼, [n1, n2]) / LEAD(컬럼, [n1, n2])

- 그룹 내 비율 함수 : CUME_DIST() / PERCENT_RANK() / NTILE(n) / RATIO_TO_REPORT(컬럼)

- 선형 분석 포함한 통계 분석 함수

 

구문

SELECT 컬럼명1, 컬럼명2,... 윈도우함수(인수) OVER ([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명;

- PARTITION BY

• 전체 집합을 기준에 의해 소그룹으로 나누는 절

- ORDER BY

• 항목에 대한 순위

- WINDOWING

• 함수의 대상이 되는 행 기준의 범위 지정
• ROW = 물리적인 결과 행의 수 / RANGE = 논리적인 값에 의한 범위
• SQL Server에서 지원 X

 

 

※ WINDOWING 절 타입

- UNBOUNDED PRECEDING

• 현재 row기준으로 더 위쪽에 있는 모든 row 참조

- UNBOUNDED FOLLOWING

• 현재 row기준으로 더 아래쪽에 있는 모든 row 참조

- CURRENT ROW

• 현재 row를 시작점/끝점으로 간주

- VALUE_EXPR PRECEDING

• 현재 row기준으로 value_expr만큼 위쪽에 있는 row까지 참조

- VALUE_EXPR FOLLOWING

• 현재 row기준으로 value_expr만큼 아래쪽에 있는 row까지 참조

 

 

그룹 내 순위 함수 (RANK() / DENSE_RANK() / ROW_NUMBER())

≫ RANK()

- ORDER BY를 포함한 문에서 특정 컬럼에 대한 순위 구하는 함수

- 동일한 값 = 동일한 순위

(1) / (2)

(1) : 하나의 SQL문에서 ORDER BY SAL DESC 조건, PARTITION BY JOB 조건 사이에 충돌 발생

      -> JOB별로 정렬이 되지 않고, ORDER BY SAL DESC조건으로 정렬

(2) : JOB별로 SAL이 높은 순서대로 정렬

 

≫ DENSE_RANK()

- RANK함수와 흡사

- 동일한 순위를 하나의 건수로 취급

- RANK()함수는 동일한 순위를 다르게 취급해서 2등이 2명이고 다음 등수가 4등이 된다

- DENSE_RANK()함수는 동일한 순위를 같게 취급해서 2등이 2명이고 다음 등수는 3등이 된다

 

≫ ROW_NUMBER()

- 동일한 값이라도 고유한 순위를 부여 

- Oracle : rowid가 적은 행이 먼저 나온다

 

그룹 내 행 순서 함수 (FIRST_VALUE(컬럼) / LAST_VALUE(컬럼) / LAG / LEAD)

≫ FIRST_VALUE(컬럼)

- 파티션별 윈도우에서 가장 먼저 나온 값

- Oracle만 지원 O

- MIN함수와 같은 결과

 

≫ LAST_VALUE(컬럼)

- 파티션별 윈도우에서 가장 나중에 나온 값

- Oracle만 지원 O

- MAX함수와 같은 결과

 

≫ LAG(컬럼, n1, n2) / LEAD(컬럼, n1, n2)

- 파티션별 윈도우에서 이전/다음 몇번째 행의 값 가져오기

- SQL Server에서 지원 X

- n1 : 몇 번째 앞/뒤의 행을 가져올지 결정 (default = 1)

- n2 : NULL값이 들어올 경우 n2로 바꾸기

- ORDER BY 절을 필수로 작성

 

그룹 내 비율 함수 (CUME_DIST / PERCENT_RANK / NTILE / RATIO_TO_REPORT)

≫ RATIO_TO_REPORT(컬럼)

- 행별 컬럼 값의 백분율을 소수점으로 리턴

- SQL Server에서 지원 X

- OVER안에 ORDER BY 사용 X

 

≫ PERCENT_RANK()

- 행의 순서별 백분율 리턴

- SQL Server에서 지원 X

 

≫ CUME_DIST()

- 현재 행보다 작거나 같은 항목에 대한 누적백분율 

- SQL Server에서 지원 X

 

≫ NTILE(n)

- 전체 건수를 인수값으로 n등분한 결과 리턴