[MySQL] 그룹별 누적 합계 구하기

SQL 쿼리를 활용하여 DB에 있는 데이터를 추출하다보면, 그룹별로 누적 합계를 계산해야 할 때가 있습니다.

최근, SQL 관련 업무를 진행하다 앞서 말한 상황이 발생해서 문제를 해결하기 위해 여러 사이트를 참고하여 포스팅을 해보겠습니다.

 

※ Oracle은 PARTITION BY / ROW_NUMBER OVER() 등 함수를 통하여 이러한 기능을 쉽게 구현할 수 있습니다.

 

하지만, 낮은 버전의 MySQL에는 이러한 기능이 부족하여 변수 설정을 통해 누적 합계를 구해야 합니다.

(최신 버전, 8.0 이상에서는 앞서 말씀드린 함수가 사용 가능하게 업데이트 된 것으로 알고 있습니다.)

 

개인적으로 공부할SQL 쿼리를 활용하여 DB에 있는 데이터를 추출하다보면, 그룹별로 누적 합계를 계산해야 할 때가 있습니다.

 

최근, SQL 관련 업무를 진행하다 앞서 말한 상황이 발생해서 문제를 해결하기 위해 여러 사이트를 참고하여 포스팅을 해보겠습니다.

 

개인적으로 공부할 때 MySQL을 깔아서 사용하기보다는 SQL test를 할 수 있는 사이트를 자주 애용합니다.

아래의 SQL Fiddle은 직접 데이터 테이블을 만들어서 연습할 수 있기에 아래에 올려두겠습니다. 

SQL Test Site :  http://sqlfiddle.com

---

▷ 데이터 테이블 예시

Test_table

1. member_id : 회원번호
2. order_amount : 주문금액
3. order_time : 주문시간

CREATE TABLE Test_table
    (`member_id` int, `order_amount` int, `order_time` datetime)
;
    
INSERT INTO Test_table
    (`member_id`, `order_amount`, `order_time`)
VALUES
    (302, 23000, '2020-04-11 12:54:11'),
    (201, 24300, '2021-05-10 03:32:31'),
    (302, 20000, '2020-01-17 11:24:30'),
    (302, 32000, '2020-06-17 05:00:00'),
    (302, 47000, '2020-05-17 03:00:00'),
    (201, 44300, '2019-05-10 04:00:00'),
    (201, 53300, '2020-09-10 06:00:00'),
    (247, 20000, '2021-08-11 01:00:00'),
    (547, 40000, '2021-08-11 02:00:02'),
    (247, 30000, '2021-08-19 05:30:10'),
    (247, 50000, '2020-06-11 11:00:00'),
    (547, 23000, '2021-11-11 08:00:00'),
    (547, 43000, '2021-11-15 05:00:00'),
    (330, 45000, '2020-10-08 04:00:00'),
    (330, 55000, '2020-11-08 03:20:01'),
    (330, 65000, '2020-09-07 00:00:00')
;

반응형

---

문제1 : 전체 회원의 날짜별 누적 주문금액을 구해라. (단, 결과는 날짜순 그리고 날짜는 연도 및 월로 표시할 것)

<MySQL Query>

SET @va = 0;
SELECT a.order_date,
       a.sum_amount,
       (@va := @va + a.sum_amount) as cumsum_amount
FROM ( SELECT date_format(order_time, '%Y-%m') order_date, 
      sum(order_amount) sum_amount
FROM Test_table GROUP BY order_date ORDER BY order_date ) a;

<Query 설명>

날짜가 지남에 따라 누적 주문금액을 만들기 위해 @va 변수를 설정해줍니다. 

추출 기반 테이블에 서브쿼리로 필요한 데이터 테이블을 만들어줍니다. date_format으로 날짜를 연도 및 월로 표시해줍니다. 그리고 날짜별 주문금액을 합산하고 날짜순으로 정렬시킨 a라는 데이터 테이블을 만들어줍니다.

이 서브쿼리 테이블에서 연도 및 월별로 계속해서 누적되는 주문 금액 (@va := @va + a.sum_amount)을 만들어줍니다.

 

<Query 결과>

order_date	sum_amount	cumsum_amount
2019-05	44300	44300
2020-01	20000	64300
2020-04	23000	87300
2020-05	47000	134300
2020-06	82000	216300
2020-09	118300	334600
2020-10	45000	379600
2020-11	55000	434600
2021-05	24300	458900
2021-08	90000	548900
2021-11	66000	614900

---

문제2 : 회원들의 날짜별 누적 주문금액을 나타내라. (단, 결과는 회원번호순으로 나타내고 날짜는 연도 및 월로 표시)

<MySQL Query>

SELECT member_id, order_amount, order_cumsum, date_format(order_time, '%Y-%m') order_date
FROM ( SELECT a.*, (CASE @vid WHEN a.member_id THEN @rownum := @rownum + order_amount 
                    ELSE @rownum := order_amount END) order_cumsum,
      (@vid := a.member_id) vid 
      FROM Test_table a, (SELECT @vid :='',@rownum := 0 FROM DUAL) b 
      ORDER BY a.member_id,a.order_time DESC ) c;

 

<Query 설명>

회원들의 날짜별 누적 주문금액이므로 크게 회원별 그리고 작게 날짜별로 그룹핑해주면서 주문금액을 누적해주어야 합니다. 

사실, 이 문제에 대한 쿼리는 제가 예전에 포스팅했던 [MySQL] 그룹별 순위 매기기 구현하기 를 참고하시면 됩니다.

해당 포스팅에서 설명했던 쿼리 방식을 그대로 가져왔으므로 변수명과 테이블만 바뀌었을 뿐 형식은 똑같습니다.

 

<Query 결과>

member_id	order_amount	order_cumsum	order_date
201	24300	24300	2021-05
201	53300	77600	2020-09
201	44300	121900	2019-05
247	30000	30000	2021-08
247	20000	50000	2021-08
247	50000	100000	2020-06
302	32000	32000	2020-06
302	47000	79000	2020-05
302	23000	102000	2020-04
302	20000	122000	2020-01
330	55000	55000	2020-11
330	45000	100000	2020-10
330	65000	165000	2020-09
547	43000	43000	2021-11
547	23000	66000	2021-11
547	40000	106000	2021-08