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
- member_id : 회원번호
- order_amount : 주문금액
- 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 |
'Programming > SQL' 카테고리의 다른 글
SQL 연습 사이트 추천 (듀토리얼, 문제) (8) | 2021.05.30 |
---|---|
[MySQL] 생년월일 데이터 다루기 > 연령대별 변수 생성 (1) | 2021.05.24 |
[MySQL] 날짜별 순서 예제 (0) | 2020.11.11 |
[MySQL] 코딩테스트 연습 예제 2 (1) | 2020.11.07 |
[MySQL] 코딩테스트 연습 예제 1 (1) | 2020.09.07 |
댓글