Method of querying cumulative value in MySQL

Suppose there is a debit and credit record sheet , The field has borrowing , lend , And the billing date . The records in the table are as follows ：

Now I want to count one day , Or in a certain period of time , The total amount of daily loans , And the cumulative historical total , such as ,2021-10-07 The total amount of borrowing on the day is 1100,1000, The historical cumulative value is 6500,5500. That's in MySQL How to make statistics in ？ There are many ways . Here is a demonstration of , How to calculate the total amount of borrowing and lending every day and the cumulative total amount in history . If you ask for the situation of a certain day , be where The conditions are simpler .

1. Let's start with functions sum() Find out the total amount of borrowing every day , Then use the window function over() Calculate the historical cumulative value （ Window function pair MySQL The version requires ）.

select tmp.book_date, tmp.day_debit, tmp.accu_debit, tmp.day_credit, tmp.accu_credit
from(
	select t.book_date, t.day_debit, sum(t.day_debit) over(order by book_date)  as accu_debit, t.day_credit, sum(t.day_credit) over(order by book_date)  as accu_credit
	from  ( 
			SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
			FROM test 
			GROUP BY book_date
			ORDER BY book_date
	) t
	group by t.book_date 
) tmp
where tmp.book_date >= '2021-10-05' and tmp.book_date <= '2021-10-07'

 2. Let's start with functions sum() Find out the total amount of borrowing every day , Then use the temporary variable to calculate the historical cumulative value .

select tmp.book_date, tmp.day_debit, tmp.accu_debit, tmp.day_credit, tmp.accu_credit
from(
	select t.book_date, t.day_debit, @accu_debit:= @accu_debit + t.day_debit as accu_debit, t.day_credit, @accu_credit:= @accu_credit + t.day_credit as accu_credit
	from  ( 
			SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
			FROM test 
			GROUP BY book_date
			ORDER BY book_date
	) t, (select @accu_debit:=0, @accu_credit:=0) c
	group by t.book_date 
) tmp
where tmp.book_date >= '2021-10-05' and tmp.book_date <= '2021-10-07'

3. Let's start with functions sum() Find out the total amount of borrowing every day , recycling join Calculate the historical cumulative value .

SELECT a.book_date, a.day_debit, SUM(b.day_debit) AS accu_debit, a.day_credit, SUM(b.day_credit) AS accu_credit
FROM (
		SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
		FROM test 
		GROUP BY book_date
		ORDER BY book_date
) a
JOIN (
		SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
		FROM test 
		GROUP BY book_date
		ORDER BY book_date
) b ON a.book_date >= b.book_date 
WHERE a.book_date >= '2021-10-05' and a.book_date <= '2021-10-07'
GROUP BY a.book_date
ORDER BY a.book_date;