MySql Statement History with user balance algorithm-CodePudding

I have a table with payment history

payments:

id	consumer_id	amount	created_at
1	1	30	2021-05-11 13:01:36
2	1	-10	2021-05-12 14:01:36
3	1	-2.50	2021-05-13 13:01:36
4	1	-4.50	2021-05-14 13:01:36
5	1	20	2021-05-15 13:01:36

In final result need to get consumer balance after each transaction. So something like this

id	consumer_id	amount	created_at	balance
1	1	30	2021-05-11 13:01:36	30.00
2	1	-10	2021-05-12 14:01:36	20.00
3	1	-2.50	2021-05-13 13:01:36	17.50
4	1	-4.50	2021-05-14 13:01:36	13.00
5	1	20	2021-05-15 13:01:36	33.00

I using this query

SET @balanceTotal = 0;
select amount, created_at, consumer_id, @balanceTotal := @balanceTotal   amount as balance
from payments
where consumer_id = 1

This works fine until I try to add some sorting or pagination.

Any suggestion on how to write a query with order by desc, limit, and offset to count balance properly?

CodePudding user response：

That's just a window sum. In MySQL 8.0:

select p.*, 
    sum(amount) over(partition by consumer_id order by created_at) balance
from payments p

You can add the filtering on the customer in the where clause if you like (in which case the partition by clause is not really needed anymore).

In earlier versions of MySQL, an alternative uses a correlated subquery:

select p.*, 
    (
        select sum(amount) 
        from payments p1 
        where p1.consumer_id = p.consumer_id and p1.created_at <= p.created_at
    ) balance
from payments p

I would not recommend user variables for this; although efficient, their behavior is quite tricky, and their use is deprecated in recent. versions.

CodePudding user response：

If using MySQL >= 8 using a window sum is preferable -

select p.*, sum(amount) over(order by created_at) balance
from payments p
where consumer_id = 1
order by created_at desc
limit 0, 5;

If you are using MySQL < 8 then using a user variable for this is significantly more efficient than using the suggested correlated subquery. You can have it as a derived table for re-ordering and pagination -

select * from (
    select p.*, @balanceTotal := @balanceTotal   amount as balance
    from payments p, (SELECT @balanceTotal := 0) vars
    where consumer_id = 1
    order by created_at
) t
order by created_at desc
limit 0, 5;