基于事务数据计算当前成员余额

ajreal的解决方案存在一个疏忽——它没有为不同的成员id重置balance变量。这个版本修复了这个问题。

这个有效(我测试过):

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;

你真的不想用这种方式实现它。 你不知道当你需要推迟交易日期的时候,你会发现自己会受到伤害。1M的记录没有那么多,所以索引应该是足够的…

1. 在这里存储余额是非规范化的,不需要
2. 如果您真的想存储余额,它应该在Members表中

简单的 SELECT member_id, SUM(amount) as Balance GROUP BY member_id 会给你结果的。如果你真的有一个性能问题,你总是可以转置一个 nice trick by Allen Browne 对你的处境。

这是对形势的分析

首先,我使用小python脚本创建了100万条记录

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秒

所以,我无法复制这个问题。因此,我建议将上述查询作为解决方案,或者请求更多信息来尝试复制问题。

为什么不从存储过程创建物化视图?它将分离数据和计算值之间的逻辑。它还将提供尽可能快的查询。

解决方案需要一个程序-我将通过存储过程显示它。

SQL在查询时尤其不擅长依赖于基于列的表中行的顺序,这些列的值是未知的,比如您的id列。处理这种情况的唯一方法是使用某种扫描数据的可执行代码。

虽然你还没说,但我假设逻辑是:

1. 按成员和id排序的进程行
2. 忽略状态为2的行
3. 如果是为成员找到的第一行,则将opening设置为0
4. 将结算更新为期初加上金额

注意,这里有一个明显的问题:如果状态发生变化(从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;

执行这些命令将显示存储过程产生正确的结果。

当状态从2变为1时,您不会说明会发生什么,是否所有跟随它的数据都需要相应地更新打开和关闭值?如果不是,那么你的系统有严重的问题。如果是这样的话,你将需要重新计算所有这些数据很多次,这就是为什么你要问这个问题的原因吗?

首先,没有理由同时存储所有记录的开始值和结束值, 关闭=打开+(如果状态为1,则金额为0结束),这将是所需计算的一半。

接下来,也许您最好只存储10、20、50或100+条记录作为关键点。因此,您只对100个ID值的括号进行了总结,总共有73020个ID值。如果您真的希望这样做,可以将其更改为还包括汇总级别上所有以前值的总运行总数。

通常,金融系统只会逐月存储期初值和期末值,并在该月内重新计算这些值。想想你的用户实际上需要这个数据,相比于当状态“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   |
+--------+--------+---------+-------+

等等,这是一个更好的数据模式,而且维护起来要容易得多。

要更新这些值,只需确定需要更新的期间,然后重新计算该期间的总计,然后只需更新所有后续期初值。即使有30000个不同的会员,你也需要4年的时间才能达到100万记录(如果你使用每月的周期),在这段时间里,我预计你的交易量将远远超过(10-50倍的数量),因此,这样做的结果是工作量减少了10-50倍,对最终用户性能的影响非常小。