SQL还原balance语句

Question

我想使用SQL创建一个表，该表显示了本金贷款余额如何随着时间的推移而摊销。

我有现金流量表；

- ID        Rate Date      Amount
- Loan 1    20% 01/01/2018  -100.00
- Loan 1    20% 01/02/2018  21.00
- Loan 1    20% 01/03/2018  21.00
- Loan 1    20% 01/04/2018  21.00
- Loan 1    20% 01/05/2018  21.00
- Loan 1    20% 01/06/2018  21.00

我想制作一个类似于下面的摊销时间表；

- ID        Date       Days Opening Interest Rental Closing
- Loan 1    01/02/2018  31  -100.00 -1.70   21.00   -80.70 
- Loan 1    01/03/2018  28  -80.70  -1.24   21.00   -60.94 
- Loan 1    01/04/2018  31  -60.94  -1.04   21.00   -40.97 
- Loan 1    01/05/2018  30  -40.97  -0.67   21.00   -20.65 
- Loan 1    01/06/2018  31  -20.65  -0.35   21.00   -0.00 

其中：

Interest = opening balance  * rate /365 * days
Days = number of days since last cash flow event
Closing balance = opening + Interest + Rental

我遇到的问题是将期末余额提前作为下一行的期初余额

我非常感谢在这方面的任何帮助或建议。

Answer 1

使用下面这样的order by子句

order by closingbalance desc 

Answer 2

使用lag function，您可以查看表中的前几行，因此：

SELECT
ID,
date,
...
lag(closing_balance,1)OVER(partition by ID ORDER BY date) as opening balance,
...
FROM cash_flow_table

将给您前几天的期初余额作为今天的期初余额。

Answer 3

您可以使用以下查询：

SELECT ID,
       RATE,
       DATE,
       AMOUNT,
       (Date - previous_withdrawal_date) as Days,
       opening_balance,
       opening_balance * rate /365 * (Date - previous_withdrawal_date) as Interest,
       Rental,
       (opening_balance + Interest + Rental) as Closing_Balance
  FROM (
    SELECT lag (Date) OVER (partition by ID order by date) as previous_withdrawal_date,
           NVL(lag (AMOUNT) OVER (partition by ID order by date), AMOUNT) as opening_balance,
           cf.*
      FROM cash_flow cf);

如果它按照你的要求工作，请告诉我。

谢谢，Idrees