sql - 按开放金额比例分配

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，因为交易2和3偿还了50，而交易4未结金额为150)
3) 传入交易 6 应在传出交易 1 和 4 之间分配 25%/75%。(交易 1 未平仓金额为 46.25，交易 4 未平仓金额为 138.75)
4) 传入事务 8 应在传出事务 1 之间分配 20%/60%/20%； 4&7.(交易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 fiddle 链接:http://www.sqlfiddle.com/#!17/9eecb/16623
生成14行的所有组合，但不分配_amt。

目前我不确定是否可以创建SQL来生成这样的逻辑。
我尝试使用窗口函数来实现这个，但没有任何运气，因为总是需要根据之前的比例分布知道流出的未结金额。
也许可以创建递归查询来涵盖此逻辑。

Answer 1

我认为如果您创建一个历史表来存储每个 IN 事务之后的分布会更容易，这样下一个 IN 事务就可以重用计算出的值，而不是试图即时计算它们。为此，您需要一个 INSERT 触发器 - 请查看 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