컴공지식/데이터베이스

SQL 윈도우 함수와 집계 함수

개강한 공대생 2024. 6. 12. 20:36

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

예를 들어 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은 현재 행 이전의 모든 행을 나타내는 말이다.