[MSSQL] window 함수

Window 함수란 무엇인가?

 

 

기존 관계형 데이터베이스는 칼럼과 칼럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면,

 

행과 행간의 관계를 정의하거나,  행과 행간을 비교, 연산하는 것을

 

하나의 SQL문으로 처리하는 것은 매우 어려운 일이었다.

 

하지만,  윈도우 함수를 이용한다면 행과 행사이의 관계를 쉽게 정의할 수 있다.

 

 

"윈도우 함수"(window function)란, 함수가 기반으로 하는 쿼리로부터 나오는 행들의 서브 집합을 대상으로 

 

각 행별로 계산을 해서 스칼라 값을 출력하는 함수를 말한다. 

 

행들의 서브집합을 윈도우 라고 하며, 현재 행과 관련된 윈도우 설정을 기반으로 한다. 

 

윈도우 함수 문법에는 OVER라는 절이 포함된다. 

 

간단히 말해서 집합을 대상으로 계산한 다음 단일 값을 출력하는 것이라고 생각하면 된다. 

 

일반적인 집계 계산 방식에는 SUM,COUNT,AVG 가 있다. 

 

 

 

 

 

윈도우 함수의 특징

 

1. 윈도우 함수는 행과 행 간의 관계를 정의하기 위해서 제공되는 함수

 

2. 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.

 

3. 윈도우 함수는 GROUP BY 구문과 병행하여 사용할 수 없다.

 

4. 윈도우 함수로 인해 결과 건수가 줄어들지는 않는다.

 

5. WINDOW 함수의 PARTITION BY 구문과 GROUP BY 구문은 

 

둘 다 파티션을 분할한다는 의미에서는 유사하다. 

 

 

 

 

 

 

윈도우 함수의 구조

SELECT WINDOW_FUNCTION(ARGUMENTS) 
  OVER (PARTITION BY 칼럼 ORDER BY 칼럼 WINDOWING절) 
FROM 테이블명;

1. ARGEMENTS(인수) : 윈도우 함수에 따라서 0 ~ N 개의 인수를 설정한다.

 

2. PARTITION BY : 전제집합을 기준에 의해 소그룹으로(SUBSET) 나눈다. -> GROUP BY 와 비슷한 역할 수행

 

3. ORDER BY : 어떤 항목에 대해서 정렬한다.

 

4. WINDOWING : 행 기준 범위를 정한다. / ROWS는 물리적 결과의 행 수 이고  

 

RANGE 는 논리적인 값에 의한 범위이다.

 

 

 

쿼리를 그룹핑하게(GROUP BY 를 통하여) 되면, 

 

집계 형태로 정보가 제공되기 때문에 전체적인 통찰력을 얻을 수 있는 

 

장점이 있지만, 그룹에 대한 세부사항을 확인할 수 없다는 단점도 있다.

 

 

아래의 쿼리 예시를 보면 전체적으로 부서마다 급여의 평균을 한번에 볼 수 있지만.

 

세부적으로 직원들이 얼마나 받는지는 알 수 없음.

 

SELECT  
     buseo 
,    AVG(basicPay) AS salary 
FROM dbo.TBLINSA WITH(NOLOCK) 
GROUP BY buseo

 

행들을 그룹핑 하게되면, 쿼리의 모든 계산은 그룹으로 정의된 단위로만 수행된다. 

 

하지만, 경우에 따라서는 세부 항목과 집계를 통한 결과를 함께 계산해야 할 때도 있다. 

 

윈도우 함수를 사용하면 이와같은 제한사항을 고려하지 않아도 된다.

 

 

 

윈도우 함수는 작업할 함수에 대한 행 집합을 정의하는 OVER 절을 가지고 있지만, 

 

쿼리 자체의 데이터를 이 순서대로 변경하지는 않는다. 

 

다시 말해, 그룹핑된 쿼리들은 쿼리에서 집합 또는 그룹을 정의하게 된다.

 

따라서 쿼리의 모든계산은이렇게 만들어진 그룹을 대상으로 하게 된다. 

 

윈도우 함수를 이용하게 되면, 각각의 함수별로 집합이 만들어지는 것이지, 

 

전체 쿼리를 대상으로 만들어지는 것은 아니다.

 

 

서브쿼리에서와 같이 윈도우 함수도 집합에 대해 연산을 수행할 수 있다. 

 

하지만 서브 쿼리는 데이터에 대한 뷰를 대상으로 연산한다는 점에서 차이가 있다. 

 

쿼리가 테이블 연산자나 필터를 포함하고 있으며 기반이 되는 쿼리로부터 

 

만들어지는 행의 서브셋을 대상으로 연산하는 서브 쿼리를 사용해야 한다면, 

 

서브 쿼리 내에서 기반이 되는 쿼리에 대해 동일한 로직을 여러 번 반복해서 수행해야 한다.

 

이와는 달리 윈도우 함수는 기반이 되는 쿼리의 결과로부터

 

만들어지는 행의 서브셋을 대상으로 적용되는 것이지, 

 

데이터 자체의 뷰를 대상으로 수행되는 것은 아니다. 

 

따라서 기반이 되는 쿼리 에 어떠한 것이 추가되더라도 

 

쿼리에서 사용되는 모든 윈도우 함수에서는 자동으로 이를 사용할 수 있다.

윈도우 함수를 사용하는 또 다른 장점은, 

 

필요에 따라 결과 집합의 관계형 측면을 훼손시키지 않으면서도 

 

**계산 작업의 일부로 순서를 정의할 수 있게 해준다는 점**이다.

 

(출력하는 과정에서 ORDER BY 절을 포함시키지 않으면 결과의 순서를 보장할 수 는 없다.)