SQL 기본 - 윈도우 함수, WINDOW FUNCTION

윈도우 함수, WINDOW FUNCTION

 

 윈도우 함수는 부분적으로 행과 행간의 관계를 쉽게 정의하기 위한 함수. 분석함수, 순위함수라고도 한다.

 일정 범위내의 행들 사이에 순위를 나타내거나 집계, 순서, 비율, 통계를 낼 수 있다.

 

윈도우 함수 명령어의 기본 구조

 

Colored By Color Scripter™

1 2 3

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

 

※ MYSQL에서는 OVER함수를 지원하지 않는다.  

 

 

윈도우 함수의 종류

 

- 순위 관련 함수

* RANK : 일정 범위 내의 순위를 정한다. 동일한 값에 대해서는 동일한 순위를 나타낸다.

* DENSE_RANK : RANK함수와 비슷하나 동일한 순위를 하나의 건수로 취급. 2등이 여러개 나와도 다음 순위는 3등이 된다.

* ROW_NUMBER : 일정 범위 내의 순위를 지정하나 RANK함수와는 다르게 동일한 값이 나와도 고유한 순위를 부여한다.

 

- 집계 관련 함수

* SUM : 일정 범위내의 합계를 구한다.

* MAX : 일정 범위내의 최댓값을 구한다.

* MIN : 최솟값을 구한다.

* AVG : 평균값을 구한다.

* COUNT : 갯수를 구한다.

 

- 순서 관련 함수

* FIRST_VALUE : 일정 범위 내(PARTITION BY 를 사용하여 그룹을 지정한 후)의 첫번째 행의 값을 구한다.

* LAST_VALUE : 마지막 행의 값을 구한다.

* LAG : 파티션별 윈도우에서 이전 몇번째 행의 값을 가져온다.

* LEAD : 이후 행의 값을 가져온다.

 

- 비율 관련 함수

* RATIO_TO_REPORT : 파티션 내의 전체값에 대한 행별 값의 백분율을 소수점으로 표현한다.

* PERCENT_RANK : 파티션 내의 순위를 백분율로 구한다.

* CUME_DIST : 파티션 내의 누적순위를 백분율로 구한다.

* NTILE : 입력된 인수 값만큼 그룹으로 나누고 순위를 나눈다.

 

PARTITION BY 절과 ORDER BY 절

일반적으로 GROUP BY와 같은 기능을 하고, 윈도우 함수 내의 ORDER BY절은 메인쿼리의 ORDER BY과 같은 기능을 한다. 

 

WINDOWING 절

 

WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 정할 수 있다.

 

ROWS는 값만큼 순서된 행을, RANGE는 비교하는 컬럼의 값의 범위에 해당하는 행을 지정.

일반적으로 BETWEEN ~ AND ~ 를 사용할 수 있다.

 

BETWEEN을 사용할 경우,

 

Colored By Color Scripter™

1 2 3 4 5

ROWS 혹은 RANGE  BETWEEN  [UNBOUNDED PRECENDING  |  (임의의 값) ROW  |  (값) PRECENDING(RANGE의 경우 FOLLOWING 사용 가능) ] AND [UNBOUNDED FOLLOWING  |  (임의의 값) ROW  |  (값) FOLLOWING(RANGE의 경우 PRECENDING 사용 가능) ]

 

 

사용하지 않을 경우,

 

Colored By Color Scripter™

1 2 3

ROWS 혹은 RANGE  [UNBOUNDED PRECENDING  |  (임의의 값) ROW  |  (값) PRECENDING]

 

식으로 사용할 수 있다.

 

 PRECENDING는 해당하는 행의 이전 범위, FOLLOWING는 이후의 범위를 지정하며, UNBOUNDED와 같이 사용할 경우 끝까지 범위를 지정하라는 뜻.

 RANGE를 사용할 경우 값의 범위를 지정한다.

 

PRECENDING, FOLLOWING는 상대적인 위치를 지정하는 것이라면, ROW는 절대위치를 지정한다.

 

예) ROWS BETWEEN UNBOUNDED PRECENDING AND 1 FOLLOWING

(해당하는 행으로 부터 처음까지, 그리고 다음 1개 행까지 지정.)

 

RANGE BETWEEN 100 PRECENDING AND 50 FOLLOWING

(해당하는 행의 컬럼에 해당하는 값의 -100부터 +50까지 해당하는 행을 지정.)

 

ROWS 10 ROW

(10번째 행부터 해당하는 행까지를 지정.)

 

 

※ 모든 내용은 'SQL 전문가 가이드, 한국데이터베이스진흥원'에서 인용, 발췌하였습니다.