WITH t1 AS ( SELECT '9999/12/31' dat, '현재잔액' nam, '기타' typ, 17500 price FROM dual ) , t2 AS ( SELECT '2021/03/21' dat, '가입축하금' nam, '적립' typ, 10000 price FROM dual UNION ALL SELECT '2021/04/21', '가입축하금' , '만료', -10000 FROM dual UNION ALL SELECT '2021/04/28', '생일축하금' , '적립', 8000 FROM dual UNION ALL SELECT '2021/04/29', '지오다노' , '사용', -5000 FROM dual UNION ALL SELECT '2021/05/05', '가정의달포인트', '적립', 3000 FROM dual UNION ALL SELECT '2021/05/08', '스타벅스' , '사용', -3500 FROM dual UNION ALL SELECT '2021/05/22', '지오다노' , '취소', 5000 FROM dual UNION ALL SELECT '2021/06/08', '룰렛포인트' , '적립', 10000 FROM dual ) SELECT dat, nam, typ, price, null remain_price FROM t1 UNION ALL SELECT dat, nam, typ, price , (SELECT price FROM t1) - SUM(price) OVER(ORDER BY dat DESC) + price remain_price FROM t2 ;
다시 데이터를 살펴보니
애초에 17500 이라는 별도의 값이 필요 없어 보이네요.
순차적으로 누적시키면 자동으로 나오는 값이네요.
WITH t AS ( SELECT '2021/03/21' dat, '가입축하금' nam, '적립' typ, 10000 price FROM dual UNION ALL SELECT '2021/04/21', '가입축하금' , '만료', -10000 FROM dual UNION ALL SELECT '2021/04/28', '생일축하금' , '적립', 8000 FROM dual UNION ALL SELECT '2021/04/29', '지오다노' , '사용', -5000 FROM dual UNION ALL SELECT '2021/05/05', '가정의달포인트', '적립', 3000 FROM dual UNION ALL SELECT '2021/05/08', '스타벅스' , '사용', -3500 FROM dual UNION ALL SELECT '2021/05/22', '지오다노' , '취소', 5000 FROM dual UNION ALL SELECT '2021/06/08', '룰렛포인트' , '적립', 10000 FROM dual ) SELECT dat, nam, typ, price , SUM(price) OVER(ORDER BY dat) remain_price FROM t ORDER BY dat DESC ;