드디어 over, partition by절 이해하고 적용해본 기념으로 쓰는 글! 실제로 내용을 이해했던 흐름으로 작성해보자!
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 함수면 다 적용이 가능하다! 자세한 내용은 이 링크 에서 확인해볼 수 있다.
틀린 점이나 궁금한 점이 있다면 편하게 댓글 달아주세욥!!
참고
좋은 정보 감사합니다