以下数据表示两笔贷款的往来交易。
事务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 |
+---------+------+-----+--------+
目标是根据传入金额的事务事件时间的传出事务打开金额，按比例将传入金额分发到传出金额。每笔贷款应单独处理。
应采用以下逻辑：
贷款编号：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行的所有组合，但不分配金额。
目前我不确定是否可以创建SQL来生成这样的逻辑。
我尝试使用窗口函数来实现这一点，但没有任何运气，因为总是需要知道基于以前的比例分布的传出未结金额。
也许可以创建递归查询来覆盖这个逻辑。
最佳答案
我认为，如果您创建一个历史表来存储每个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
关于sql - 基于未清金额的比例分配，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51037008/