Calculation of the current balance of participants based on transactional data

Question

I have a table of financial transactions MySQL.

The table looks like this:

+----+-----------+--------+--------+---------+---------+
| id | member_id | status | amount | opening | closing |
+----+-----------+--------+--------+---------+---------+
| 1  | 2         | 1      | 1000   | 0       | 1000    |
| 2  | 2         | 2      | 100    | 1000    | 1000    |
| 3  | 2         | 1      | -20    | 1000    | 980     |
| 4  | 2         | 1      | 10     | 980     | 990     |
+----+-----------+--------+--------+---------+---------+

The open and close fields are currently empty. State 1 is committed, but 2 is not completed.

Can someone please tell me how I will write a request to scan the entire table and update all open and close balances?

There are almost 1,000,000 records in this table, so it would be nice if the query was quite optimized. Also, this is not needed for mission-critical real-time data. This will only be a current assessment of the balance of participants.

Answer 1

- reset . .

( ):

set @clo:=0, @opn:=0, @mem:=0; 
update member_txns 
set
  opening=if(status=1, @opn:=if(@mem=(@mem:=member_id), @clo, 0), @clo), 
  closing=if(status=1, @clo:=@opn+amount, @clo) 
order by member_id, id;

Answer 2

. , , . 1 arent, , ...

Answer 3

• -
• , Members

SELECT member_id, SUM(amount) as Balance GROUP BY member_id . , .

Answer 4

1M, python script

import csv
import random
ofile = open('sample.csv', "wb")
writer = csv.writer(ofile)
for i in xrange(1000000):
    row = [ i, i/(5+random.randint(0,10)), 1+random.randint(0,10)/10, random.randint(10, 200)*10, 0 ]   
    writer.writerow(row)
ofile.close()

CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `opening` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

( , , )

LOAD DATA LOCAL INFILE 'sample.csv' INTO TABLE transactions FIELDS TERMINATED BY ',';

, 0.10

SELECT SQL_NO_CACHE member_id, SUM(amount) 
FROM transactions 
WHERE member_id between 500 and 1000 AND status = 1 
GROUP BY member_id;

, ,

SELECT SQL_NO_CACHE member_id, name, SUM(amount) 
FROM transactions JOIN members ON transactions.member_id = members.id 
WHERE member_id between 500 and 1000 AND status = 1 
GROUP BY member_id;

0.17

, . , , , , .

Answer 5

- .

SQL , (), - . - - , .

, , :

• , id
• , status = 2
• 0,
• -

, : , ( 2 1 1 2)?

, :

drop procedure if exists calculate_balances;
delimiter ~
create procedure calculate_balances()
comment 'calculates running balances'
begin
declare _id int;
declare _member_id int;
declare _amount int;
declare _balance int;
declare _current_member_id int default 0;
declare _done int default 0;

declare _cursor cursor for
select id, member_id, case when status = 1 then amount else 0 end
from member_txns
order by member_id, id;

declare continue handler for not found set _done = 1;

open _cursor;

repeat
  fetch _cursor into _id, _member_id, _amount;

  if not _done then
    if _current_member_id != _member_id then
      set _balance = 0;
      set _current_member_id = _member_id;
    end if;

    update member_txns set opening = _balance, closing = _balance + _amount where id = _id;

    set _balance = _balance + _amount;
  end if;
until _done end repeat;

end;~
delimiter ;

, .

:

create table member_txns ( 
id int,
member_id int,
status int,
amount int,
opening int,
closing int
);

insert into member_txns (id, member_id, status, amount) values 
(1,2,1,1000),
(2,2,2,100),
(3,2,1,-20),
(4,2,1,10),
(5,3,1,-20),
(6,3,1,100);

call calculate_balances();

select * from member_txns;

, .

Answer 6

, ? . .

Answer 7

, , 2 1, , , ? , . , , , ?

-, , , = + (CASE WHEN Status = 1 THEN Amount Else 0 END), .

, , 10, 20, 50 100+ . , 100 , 73020. , , .

, . , , "2" "1"

, :

+--------+--------+---------+-------+
| Period | Member | Opening | Total |
+--------+--------+---------+-------+
| 1      | 1      | 0       | 50    |
| 2      | 1      | 50      | 1000  |
| 2      | 2      | 0       | 100   |
| 3      | 1      | 1050    | 0     |
| 3      | 2      | 50      | 600   |
+--------+--------+---------+-------+

, , .

, , , , . 30 000 4 " ", 1 ( , ), , , ( 10-50 ), , 10-50 .