[SQLD] SQL활용- 윈도우 함수

윈도우 함수

윈도우 함수는 행과 행 간의 관계를 정의하기 위해 제공되는 함수이다. 윈도우 함수를 사용해 순위, 합계 , 평균, 행 위치등을 조작 할 수 있다

윈도우 함수 구조

SELECT WINDOW_FUNC(ARGUMENTS)
	OVER (PARTITION BY 컬럼
        ORDER BY WINDOWING절)
FROM 테이블명

• ARGUMENTS(인수) - 윈도우 함수에 따라 0-N개의 인수를 설정한다
• PARTITION BY         - 전체 집합을 기준에 의해 소그룹 으로 나눈다.
• ORDER BY                - 어떤 항목에 대해 정렬한다.
• WIDOWING              - 행 기준의 범위를 정한다. ROWS는 물리적 결과의 행 수이고 RANGE는 논리적인 값에 의한 범위이다.

WINDOWING

WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다. 다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.

구조	설명
ROWS	부분 집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
RANGE	논리적인 주소에 의해 행 집합을 지정
BETWEEN ~ AND	윈도우의 시작과 끝의 위치 지정
UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 행
UNBOUNDED FOLLOWING	윈도우 마지막 위치가 마지막 행
CURRENT ROW	윈도우 시작 위치가 현재 행

 

사용 예시

SELECT 
	MGR,
	ENAME,
	SAL,
	SUM(SAL) OVER(PARTITION BY MGR ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_RANGE
FROM EMP e 

MGR |ENAME |SAL |SUM_RANGE|
----+------+----+---------+
7566|FORD  |3000|     6000| - 동일한 값이 1,2에 있기 때문에 더해짐
7566|SCOTT |3000|     6000|
7698|JAMES | 950|      950|
7698|WARD  |1250|     3450|
7698|MARTIN|1250|     3450|
7698|TURNER|1500|     4950|
7698|ALLEN |1600|     6550|
7782|MILLER|1300|     1300|
7788|ADAMS |1100|     1100|
7839|CLARK |2450|     2450|
7839|BLAKE |2850|     5300|
7839|JONES |2975|     8275|
7902|WOOK  | 800|      800|

 -> CURRENT ROW 현재 행을 기준으로 UNBOUNDED PRECEDING 첫번째 행까지 범위를 지정하며 값이 같으면 동일한 행으로 본다

 

순위 함수

ANSI/ISO 표준 기준  ORACLE, SQL SERVER등 다른 RDBMS에서도 지원하는 기능이다.

RANK

RANK 함수는 ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다. 이때 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다.

동일값에 대해서는 동일 순위 부여하고  중복 순위 다음은 해당 개수만큼 증가한다.

RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC)

 -> JOB을 파티션으로 만들고 JOB별 순위를 조회한다.

DENSE_RANK

동일한 순위를 하나의 건수로 계산한다.

ROW_NUMBER

동일한 순위에 대해서 고유의 순위를 부여한다.

 

집계 함수

집계 함수	설명
SUM	파티션 별로 합계 계산
AUG	파티션 별로 평균 계산
COUNT	파티션 별로 행 수 계산
MAX/ MIN	파티션 별로 최댓값과 최솟값 계산

행 순서 관련 함수

행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있고, 특정 위치의 행도 출력 할 수 있다.

행 순서	설명
FIRST_VALUE	- 파티션에서 가장 처음에 나오는 값 - MIN함수 결과값과 같은 결과
LAST_VALUE	- 파티션에서 가장 나중에 나오는 값 - MAX함수 결과값과 같은 결과
LAG	- 이전 행
LEAD	- 윈도우에서 특정 위치의 행을 가지고오며 기본값은 1이다.

 

비율 관련 함수

비율 관련 함수는 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할할 결과 등을 조회할 수 있다.

비율 함수	설명
CUME_DIST	- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다 - 누적 분포상에 위치를 0~1 사이의 값을 가진다
PERCENT_RANK	- 파티션에서 제일 먼저 나온 것을 0, 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회한다
NTITLE	- 파티션별로 전체 건수를 ARGUMENT 값으로 N등분 결과를 조회한다
RATIO_TO_REPORT	- 파티션 내에 전체 SUM(컬럼)에 대한 행 별 컬럼값의 백분율을 소수점까지 조회한다