SQL OVER 절

1. 테이블 정의

물품 A의 재고관리를 위해 다음과 같은 테이블 "창고"가 있다고 가정해 보자.

 

칼럼명	데이터 타입
번호(PK)	LONG
날짜	DATE
수량	INT

위 테이블은 창고에 A가 언제, 얼마나 입/출고 되었는지 나타낸다. 수량이 양수일 시 입고, 음수일 시 출고를 의미한다.

데이터는 아래와 같다.

 

번호	날짜	수량
1	2021-10-01	5
2	2021-10-02	-3
3	2021-10-04	6

2. OVER 절

위 데이터에서 각 날짜별로 창고의 재고를 알고싶다고 할 때, 필요한 데이터는 전체 합이 아닌 날짜별 누적합이다. 데이터는 아래와 같을 것이다.

 

번호	날짜	수량	재고
1	2021-10-01	5	5
2	2021-10-02	-3	2
3	2021-10-04	6	8

 

 

즉,
1번 행은 10월 1일까지의 합 SUM(1),
2번 행은 10월 2일까지의 합 SUM(1, 2),
3번 행은 10월 3일까지의 합 SUM(1, 2, 3) 이다.

이를 보면 각 행별로 SUM 함수에 사용된 레코드의 집합이 다 다른 것을 알 수 있다.

이렇게 각 행별로 특정 기준에 따라 필요한 집합을 구해 함수를 적용하고 싶을 때 쓰는 구문이 OVER 절이다.

쿼리로 나타내면 아래와 같다.

 

 

SELECT 번호
  , 날짜
  , 수량
  , SUM(수량) OVER(ORDER BY 날짜) AS 재고
FROM 창고

 

 

여기서 OVER은 위에서 말했듯이 함수 SUM 에 대해 특정 행 집합을 적용하겠다는 의미이다.

언뜻 들으면 GROUP BY랑 비슷하게 들리지만 둘은 엄연히 다르다..!

1) OVER절은 행과 행 간의 관계를 정의하는 함수인 WINDOW FUNCTION 이며,

그룹을 지어주기보다는 행의 범위를 지정해주는 의미에 좀 더 가깝다.

2) GROUP BY절은 결과 행 개수에 영향을 미치지만 OVER절은 영향을 미치지 않는다.

 

(다만 다음에 설명할 PARTITION BY와 함께 사용하게 되면 그룹을 지어준다는 의미에서는

GROUP BY와 어느정도 유사하다고 볼 수 있겠다...!)

3. ORDER BY 와 PARTITION BY

그럼 괄호 안 ORDER BY 는 무엇일까? 행 집합을 정의하는 기준이다.
날짜에 따른 누적합을 구해야 하니, 날짜로 정렬한 후 자기 자신과 상위에 위치한 행들을 집합에 포함시킨 것이다.

OVER 절에서 집합을 나누는 기준은 괄호 안에 작성하며, 이 안에 ORDER BY 와 PARTITION BY를 사용할 수 있다.

ORDER BY 는 위에서 짚고 넘어갔으니 PARTITION BY 에 대해 알아보자.

우선 위에서 사용했던 테이블에 물품 칼럼을 하나 더 추가해보자.

 

칼럼명	데이터 타입
번호(PK)	LONG
날짜	DATE
물품	VARCHAR(5)
수량	INT

 

이를 통해 창고 테이블로 A뿐만이 아닌 다른 물품에 대해서도 입/출고 현황을 알 수 있다.

데이터도 조금 수정해보자!

 

번호	날짜	물품	수량
1	2021-10-01	A	5
2	2021-10-02	A	-3
3	2021-10-04	A	6
4	2021-10-03	B	4
5	2021-10-05	B	-2

 

위 데이터를 바탕으로 A, B 각각에 대해 날짜별로 누적합을 구하고 싶다고 가정해보자.

이때 쓰는 것이 PARTITION BY 절이다.

 

PARTITION BY를 통해 어떤 칼럼의 값을 기준으로 행 집합을 나눌 지 정의할 수 있다.

여기서는 '물품' 칼럼의 값을 기준으로 행 집합을 나누게 될 것이다.

 

(물론 날짜별로 재고를 구해야 하니 ORDER BY 조건도 들어가야 할 것이다!)

이에 따라 쿼리는 아래와 같다.

SELECT 번호
  , 날짜
  , 수량
  , SUM(수량) OVER(PARTITION BY 물품 ORDER BY 날짜) AS 재고
FROM 창고
ORDER BY 날짜

 

실행 시 결과는 아래와 같다.

 

번호	날짜	물품	수량	재고
1	2021-10-01	A	5	5
2	2021-10-02	A	-3	2
4	2021-10-03	B	4	4
3	2021-10-04	A	6	8
5	2021-10-05	B	-2	2

 

4. 마무리

지금까지 OVER 절과 ORDER BY, PARTITION BY에 대해 알아보았다. 예제에서는 SUM 에만 OVER 절을 적용해 보았지만, 순위함수, 집계함수, 분석함수, NEXT VALUE FOR 함수면 다 적용이 가능하다! 자세한 내용은 이 링크 에서 확인해볼 수 있다.