基于开盘量的比例分布

Question

以下数据表示两笔贷款的传入和传出交易。事务ID (t_id)表示事务事件发生的顺序。

+---------+------+-----+--------+
| loan_id | t_id | amt | t_type |
+---------+------+-----+--------+
|       1 |    1 | 100 | OUT    |
|       1 |    2 |  20 | IN     |
|       1 |    3 |  30 | IN     |
|       1 |    4 | 150 | OUT    |
|       1 |    5 |  15 | IN     |
|       1 |    6 |  25 | IN     |
|       1 |    7 |  40 | OUT    |
|       1 |    8 | 200 | IN     |
|       2 |    1 | 150 | OUT    |
|       2 |    2 |  50 | OUT    |
|       2 |    3 | 120 | IN     |
|       2 |    4 |  20 | OUT    |
|       2 |    5 | 100 | IN     |
+---------+------+-----+--------+

目标是根据传入金额的交易事件时间的传出交易打开金额，将传入金额按比例分配到传出金额。每笔贷款都应该单独处理。

以下逻辑应适用：

loan_id: 1

1)传入事务处理2和3应100%分配给传出事务处理1，因为它是当时唯一的传出事务处理。

2)传入事务处理5应在传出事务处理1和4之间分配25%/75%。(事务处理1未结金额为50，因为50由事务处理2和3偿还，事务处理4未结金额为150)

3)传入事务处理6应在传出事务处理1和4之间分配25%/75%。(事务处理1未结金额为46.25%，事务处理4未结金额为138.75)

4)传入事务处理8应在传出事务处理1、4和7之间分配20%/60%/20%。(事务处理1；4和7未结金额分别为40、120和40 )

我们可以假设总入库量和运行总入库量总是小于或等于总的开放出库量。

这是最终结果应该是什么样子：

+---------+---------+----------+---------------+
| loan_id | in_t_id | out_t_id | allocated_amt |
+---------+---------+----------+---------------+
|       1 |       2 |        1 | 20            |
|       1 |       3 |        1 | 30            |
|       1 |       5 |        1 | 3.75          |
|       1 |       5 |        4 | 11.25         |
|       1 |       6 |        1 | 6.25          |
|       1 |       6 |        4 | 18.75         |
|       1 |       8 |        1 | 40            |
|       1 |       8 |        4 | 120           |
|       1 |       8 |        7 | 40            |
|       2 |       3 |        1 | 90            |
|       2 |       3 |        2 | 30            |
|       2 |       5 |        1 | 60            |
|       2 |       5 |        2 | 20            |
|       2 |       5 |        4 | 20            |
+---------+---------+----------+---------------+

链接到SQL：http://www.sqlfiddle.com/#!17/9eecb/16623

生成14行的所有组合，但不生成allocated_amt。

目前，我不确定是否可以创建SQL来生成这样的逻辑。

我试图使用窗口函数来实现这一点，但没有任何幸运，因为总是需要知道基于先前比例分布的流出开头量。

也许可以创建递归查询来覆盖这一逻辑。

Answer 1

我认为如果您创建一个历史表来存储每个IN事务之后的分布，这样下一个IN事务就可以重用计算的值，而不是试图动态地计算它们，这会更容易。为此，您需要一个插入触发器-请查看SQLfiddle

-- INSERT INTO distribution(loan_id,t_in,t_out,amount)
-- we compute the distributions for the current IN transaction and store them in table DISTRIBUTIONS 
-- to be used by the next IN transaction
SELECT current.loan_id,6 AS t_in,current.t_id AS t_out,25 * (current.amt - paid) / (total_due - total_paid) AS distributed
FROM 
-- first we get the amount paid for each existing OUT transaction
(SELECT t.loan_id,t.t_id,t.amt,SUM(d.amount) AS paid
FROM transactions AS t
LEFT JOIN distribution AS d ON t.loan_id = d.loan_id AND t.t_id = d.t_out
WHERE t_type = 'OUT' AND t.t_id < 6 -- only OUT transactions before the current IN transaction
GROUP BY t.loan_id,t.t_id,t.amt) AS current
LEFT JOIN
-- next we get the total amount due for the given loan
(SELECT loan_id,SUM(amt) AS total_due
FROM transactions
WHERE t_type = 'OUT' AND t_id < 6 -- only OUT transactions before the current IN transaction
GROUP BY loan_id) AS t_due                                                                   
ON current.loan_id = t_due.loan_id
LEFT JOIN
-- next we get the total amount paid for the given loan
(SELECT loan_id,COALESCE(SUM(amount),0) AS total_paid
FROM distribution AS d
WHERE t_out < 6 -- only OUT transactions before the current IN transaction
GROUP BY loan_id) AS t_paid
ON current.loan_id = t_paid.loan_id
WHERE current.loan_id = 1 -- the loan ID of the current IN transaction