[MySQL] 까다롭디 까다로운 잔액 , 잔고 테이블 쿼리 구성

처음 잔액, 잔고 테이블을 구성할 때 누적 잔액을 어떻게 구현해야할지 고민을 많이 했었다.

 

1. DB에 누적 잔액 컬럼을 만들어서 매번 누적잔액을 넣어야하나?

 

2. 아니면 SQL문으로 그때그때의 실시간 누적잔액을 계산해야하나?

 

 

 

 

1번은 컬럼에 저장해버리면 어느 시점부터의 누적 잔액을 조회하고 싶을 때 조회 하기가 힘들다.

 

2번으로 구현하려니 쿼리를 짜는 게 여간 복잡한 게 아니었다.

 

 

 

 

 

하지만 유동적이고 유연한? 조회를 위해 1번이 아닌 2번으로 쿼리를 짜기로 했다...

 

 

잔액 테이블(balance)
날짜(balance_date)	변동금액(change_balance)	잔액테이블 기본키(PK)(balance_id)
2022-04-21	6000	1
2022-04-22	-2000	2
2022-04-18	2000	3

 

잔액 테이블이다. 추가, 차감을 구분하는 상태 컬럼을 만들어서 변동금액에는 양수만 들어가는 테이블을 구성할 수도

있고

입금,출금 테이블을 따로 하여 연산하고 JOIN하는 방법도 있겠으나..

 

이건 쿼리가 더 복잡해져서 나중에 좀 더 생각해봐야겠다.

 

MySql 8 기준

SELECT
balance_date AS 날짜, 
change_balance AS 변동금액,

# SUM() OVER(ORDER BY 더할 순서가 될 기준 컬럼)
SUM(change_balance) OVER(ORDER BY balance_date) AS 누적잔고


FROM balance;

 

SUM() OVER()함수를 사용한다

 

SUM(계산할 컬럼) 

 

OVER()는 누적을 보여주는 함수이다.

 

OVER() 안의 ORDER BY는 정렬될 컬럼을 설정해준다. 이 컬럼 순서로 계산할 컬럼이 누적되면서 표현된다.

 

OVER(ORDER BY balance_date)

 

그런데??? 문제가 있다.

 

날짜(balance_date)가 겹칠때는 변동금액이 달라도 누적이 하나하나 변하는 게 보이는 게 아니라 한꺼번에 보이게 된다.

 

말이 어렵다면 결과를 보자

 

날짜가 중복된 값, 똑같은 값이 있으면 아래 결과 처럼 보이게 된다.

 

 

날짜가 겹칠때는 변동금액이 달라도 누적이 하나하나 변하는 게 보이는 게 아니라 한꺼번에 계산된 값으로 보이게 된다.

 

 

이 문제를 해결하기 위해 몇 시간을 썼는지 모르겠다...

 

ORDER BY 값 뒤에 값을 하나 추가해주면 간단하게 해결 되었다

 

SUM() OVER(ORDER BY 날짜, PK값)

 

그 말은 즉슨~

 

SELECT
balance_date AS 날짜, 
change_balance AS 변동금액,

# SUM() OVER(ORDER BY 더할 순서가 될 기준 컬럼, 값이 겹칠때 기준이 될 컬럼)
SUM(change_balance) OVER(ORDER BY balance_date, balance_id) AS 누적잔고


FROM balance;

 

결과

 

정확히 왜 작동하는지 모르겠으나

 

나는 값이 겹칠 때 다음으로 기준이 될 컬럼이라고 생각했다. 아직 많은 실험이 필요하다.

PK값은 고유한 값을 가지니 값이 겹칠 일이 없어 구분하기에 좋다.

 

LEAD함수 LAG함수 등 다양한 시도를 했으나 일단 잔고는 이런 방법으로 구현하였다.

 

 

 

최신 잔고가 위로 오기 위해서는 또 복잡한 로직을 거치는데 단순히 

 

마지막에 ORDER BY 날짜로 하면 아까의 중복된 날짜 부분이 다시 제멋대로 들어가 버린다.

 

 

조회된 결과를 그대로 뒤집어야 하는데 아무리 검색을 해봐도 

 

결과가 나온 이후에 뒤집는 것은 보이지가 않았다.

 

임시 인덱스를 생성해서 인덱스를 기준으로 뒤집는 방법을 생각했으나

 

Oracle에서는 기본 함수로 ROWNUM을 생성해주는 기능이 있으나

 

MySql에서는 네이티브로 임의 인덱스를 생성하는 것을 지원하지 않아 변수를 선언하여 진행해주어야 했다.

 

 

 SELECT
@ROWNUM:= @ROWNUM + 1 AS 인덱스,
balance_date AS 날짜, change_balance AS 변동금액,
SUM(change_balance) OVER(ORDER BY balance_date, balance_Id) AS 누적잔고
FROM balance,(SELECT @ROWNUM:=0) r

그렇게 @ROWNUM을 생성해서 진행해주려고 했으나...

 

인덱스가 순서대로 나오는 게 아니라

 

SUM()함수가 자꾸 인덱스 번호를 통일시켜 버리는 것 같았다.

 

정말 또 한참을 고민하던 중에 결국

 

FROM으로 조회된 결과를 가져와서 뒤집자고 생각해서 복잡한 쿼리를 진행하였다.

 

SELECT
@ROWNUM:= @ROWNUM + 1 AS 인덱스,
b.*
FROM(
SELECT

balance_date AS 날짜, change_balance AS 변동금액,
SUM(change_balance) OVER(ORDER BY balance_date, balance_Id) AS 누적잔고
FROM balance,(SELECT @ROWNUM:=0) r) b;

FROM 안에 결과를 넣자고 생각해서 짜보았다.

 

오 드디어 인덱스가 잘 나온다.

 

이제 인덱스를 기준으로 뒤집어보자

 

 

SELECT
@ROWNUM:= @ROWNUM + 1 AS 인덱스,
b.*
FROM(
SELECT

balance_date AS 날짜, change_balance AS 변동금액,
SUM(change_balance) OVER(ORDER BY balance_date, balance_Id) AS 누적잔고
FROM balance,(SELECT @ROWNUM:=0) r) b
ORDER BY 인덱스 desc;

 

 

드디어 조회된 결과를 그대로 뒤집는데에 성공했다.

 

 

이게 효율적인 방법인지 모르겠으나

 

조회된 결과를 결과가 나온 이후에 그대로 뒤집는 방법을 생각해내고 구현했다는 것에 기뻤다.

 

아무리 검색해도 출력할 때 정렬해서 나오는 내용 밖에 나오지 않았기 때문이다...

 

좀 더 공부해서 잘 활용해봐야겠다