MySql查询运行余额校正

2022-01-23 00:00:00 subquery mysql

请有人帮我做一个 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.

相关文章