MySql查询运行余额校正 [英] MySql Query for Running Balance Correction
问题描述
请有人帮我做一个 MySql 查询以进行运行平衡校正.
Please someone help me to make a MySql query for running balance correction.
实际上，我们有很多来自其他应用程序的脏数据.有时我们在正常运行余额计算中得到负余额.
Actually We have very much dirty data that came from other application. Sometimes we got negative balance for normal running balance calculation.
来自之前的问题我得到了答案，但我想要贷方交易仅在正常余额计算为负时首先计算，因此如果余额不为负，则同一日期内的借记和贷记顺序不应改变
From the question before I got an answer but I want credit transaction only calculating first if the normal balance calculation is negative so if the balance does not negative then the order of debit and credit within the same date should not change
这是表格示例:
trx_no trx_date Opening debit credit
1 2019-10-01 200 0 100
2 2019-10-02 200 0 100
3 2019-10-03 200 100 0
4 2019-10-03 200 400 0
5 2019-10-03 200 0 200
6 2019-10-04 200 0 100
7 2019-10-05 200 100 0
8 2019-10-05 200 0 400
对于正常余额计算，我们得到负余额:
For normal balance calculation we got negative balance:
trx_no trx_date Opening debit credit balance
1 2019-10-01 200 0 100 300
2 2019-10-02 200 0 100 400
3 2019-10-03 200 100 0 300
4 2019-10-03 200 400 0 -100 <- negative balance
5 2019-10-03 200 0 200 100
6 2019-10-04 200 0 100 200
7 2019-10-05 200 100 0 100
8 2019-10-05 200 0 400 500
这是我从@forpas 得到的查询(非常感谢):
Here is the query I got from @forpas (many thanks) :
SELECT
t1.trx_no,
t1.trx_date,
t1.Opening,
t1.debit,
t1.credit,
t1.Opening + (
SELECT SUM(t2.credit - t2.debit)
FROM MyTable t2
WHERE
concat(t2.trx_date, t2.debit > t2.credit, lpad(t2.trx_no, 10, '0')) <=
concat(t1.trx_date, t1.debit > t1.credit, lpad(t1.trx_no, 10, '0'))
) AS balance
FROM MyTable t1
ORDER BY concat(t1.trx_date, t1.debit > t1.credit, lpad(t1.trx_no, 10, '0'))
以下是上述查询的结果，没有负余额，但 trx_no 8 在 trx_no 7 之前排序:
Below is the result for above query, no negative balance but trx_no 8 ordered before trx_no 7:
trx_no trx_date Opening debit credit balance
1 2019-10-01 200 0 100 300
2 2019-10-02 200 0 100 400
5 2019-10-03 200 0 200 600
3 2019-10-03 200 100 0 500
4 2019-10-03 200 400 0 100
6 2019-10-04 200 0 100 200
8 2019-10-05 200 0 400 600
7 2019-10-05 200 100 0 500
我们需要的结果如下.仅在余额为负数时才首先计算信用交易.因此，如果余额不是负数，则订单不应更改:
The result what we need is below. Credit transaction only calculating first if the balance is negative. So if the balance not negative the order should not changed:
trx_no trx_date Opening debit credit balance
1 2019-10-01 200 0 100 300
2 2019-10-02 200 0 100 400
5 2019-10-03 200 0 200 600
3 2019-10-03 200 100 0 500
4 2019-10-03 200 400 0 100
6 2019-10-04 200 0 100 200
7 2019-10-05 200 100 0 100
8 2019-10-05 200 0 400 500
这里是演示
Here is the DEMO
推荐答案
我认为这不能使用基于集合的代码来完成，因为您需要预读和回读.一些程序可以工作.使用游标和临时表我几乎可以做到这一点(您需要添加一些东西以确保累积余额不会变为负数).从你所说的第一笔交易永远不会使余额为负，但我会检查一下.请注意，从贷方池中获取的款项可能来自于借方补偿的借方的更晚日期——因为余额永远不会变为负数，所以这不应该是一个问题(原文如此).当然，我不会期望性能会很棒.
I don't think this can be done using set based code since you need read ahead and read back. Something procedural could work. Using a cursor and a temporary table I can pretty much accomplish that (you need to add stuff to ensure that cumulative balance does not go negative). From what you say the first transaction can never take the balance negative but I would check for that. Note well the takes from the credit pool may come from a later date than the debit the take is compensating for - since the balance can never go negative then that should not be a problem (sic). And of course I would not expect the performance to be great.
DROP TABLE IF EXISTS T;
create table t
(trx_no int, trx_date date, Opening int,debit int, credit int);
insert into t values
(1 , '2019-10-01' , 200 , 0 , 100),
(2 , '2019-10-02' , 200 , 0 , 100),
(3 , '2019-10-03' , 200 , 100 , 0 ),
(4 , '2019-10-03' , 200 , 400 , 0 ),
(5 , '2019-10-03' , 200 , 0 , 200),
(6 , '2019-10-04' , 200 , 0 , 100),
(7 , '2019-10-05' , 200 , 100 , 0 ),
(8 , '2019-10-05' , 200 , 0 , 400);
DROP TABLE IF EXISTS stmT;
create table stmt
(trx_no int, trx_date date, Opening int,debit int, credit int, cumbal int);
drop procedure if exists p;
delimiter $$
create procedure p()
begin
declare v_trx_no int;
declare v_dt date;
declare v_debit int;
declare v_credit int;
declare h_trx_no int;
declare h_dt date;
declare h_credit int;
declare opbal int;
declare rbal int;
declare done int default 0;
declare cur cursor for select trx_no,trx_date,debit,credit from t order by trx_date,trx_no;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
select opening into opbal from t order by trx_no limit 1;
set rbal = opbal;
drop table if exists creditpool;
create temporary table if not exists creditpool as select trx_no,trx_date,credit,0 as used from t where credit > 0;
#select * from creditpool;
open cur;
cursorloop:loop
fetch cur into v_trx_no,v_dt,v_debit,v_credit;
if done = true then
leave cursorloop;
end if;
if v_credit > 0 and exists (select 1 from creditpool where trx_no = v_trx_no and used = 0) then
if v_credit > 0 then
insert into stmt values (v_trx_no,v_dt,opbal,v_debit,v_credit, rbal + v_credit);
set rbal = rbal + v_credit;
update creditpool set used = 1 where trx_no = v_trx_no;
end if;
end if;
if v_debit > 0 and v_debit <= rbal then
insert into stmt values (v_trx_no,v_dt,opbal,v_debit,v_credit, rbal - v_debit);
set rbal = rbal - v_debit;
elseif v_debit > 0 and v_debit > rbal then
poolloop: loop
select trx_no,trx_date,credit
into h_trx_no,h_dt,h_credit
from creditpool
where used = 0
order by trx_date,trx_no limit 1;
insert into stmt values (h_trx_no,h_dt,opbal,0,h_credit, rbal + h_credit);
set rbal = rbal + h_credit;
update creditpool set used = 1 where trx_no = h_trx_no;
if v_debit <= rbal then
insert into stmt values (v_trx_no,v_dt,opbal,v_debit,v_credit, rbal - v_debit);
set rbal = rbal - v_debit;
leave poolloop;
end if;
end loop poolloop;
end if;
end loop cursorloop;
close cur;
end $$
delimiter ;
truncate stmt;
call p();
select * from stmt;
+--------+------------+---------+-------+--------+--------+
| trx_no | trx_date | Opening | debit | credit | cumbal |
+--------+------------+---------+-------+--------+--------+
| 1 | 2019-10-01 | 200 | 0 | 100 | 300 |
| 2 | 2019-10-02 | 200 | 0 | 100 | 400 |
| 3 | 2019-10-03 | 200 | 100 | 0 | 300 |
| 5 | 2019-10-03 | 200 | 0 | 200 | 500 |
| 4 | 2019-10-03 | 200 | 400 | 0 | 100 |
| 6 | 2019-10-04 | 200 | 0 | 100 | 200 |
| 7 | 2019-10-05 | 200 | 100 | 0 | 100 |
| 8 | 2019-10-05 | 200 | 0 | 400 | 500 |
+--------+------------+---------+-------+--------+--------+
8 rows in set (0.00 sec)
请注意，结果与您想要的结果略有不同，因为仅在需要时才会进行拍摄.
Note the outcome is slightly different to your desired result because a take only occurs when required.
这篇关于MySql查询运行余额校正的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
