[SQL] OVER절

테이블 정의

 

데이터는 아래와 같다

쿼리문

위 데이터에서 각 날짜별로 창고의 재고를 알기위해 필요한 데이터는 전체 합이 아닌 날짜별 누적합이다

1번 행은 10월 1일까지의 합 SUM(1)

2번 행은 10월 2일까지의 합 SUM(1, 2)

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

 

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

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

 

언뜻 GROUP BY랑 비슷하게 보이지만

1) OVER절은 행과 행 간의 관계를 정의하는 함수인 WINDOW FUNCTION이며, 그룹을 지어주기보다는 행의 범위를 지정해준다

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

ORDER BY와 PARTITION BY

괄호 안 ORDER BY는 행 집합을 정의하는 기준이다

날짜에 따른 누적합을 구해야하니 날짜로 정렬한 후 자기 자신과 상위에 위치한 행들을 집합에 포함시킨 것이다.

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

 

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

 

데이터 추가

 

위 데이터를 바탕으로 A, B 각각에 대해 날짜별로 누적합을 구하고 싶으면 쓰는것이 PARTITION BY 절이다

PARTITION BY를 통해 어떤 칼럼의 값을 기준으로 행 집합을 나눌 지 정의할 수 있다. 여기서는 '물품' 칼럼의 값을 기준으로 행 집합으로 나누게 될 것이다.

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

예시)

CREATE TABLE IF NOT EXISTS Sales (
    sale_id INT PRIMARY KEY,
    store_name VARCHAR(50),
    sale_date DATE,
    sale_amount DECIMAL(10, 2)
);

INSERT INTO Sales (sale_id, store_name, sale_date, sale_amount) VALUES
(1, 'Store A', '2025-01-01', 150.00),
(2, 'Store B', '2025-01-01', 200.00),
(3, 'Store A', '2025-01-02', 175.50),
(4, 'Store B', '2025-01-02', 190.25),
(5, 'Store A', '2025-01-03', 210.00),
(6, 'Store B', '2025-01-03', 225.50),
(7, 'Store A', '2025-01-04', 185.00),
(8, 'Store B', '2025-01-04', 215.75);

SELECT
    sale_id,
    store_name,
    sale_date,
    sale_amount,
    
    SUM(sale_amount) OVER (PARTITION BY store_name ORDER BY sale_date) AS running_total_sales,
    RANK() OVER (PARTITION BY store_name ORDER BY sale_amount DESC) AS sales_rank_by_store
FROM
    Sales
ORDER BY
    store_name,
    sale_date;

 

https://velog.io/@wltn716/SQL-Over-%EC%A0%88