MySQL에서 누적 합계(Cumulative Sum) 열을 만드는 방법

MySQL에서 누적 합계(cumulative sum) 열을 만들려면 먼저 사용자 정의 변수를 생성하고 0으로 초기화해야 합니다. 누적 합계란 이전 행까지의 값을 차례대로 더해 나가면서 각 행마다 합계를 기록하는 방식입니다.

1단계: 변수 초기화

먼저 SET 명령어를 사용해 변수를 선언하고 0으로 초기화합니다. 기본 문법은 다음과 같습니다.

set @anyVariableName := 0;

2단계: 누적 합계 쿼리 작성

누적 합계 열을 만드는 기본 문법은 다음과 같습니다.

select yourColumnName1, yourColumnName2, ........N,
(@anyVariableName := @anyVariableName + yourColumnName2) as anyVariableName
from yourTableName order by yourColumnName1;

실습 예제

개념을 이해하기 위해 실제 테이블을 만들어 확인해 보겠습니다. 아래는 테이블 생성 쿼리입니다.

mysql> create table CumulativeSumDemo
    -> (
    -> BookId int,
    -> BookPrice int
    -> );
Query OK, 0 rows affected (0.67 sec)

다음으로 INSERT 문을 사용해 샘플 데이터를 입력합니다.

mysql> insert into CumulativeSumDemo values(101,400);
Query OK, 1 row affected (0.15 sec)

mysql> insert into CumulativeSumDemo values(102,500);
Query OK, 1 row affected (0.16 sec)

mysql> insert into CumulativeSumDemo values(103,600);
Query OK, 1 row affected (0.16 sec)

mysql> insert into CumulativeSumDemo values(104,1000);
Query OK, 1 row affected (0.18 sec)

입력된 전체 레코드를 조회해 보겠습니다.

mysql> select *from CumulativeSumDemo;

실행 결과는 다음과 같습니다.

+--------+-----------+
| BookId | BookPrice |
+--------+-----------+
|    101 |      400 |
|    102 |      500 |
|    103 |      600 |
|    104 |     1000 |
+--------+-----------+
4 rows in set (0.00 sec)

3단계: 누적 합계 열 추가하기

누적 합계 열을 추가하려면 먼저 변수를 0으로 초기화합니다.

mysql> set @CumulativeSum := 0;
Query OK, 0 rows affected (0.00 sec)

앞서 설명한 문법을 적용하여 누적 합계 열을 계산하는 쿼리를 실행합니다.

mysql> select BookId, BookPrice, (@CumulativeSum := @CumulativeSum + BookPrice) as CumSum
    -> from CumulativeSumDemo order by BookId;

아래 결과에서 오른쪽에 누적 합계 열(CumSum)이 함께 출력되는 것을 확인할 수 있습니다.

+--------+-----------+--------+
| BookId | BookPrice | CumSum |
+--------+-----------+--------+
|    101 |       400 |    400 |
|    102 |       500 |    900 |
|    103 |       600 |   1500 |
|    104 |      1000 |   2500 |
+--------+-----------+--------+
4 rows in set (0.00 sec)

결과를 보면 각 행의 가격이 순서대로 누적되어 400 → 900 → 1500 → 2500으로 증가하는 것을 알 수 있습니다.

참고: MySQL 8.0 이상에서는 윈도우 함수 활용 가능

MySQL 8.0부터는 사용자 정의 변수 대신 윈도우 함수(Window Function)를 사용하는 것이 권장됩니다. SUM() OVER() 구문을 활용하면 더 깔끔하고 성능 면에서도 유리하게 누적 합계를 구할 수 있습니다.

select BookId, BookPrice,
sum(BookPrice) over (order by BookId) as CumSum
from CumulativeSumDemo;

윈도우 함수 방식은 코드가 간결할 뿐만 아니라, 세션 변수에 의존하지 않기 때문에 예측 가능한 결과를 보장한다는 장점이 있습니다. MySQL 5.x 버전을 사용 중이라면 위에서 소개한 변수 방식을, 8.0 이상이라면 윈도우 함수 방식을 사용하는 것이 좋습니다.