sql - SQL:跨借方(票据)有效地应用贷方(付款)

Question

我正在尝试从现成的财务系统中生成“现金滞后”报告。我可以访问数据库(Oracle)。报告应将贷方记入借方(最早的借方)，并计算生成账单与收到付款之间的天数差。所有借方和贷方都存储在金融交易表的单个列中。适用的列是:

Column Name  Type
-----------  -------------
AMOUNT       NUMERIC(15,2)   --debits are positive, credits are negative
FT_ID        CHAR(10)
ACCOUNT_ID   CHAR(10)
ACCEPTED_DT  DATETIME

因此，对于像这样的表:

ACCOUNT_ID  FT_ID  AMOUNT   ACCEPTED_DT
---------------------------------------
         1  12345  100.00   12/01/2011
         1  12346  -75.00   12/11/2011
         1  12347  100.00   12/12/2011
         1  12348  -50.00   12/16/2011

我想找回的是以下内容(编辑:已更新以显示所应用的金额。我想要实际的贷方金额，但可以根据贷方的FT ID进行查询):

ACCOUNT_ID  DEBIT_ID  DEBIT_AMOUNT  CREDIT_ID  CREDIT_AMOUNT  AGE
------------------------------------------------------------------
         1     12345         75.00      12346         -75.00   10
         1     12345         25.00      12348         -25.00   15
         1     12347         25.00      12348         -25.00    4
         1     12347         75.00      NULL            NULL  NULL

编辑:我公司选择的现成系统的优点是付款应用程序是可配置的，这意味着贷方与借方没有直接联系。我们首先将款项应用于最旧的债务，这就是我在示例中试图显示的。第一次付款将完全应用于最旧的债务。第二笔付款在最老和第二笔最老债务之间分配。这一直持续到所有积分被应用为止。

编辑2 :对不起，这似乎让我难以解释:)看示例数据，FT 12346完全适用于FT 12345，在帐户上留下了25美元的债务。然后，下一次付款也将应用于此债务，而任何剩余金额都将应用于下一个最旧的债务。正在运行的总计与我要完成的操作完全不匹配，因为我需要知道匹配的每个“片段”有多大:

编辑3 :上面的表根本不清楚，对不起。我已经更新了表格。

                 Debits                 Credits
        +-----------------------+-------------------------+
        |     FT 12345          |      FT 12346           |
        |                       |                         |
        |     $100.00           |       $75.00            |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       +-------------------------+
        |                       |      FT 12348           |
        |                       |       $50.00            |
        |                       |                         +----->$25.00 towards 12345
        +-----------------------|.........................|
        |    FT 12347           |                         |
        |                       |                         +----->$25.00 towards 12347
        |      $100.00          +-------------------------+
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        +-----------------------+-------------------------+

我们有一个使用游标的解决方案，但是在整个金融交易表(约5000万行)中，这非常慢。我想知道是否有一种方法可以根据纯表来重述该问题以加快速度。我发现 a recipe for genreating a running balance似乎是一个开始，但我不确定从那里去哪里。

Answer 1

如果正确理解了您想要的内容，则可以通过分析来做到这一点:

create table cred_deb (account_id integer, transaction_date date, amount number);


insert into cred_deb values (1, sysdate - 10, 100.00); -- bill of 100
insert into cred_deb values (1, sysdate - 9, -10.00); -- payment of 10
insert into cred_deb values (1, sysdate - 8, -80.00); -- payment of 80
insert into cred_deb values (1, sysdate - 5, 80.00); -- bill of 80
insert into cred_deb values(1, sysdate - 3, -80.00); -- payment of 80

-- 2nd account
insert into cred_deb values(2, sysdate - 3, 80.00); -- bill of 80
insert into cred_deb values(2, sysdate - 3, -80.00); -- payment of 80



select account_id, 
       transaction_date,
       amount,
       sum(amount) over( partition by account_id order by transaction_date) running_total
from cred_deb
order by account_id, transaction_date;

ACCOUNT_ID             TRANSACTION_DATE          AMOUNT                 RUNNING_TOTAL          
---------------------- ------------------------- ---------------------- ---------------------- 
1                      06-DEC-11                 100                    100                    
1                      07-DEC-11                 -10                    90                     
1                      08-DEC-11                 -80                    10                     
1                      11-DEC-11                 80                     90                     
1                      13-DEC-11                 -80                    10                     
2                      13-DEC-11                 80                     80                     
2                      13-DEC-11                 -80                    0                     

仔细研究一下您的示例，也许您想将特定账单的所有贷方以及账单金额分组到一行。如果您可以更清楚地说明您所从事的工作，我认为分析将能够解决。

编辑-以天为单位增加年龄。

select account_id, 
       transaction_date,
       amount,
       sum(amount) over( partition by account_id order by transaction_date) running_total,
       trunc(transaction_date) - max(case 
                                      when amount > 0 then 
                                        trunc(transaction_date)
                                      else
                                        null
                                      end) over (partition by account_id order by transaction_date)  age_in_days
from cred_deb
order by account_id, transaction_date