[SQL] 윈도우 함수(Window Function)

SQL에는 데이터를 그룹화하여 하나의 값을 반환하는 집계함수가 존재한다. 집계함수는 컬럼 간의 연산은 잘 수행하지만, 행(레코드) 간의 연산은 수행하지 못하는 단점이 존재한다. 이를 위해 행 간의 연산을 처리하기 위한 함수인 윈도우 함수(Window Function)을 사용한다.

 

윈도우 함수(Window Function)

 

윈도우 함수란 행과 행 사이의 관계를 쉽게 정의하기 위해 사용하는 함수다.

윈도우 함수에는 몇 가지 특징이 존재한다.

• 서브쿼리는 사용 가능하지만, 중첩쿼리는 사용이 불가능.
• OVER 키워드가 필수로 사용.
• BETWEEN을 사용하는 타입/사용하지 않는 타입.

 

더불어 윈도우 함수는 크게 5가지 그룹으로 분류가 가능하다.

• 그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER
• 그룹 내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (SQL Server에서는 OVER 절의 OREDER BY 지원 X)
• 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클만 지원)
• 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT  
• 선형 분석을 포함한 통계 분석 함수

 

윈도우 함수의 기본 문법

 

윈도우 함수는 아래와 같은 형태를 기본으로 가진다. 대괄호([])에 쓰인 부분은 생략이 가능하다.

SELECT WINDOW_FUNCTION (ARGUMENTS)
	OVER([PARTITION BY 컬럼] [ORDER BY] [ROWS BETWEEN ~ AND ~])
FROM TABLE;

 

각 문법에 대한 기본적인 설명은 아래와 같다.

• WINDOW_FUNCTION : 윈도우 함수 
• ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다. 
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.   
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.

 

윈도우 함수의 경우, 행을 하나씩 선택하여 연산을 수행한다. 때문에 경우에 따라서 BETWEEN 구문을 사용해 연산할 행을 프레임으로 지정해야 할 경우가 존재한다.

 

앞서 윈도우 함수에는 OVER 절이 필수로 들어가야 한다고 언급하였다. OVER 절에는 다양한 옵션이 존재한다.

• PARTITION BY 컬럼
  • SQL에서 GROUP BY로 그룹화하는 것과 같이 윈도우 함수를 적용할 데이터를 나눈다.
  • PARTITION BY 과일로 옵션을 주게되면, 과일별로 윈도우를 나누어 계산이 가능하다.
• ORDER BY 컬럼
  • 데이터 정렬 기능과 같이 윈도우 안에서 함수를 적용하기 전에 데이터를 정렬한다.
  • PARTITION을 지정하면 그 안에서 데이터를 정렬한다.
• ROWS BETWEEN ~ AND ~
  • 윈도우의 프레임을 지정한다.
  • ~ 에 조건으로 제시될 수 있는 키워드가 여럿 존재한다.
    • CURRENT ROW : 현재 행
    • n PRECEDING : n행 앞
    • n FOLLOWING : n행 뒤
    • UNBOUNDED PRECEDING : 앞 행 전부
    • UNBOUNDED FOLLOWING : 뒷 행 전부

 

예시

 

지금부터 윈도우 함수에 대한 몇가지 예시를 소개하고자 한다.

 

RANK

 

순위를 구하는 함수로 특정 범위 내에서 순위를 구하거나 전체 데이터 내에서 순위를 구할 수 있다.

동일한 값에 대해서는 동일한 순위를 부여하게 된다.

SELECT JOB, ENAME, SAL,
       RANK() OVER (ORDER BY SAL DESC) ALL_RANK, -- 급여 높은 순
       RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK -- job 별로 급여 높은 순
FROM EMP ;

 

SUM

 

SUM 함수를 사용하여 파티션별 윈도우의 합을 구할 수 있다.

아래는 사원들의 급여와 같은 매니저를 두고 있는 사원들의 급여 합을 구하는 예제이다.

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;

 

LAG

 

파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. (SQL SERVER 에는 지원 X)

아래는 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력하는 예제이다.

SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

 

NTILE

 

파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다. (boxplot)

아래는 전체 사원을 급여가 높은 순으로 정렬하고 급여를 기준으로 4개 그룹으로 분류한다.

SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) QUAR_TILE
FROM EMP;

 

---

Reference

https://velog.io/@jwkim_1018/SQL-%EC%9C%88%EB%8F%84%EC%9A%B0-%ED%95%A8%EC%88%98window-function

SQL - 윈도우 함수(window function)

https://moonpiechoi.tistory.com/128

[SQL] 윈도우 함수 (WINDOW FUNCTION)