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

1.   WINDOW FUNCTION 개요

-       행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수

 

-       WINDOW FUNCTION 종류

n  순위(RANK) 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER (ANSI/ISO SQL표준 / Oracle / SQL Server 등 대부분 DBMS지원)

n  집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (ANSI/ISO SQL표준 / Oracle / SQL Server 등 대부분 DBMS지원)
* SQL Server의 경우 집계 함수는 OVER절 내의 ORDER BY 구문을 지원하지 않는다.

n  행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (Oralce에서만 지원되는 함수)

n  비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT (CUME_DIST, PERCENT_RANK : ANSI/ISO SQL 표준/ Oracle 지원)
(NTILE : Oracle/ SQL Server 지원)
(RATIO_TO_REPORT : Oracle 지원)

n  선형 분석을 포함한 통계 분석 관련 함수 : Oracle의 통계 관련함수 ( CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY )

-       WINDOW FUNCTION SYNTAX

n  WINDOW 함수에는 OVER 문구 필수.
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER (PARTITION_BY 칼럼, ORDER BY 절, WINDOWING 절)
FROM 테이블 명

u  ARGUMENTS (인수) : 함수에 따라 0~N개의 인수

u  PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

u  ORDER BY절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY절을 기술한다.

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

u  BETWEEN 사용 타입 ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRET ROW | VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

u  BETWEEN 미사용 타입 ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

 

2.   그룹 내 순위 함수

가.   RANK함수

n  특정항목(칼럼) / 특정범위(PARTITION) / 전체 데이터에 대한 순위를 구하는 함수, 동일한 값에 대해서는 동일한 순위

나.   DENCS_RANK 함수

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

다.   ROW_NUMBER 함수

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

 

3.   일반 집계 함수

가.   SUM 함수

n  파티션별 윈도우의 합

나.   MAX 함수

n  파티션별 윈도우의 최대값

다.   MIN 함수

n  파티션별 윈도우의 최소값

라.   AVG 함수

n  파티션별 윈도우의 평균값

마.   COUNT 함수

n  파티션별 윈도우의 수

 

4.   그룹 내 행 순서 함수

가.   FIRST_VALUE 함수 – SQL Server에서는 지원하지 않는 함수

n  파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

n  MIN 함수를 활용하여 같은 결과를 얻을 수도 있다.

n  UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

나.   LAST_VALUE 함수 – SQL Server에서는 지원하지 않는 함수

n  파티션별 윈도우에서 가장 나중에 나온 값을 구한다.

n  MAX 함수를 활용하여 같은 결과를 얻을 수도 있다.

n  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

다.   LAG 함수 – SQL Server에서는 지원하지 않는 함수

n  파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

라.   LEAR 함수 – SQL Server에서는 지원하지 않는 함수

n  파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

 

5.   그룹 내 비율 함수

가.   RATIO_TO_REPORT 함수 – SQL Server에서는 지원하지 않는 함수

n  파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.

n  결과 값은 > 0 & <= 1 의 범위를 가진다.

n  개별 RATIO의 합을 구하면 1이 된다.

나.   PERCENT_RANK 함수 – SQL Server에서는 지원하지 않는 함수

n  파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별로 백분율을 구한다.

n  결과 값은 >= 0 & <= 1 의 범위를 가진다.

다.   CUME_DIST 함수 – SQL Server에서는 지원하지 않는 함수

n  파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율이다.

n  결과 값은 > 0 & <= 1 의 범위를 가진다.

라.   NTITLE 함수

n  파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과이다.