SQL Server - 분석 함수 Window functions

 

윈도우 함수 내의 여러 갈래

 - 집계 함수 : AVG, SUM, COUNT, MIN, MAX  etc..

 - 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER etc..

 - 분석 함수 : LEAD, LAG, FIRST_VALUE, LAST_VALUE etc...

 

 

OVER절은 함수들이 적용될 행의 분할이나 순서를 담당한다. 따라서 이러한 기능들을  Window functions 이라고 한다. OVER Clause는 세가지 인수를 허용한다. OVER clause defines the partitioning and ordering of rows(i.OVER clause defines the partitioning and ordering of rows(i.e a window) for the above functions to operate on. Hence these functions are called window functions. The OVER clause accepts the following three arguments to define a window for these functions to operate on.

 

 - ORDER BY : 행의 논리적 순서를 정의함.Defines the logical order of the rows

 - PARTITION BY : 쿼리의 결과 화면을 파티션으로 나눔. 윈도우 함수는 파티션별로 따로 적용이 된다. Divides the query result set into partitions. The window function is applied to each partition separately.

 - ROWS or RANGE clause : 파티션 내에서 시작과 끝을 지정함으로써 파티션 내에 행을 제한함.Further limits the rows within the partition by specifying start and end points within the partition.

 

 

example

SELECT Name, Gender, Salary,
AVG(Salary) OVER (PARTITION BY Gender ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average,
COUNT(Salary) OVER (PARTITION BY Gender ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Count,
SUM(Salary) OVER (PARTITION BY Gender ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Sum
FROM Employees

만약 범위를 지정하지 않는다면 기본값은

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

이다. 제한 없는 첫 행부터 지금의 행까지 범위를 지정한 것이고 옵션이 있다.

 

 - ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

: 바로 전 행 한개 바로 뒷 행 한개 까지가 범위로 해서 함수가 실행됨

 - RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

: 범위를 현재행부터 끝 행까지로 지정.

 

 

여기서 알아봐야할

ROWS and RANGE의 차이점 

어떻게 중복을 처리하느냐가 다르다. ROWS는 중복을 distinct values값으로 보고 처리를 하나 Range는 중복을 한 개의 개체로 취급하여 넘어감.

 

 

SELECT Name, Salary,
	SUM(Salary) OVER(ORDER BY Salary) AS [Default],
    SUM(Salary) OVER(ORDER BY Salary
    		RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range],
    SUM(Salary) OVER(ORDER BY Salary 
    		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Rows]
FROM Employees

위의 쿼리에 대한 결과