MariaDB 강좌 / 윈도 함수로 누적 합계와 이전·다음 행 비교하기

SUM의 윈도 계산은 행을 유지하면서 누적 합계를 만들고 LAG·LEAD는 정렬 기준상 이전·다음 행의 값을 읽습니다. 날짜가 같은 행의 순서와 누적 범위인 프레임을 명시해야 매번 같은 의미의 누적값을 얻을 수 있습니다.

누적할 행의 단위와 시간 기준 정하기

주문별 누적 매출과 날짜별 누적 매출은 다릅니다. 여기서는 paid 주문 한 건마다 상품 금액을 합산한 뒤 주문 시각·번호 순으로 누적합니다. 배송비·취소·환불을 어떻게 반영할지는 별도 정책이며 이 예제는 paid 상세의 수량×주문 당시 단가만 합산합니다. 행별 계산과 그룹별 계산의 차이는 윈도 함수로 순번과 순위 계산하기, “이전 행”과 실제 날짜 차이의 차이는 날짜 계산하기: 기간·날짜 차이·표시 형식과 함께 판단할 수 있습니다.

시간 순서가 정해진 주문 데이터 준비하기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c33_customers, c33_products, c33_orders, c33_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

USE shopcourse;
CREATE TABLE c33_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c33_order_customer FOREIGN KEY (customer_id)
  REFERENCES c33_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c33_item_order FOREIGN KEY (order_id)
  REFERENCES c33_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c33_item_product FOREIGN KEY (product_id)
  REFERENCES c33_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c33_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c33_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c33_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c33_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

paid 주문은 1월 10일 101번, 1월 20일 102번, 2월 5일 103번입니다. 날짜가 같을 때도 순서를 결정할 수 있도록 order_id를 보조 기준에 둡니다. 실제 시스템에서 주문 번호가 시간 순서를 보장하는지와 무관하게, 같은 시각의 출력 순서를 명시하는 역할입니다.

ROWS 프레임으로 주문별 누적 금액 계산하기

WITH order_totals AS (
 SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
 FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
 WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
 SUM(goods_total) OVER(ORDER BY ordered_at,order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
 LAG(goods_total) OVER(ORDER BY ordered_at,order_id) AS previous_total,
 LEAD(goods_total) OVER(ORDER BY ordered_at,order_id) AS next_total
FROM order_totals ORDER BY ordered_at,order_id;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 정렬된 첫 행부터 현재 행까지 포함합니다. 프레임을 생략했을 때의 기본 범위나 같은 정렬값의 처리에 기대기보다 누적 의도를 명시합니다. 같은 날짜가 많은 데이터에서는 ROWS와 RANGE의 동점 범위가 특히 다를 수 있습니다. 윈도 계산과 프레임 개요를 참고하세요.

동점 처리는 테이블을 바꾸지 않는 다음 독립 예제로 비교할 수 있습니다. 501·502번은 시각이 같고 금액만 다릅니다.

WITH sample AS (
 SELECT 501 AS order_id,CAST('2026-01-10 10:00:00' AS DATETIME) AS ordered_at,10 AS amount
 UNION ALL SELECT 502,'2026-01-10 10:00:00',20
 UNION ALL SELECT 503,'2026-01-11 10:00:00',5
)
SELECT order_id,amount,
 SUM(amount) OVER (ORDER BY ordered_at,order_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_unique,
 SUM(amount) OVER (ORDER BY ordered_at,order_id
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_unique,
 SUM(amount) OVER (ORDER BY ordered_at
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_time
FROM sample ORDER BY ordered_at,order_id;

첫 두 누적 열은 정렬 기준이 시각·고유 번호로 같고 프레임만 다릅니다. 고유 번호 때문에 동점이 없어 둘 다 10·30·35입니다. 세 번째 열은 RANGE에서 번호 기준만 제거했으므로 같은 시각의 두 행을 함께 포함해 30·30·35가 됩니다. 프레임을 ROWS로 바꾸면서 고유한 보조 기준까지 생략하면 동점 안의 행 순서를 단정할 수 없습니다.

이전·다음 값과 변화량의 의미 확인하기

LAG의 이전은 시간 차이가 일정한 직전 날짜가 아니라 정렬상 앞 행입니다. 주문이 없었던 날짜를 자동으로 만들어 전일 값으로 가져오는 기능이 아닙니다. 전일 대비 지표가 필요하면 날짜별 집계와 누락 날짜를 채우는 기준을 따로 준비해야 합니다. LAG의 행 위치 기준을 확인하세요.

현재 금액에서 이전 금액을 빼면 두 주문의 금액 차이를 구할 수 있지만 첫 행은 이전 값이 없어 NULL입니다. 무조건 COALESCE로 0을 넣으면 첫 주문의 증가액을 그 금액 전체로 정의하는 셈이므로 보고서의 기준을 먼저 정합니다. 결측과 실제 0은 같은 정보가 아닙니다.

필터를 언제 적용하는지에 따라 누적 범위 달라지기

WHERE로 2월 주문만 먼저 남긴 다음 누적하면 2월 안에서 누적이 시작됩니다. 전체 기간을 누적한 결과에서 2월 행만 보여 주려면 윈도 계산을 안쪽 CTE에서 끝내고 바깥에서 날짜 조건을 적용해야 합니다. 두 방식 모두 문법상 가능하지만 지표의 시작점이 다릅니다.

상세 행을 주문 단위로 먼저 합치지 않으면 한 주문의 여러 줄마다 누적이 진행됩니다. 누적 결과를 검토할 때는 기본 단위·대상 상태·정렬 키·프레임·필터 위치 다섯 가지를 함께 기록하면 재현하기 쉽습니다.

아래 첫 쿼리는 전체 paid 주문을 누적한 다음 2월만 표시합니다. 둘째 쿼리는 같은 날짜 조건을 윈도 계산 전으로 옮깁니다. 기존 c33 테이블을 사용하며 원본 데이터는 바꾸지 않습니다.

WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
), running AS (
 SELECT order_id,ordered_at,goods_total,
        SUM(goods_total) OVER (ORDER BY ordered_at,order_id
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
 FROM order_totals
)
SELECT order_id,goods_total,running_total FROM running
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;

WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
       SUM(goods_total) OVER (ORDER BY ordered_at,order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;

두 결과 모두 103번 한 행이지만 누적의 시작점이 다릅니다. 전체 누적을 보여 주는 보고서와 월 안에서 다시 시작하는 보고서 중 어느 의미가 필요한지 먼저 정하세요.

정렬과 경계 행을 검증하고 정리하기

작은 데이터에서 직접 11500, 19500, 39000의 누적을 계산해 대조합니다. 첫 행의 LAG와 마지막 행의 LEAD가 NULL인지, 날짜가 같은 테스트 행을 추가했을 때 보조 기준이 일관적인지 확인하세요. 최종 ORDER BY도 따로 명시하고 인덱스의 우연한 순서에 의존하지 않습니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.c33_items;
DROP TABLE shopcourse.c33_orders;
DROP TABLE shopcourse.c33_products;
DROP TABLE shopcourse.c33_customers;