[SQL] 분석함수(윈도함수) OVER( ), PARTITION BY, ORDER BY

분석함수(윈도함수)란?

특정 데이터 집합에 대해 계산을 수행하고 그 결과를 각 행에 출력하는 함수이다.
 OVER( PARTITION BY <파티션화 기준> ORDER BY <정렬 기준>)

 

OVER를 사용하여 테이블 쭉 더하고 개수세서 조회

OVER는 SQL에서 사용되는 윈도우 함수와 함께 사용되는 절이다.

OVER절은 윈도우 함수에 적용할 파티션과 정렬 순서를 지정하는데 사용된다.

 

현재 위 쿼리에서는 

COUNT(HEIGHT) OVER( ORDER BY STUDNO)
SUM(HEIGHT) OVER(ORDER BY STUDNO)

이 두개를 사용해서 COUNT는 누적으로 1개씩 갯수 증가, SUM은 이전 의 합에 누적해서 쌓아가는 것을 확인할 수 있다.

---

PARTITION BY를 사용하여 분석함수 적용

PARTITION BY는 SQL에서 사용되는 윈도우 함수의 OVER 절과 함께 사용되는 추가적인 절이다.

PARTITION BY를 사용하면 데이터를 서로 다른 그룹으로 분할해 윈도우 함수를 그룹 별로 계산할 수 있다.

 

현재 위 쿼리에서는

COUNT(HEIGHT) OVER(partition by DEPTNO ORDER BY STUDNO)
SUM(HEIGHT) OVER(partition by DEPTNO ORDER BY STUDNO)

DEPTNO를 기준으로, 같은 DEPTNO끼리 정렬하여 나타낼 수 있다. 이것이 PARTITION BY 사용해서 나타낸 VIEW이다.

---

다시 한번 쿼리를 짜보았다. EMPVIEW9 뷰를 생성하여,

윈도함수인 OVER을 사용해 데이터 정렬을 지정해주었더니 위와 같이 SUM으로 정렬되어 나올 수 있었다.

 

 

PARTITION BY DEPTNO :

DEPTNO를 기준으로 데이터를 파티션화한다. 즉, 같은 부서(DEPTNO)내에서 윈도함수를 실행한다.

 

ORDER BY EMPNO :

EMPNO를 기준으로 데이터를 정렬한다. 윈도 함수가 적용되는 순서를 정의한다.

 

ROW 2 PRECEDING :

현재 행을 기준으로 이전 두 행까지의 범위를 설정한다.
따라서 윈도 함수는 현재 행과 이전 두 행을 포함하여 세 행의 SAL 값을 사용해 COUNT를 계산할 수 있다.

 

---

간단한 예시로, 아래 테이블이 있다.

DEPTNO | EMPNO | ENAME | SAL | COUNT(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS 2 PRECEDING)

결과에서 볼 수 있듯이, ROW 2 PRECEDING으로 인해 DEPTNO10인 경우, 현재 행과 이전 두 행을 포함해 COUNT(SAL)을 계산하고, DEPTNO 20인 경우는 이전 두 행이 없으므로 COUNT(SAL)을 계산할 수 없다.

따라서 해당 결과가 NULL로 나온다.