SQL 윈도우 함수와 집계 함수

집계함수란 값을 모두 모으는 느낌의 함수라고 생각하면 된다.

예를 들어 SUM,  AVG 등이 있다.

 

윈도우 함수는 GROUP BY와 사용이 불가능하다. 자체에서 GROUP BY 역할을 하는 애가 있는데 그 애는 바로

PARTITION BY이다.

이 PARTITION BY는 엑셀같은 느낌이라고 생각하면 된다. 

우리는 엑셀에서 어떤 열의 총 합을 구하려고 하면 특정 열을 선택하는데, 

PARTITION BY '집계하고자하는 열' 이런 식으로 명령어를 작성하면 GROUP BY와 동일한 효과를 낸다.

그러면 왜 굳이 GROUP BY 절을 놔두고 PARTITION BY를 사용할까?

 

바로 윈도우 함수인 RANK(), DENSE_RANK(), ROW_NUMBER() 등 사용하기 위해서다.

그러니까 큰 차이는 윈도우 함수 적용 유무라고 할 수 있다.

 

아무튼 윈도우 함수인  RANK(), DENSE_RANK(), ROW_NUMBER()를 쉽게 정리해보겠다.

RANK() 는 순위를 매길 때 다음과 같다.

1,2,3,3,5 (3이 두 개인 이유는 동일한 값이기 때문)

ROW_NUMBER()는 순위를 매길 때 다음과 같다.

1,2,3,4,5 

DENSE_RANK()는 순위를 매길 때 다음과 같다.

1,2,3,3,4 ( 3이 두 개인 이유는 동일한 값이기 때문)

위의 것만 이해하면 다 이해했다고 봐도 좋다. 

 

PARTITION BY라는게 쉽게 와닿지가 않는다.

다음 실전 예제를 확인하여 좀 더 쉽게 이해해보자.

 

먼저, EMP 테이블에 다음과 같은 데이터가 있다고 가정해보자:

이제 이 쿼리를 실행한다고 해보자.

SELECT ENAME, SAL, JOB, HIREDATE,
       RANK() OVER w AS RANK_HIREDATE
FROM EMP
WINDOW w AS (PARTITION BY JOB ORDER BY HIREDATE DESC);

위의 쿼리는 아래 쿼리와 동일하다. (왜 위의 쿼리를 사용하냐면 가독성 때문)

SELECT ENAME, SAL, JOB, HIREDATE,

       RANK() OVER (PARTITION BY JOB ORDER BY HIREDATE DESC) AS RANK_HIREDATE

FROM EMP;

 

쿼리 실행 결과

다음과 같이 RANK_HIREDATE라는 열이 새로 추가가 되고 각 직업에 따라 고용 날짜의 내림차순 정렬에 따라 순위가 매겨지는 것을 확인할 수 있다.

그 외에도 PERCENT_RANK(), FIRST_VALUE(), LAST_VALUE()와 같은 윈도우 함수도 있다. 

PERCENT_RANK()는 백분율 랭크

FIRST_VALUE(), LAST_VALUE()는 처음 밸류나 마지막 밸류

LAG('행 이름', 얼만큼 이전), LEAD('행이름', '얼만큼 다음(만약 1이면 바로 다음)') 앞 뒤 값 가져오는 함수

 

그리고 다음 SQL을 살펴보자

SELECT EMPNO, ENAME, SAL,
       SUM(SAL) OVER(ORDER BY SAL
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTSAL
FROM EMP;

 

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 이런 명령어가 섞여 있다.

이 말은 대충 처음부터 현재 행까지라는 말이다.

현재 행은 CURRENT ROW이고

UNBOUNDED PRECEDING은 현재 행 이전의 모든 행을 나타내는 말이다.