MSSQL Window Function 사용법 및 예제

Window Function는 데이터베이스 쿼리에서 행과 행 간의 관계를 쉽게 정의할 수 있어 행간의 연산 수행을 더욱 쉽게 처리할 수 있습니다. 다른 이름으로는 분석함수, 순위함수로 불리고 있습니다.

Window Function 구조

Window Function Syntax

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY column1,column2...] [ORDER BY column1,column2...] [WINDOWING 절] )
FROM table Name;

WINDOW_FUNCTION : Window Function, 집계함수, 순위함수 등이 있습니다.
ARGUMENTS : 사용함수에 따라서 인수가 확인하고 넣어야 합니다.
PARTITION BY : 쿼리 결과 전체집합을 파티션 단위로 분할합니다.
ORDER BY : column 항목에 대해 순위를 지정할 때 order by 절을 사용합니다.
WINDOWING : WINDOWING 절은 함수의 대상이 되는 행간의 기준 범위를 강력하게 지정할 수 있습니다.

Window Function 사용법

순위 함수 예제.

RANK : 중복 값들에 대해 동일한 순위를 표시한 후, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력합니다.
DENSE_RANK : RANK 함수와 유사하지만, 같은 순위를 하나의 등수로 표현합니다. 즉, 중복 순위가 존재해도 순차적으로 다음 순위 값을 출력합니다.
ROW_NUMBER : 같은 값에 대해 고유한 순위를 출력합니다. 중복된 data 있어도 순위는 다룰 수 있습니다.

--  RANK, DENSE_RANK, ROW_NUMBER 함수  
SELECT classNo,studentNm,korScore
      ,RANK() OVER (PARTITION BY classNo ORDER BY classNo asc, korScore DESC ) AS winFuncRank
      ,DENSE_RANK() OVER (PARTITION BY classNo ORDER BY classNo asc, korScore DESC ) AS winFuncDenseRank
      ,ROW_NUMBER() OVER (PARTITION BY classNo ORDER BY classNo asc, korScore DESC ) AS winFuncRowNum
 FROM UserInfoForWindow
 WHERE scoreyyyymm = '202401';

순위함수 RANK,DENSE_RANK, ROW_NUMBER 사용방법 및 예제 입니다.

Window Function에서 집계 함수 예제.

SUM:파티션별로 합계를 출력합니다.
AVG:파티션별로 평균을 출력합니다.
COUNT:파티션별로 행 수를 출력합니다.
MAX : 파티션별로 최댓값 출력합니다.
MIN :파티션별로 최솟값을 출력합니다.

-- SUM,AVG,COUNT,MAX, MIN
 SELECT classNo,studentNm,korScore
        ,SUM(korScore) OVER (partition by classNo  ORDER BY classNo asc) AS winFuncSum
        ,AVG(korScore) OVER (partition by classNo  ORDER BY classNo asc) AS winFuncAvg
        ,COUNT(korScore) OVER (partition by classNo  ORDER BY classNo asc) AS winFuncCnt
        ,MAX(korScore) OVER (partition by classNo  ORDER BY classNo asc) AS winFuncMax
       ,MIN(korScore) OVER (partition by classNo  ORDER BY classNo asc) AS winFuncMin
  FROM UserInfoForWindow
 WHERE scoreyyyymm = '202401';

집계 함수 sum,avg,count,max,min 사용방법및 예제 입니다.

Window Function 에서 WINDOWING 사용법.

ROWS : 물리적 단위로 행의 집합을 지정합니다.
RANGE : 논리적인 주소에 의해 행 집합을 지정합니다.
BETWEEN ~ AND : 파티션 행의 시작과 끝의 위치를 지정할 때 사용합니다.
UNBOUNDED PRECEDING : 파티션의 첫 번째 행에서 시작되도록 지정합니다.
UNBOUNDED FOLLOWING : 파티션의 마지막 행에서 끝나도록 지정합니다.
CURRENT ROW : 파티션의 실행되는 지점의 행의 위치를 저 정하므로 CURRENT ROW는 시작, 끝 어디에도 지정할 수 있습니다.

-- UNBOUNDED PRECEDING : 파티션별 위치가 첫 번째 행임을 의미함
-- UNBOUNDED FOLLOWING : 파티션별 위치가 마지막 행임을 의미함
-- CURRENT ROW : 파티션별 시작 위치가 현재 행임을 의미함
SELECT classNo,studentNm,korScore
      ,SUM(korScore) OVER (PARTITION BY classNo ORDER BY classNo,studentNm, korScore ASC
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS winUrcr
      ,SUM(korScore) OVER (PARTITION BY classNo ORDER BY classNo,studentNm, korScore ASC
                           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS winFuncCrUf
      ,SUM(korScore) OVER (PARTITION BY classNo ORDER BY classNo 
                           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS winFuncUpUf
 FROM UserInfoForWindow
 WHERE scoreyyyymm = '202401'
 ORDER BY classNo,studentNm,korScore;