具有自引用條件的滾動總和聚合
我有一個我認為是遞歸 CTE 的案例,但我還沒有弄清楚如何建構它。要求是在攤銷時間表上創建滾動總和,但付款不直接與時間表中的付款相關聯。首先,我們有一個計劃對象:
CREATE TABLE sch AS SELECT ctr_id::int , mth::date , pmt_amt::numeric FROM ( VALUES ( 1 , '2019-01-01' , 145.0 ) , ( 1 , '2019-02-01' , 145.0 ) , ( 1 , '2019-03-01' , 145.0 ) , ( 1 , '2019-04-01' , 145.0 ) , ( 1 , '2019-05-01' , 145.0 ) , ( 1 , '2019-06-01' , 145.0 ) ) AS sch ( ctr_id , mth , pmt_amt ) ;
此表跟踪給定契約 (ctr_id) 的攤銷時間表。有一個單獨的付款對象跟踪對契約的每筆付款:
CREATE TABLE pmt AS SELECT ctr_id::int , dt::date , amt::numeric FROM ( VALUES ( 1 , '2019-01-04' , 145.0 ) , ( 1 , '2019-02-01' , 145.0 ) , ( 1 , '2019-03-01' , 145.0 ) , ( 1 , '2019-03-29' , 145.0 ) , ( 1 , '2019-05-03' , 145.0 ) , ( 1 , '2019-06-07' , 145.0 ) ) AS sch ( ctr_id , dt , amt ) ;
從整體上看這些數據,很容易將 3-29 付款辨識為針對 4 月付款。但是,我在實現同時考慮這種情況和下一個情況的邏輯時遇到了麻煩:
SELECT ctr_id::int , dt::date , amt::numeric FROM ( VALUES ( 1 , '2019-01-01' , 145.0 ) , ( 1 , '2019-05-01' , 435.0 ) , ( 1 , '2019-05-03' , 145.0 ) , ( 1 , '2019-06-01' , 145.0 ) ) AS sch ( ctr_id , dt , amt ) ;
在這種情況下,很明顯 5 月的第一筆付款是補繳款,而第二筆付款是針對 5 月本身的付款。我開始使用的分析 SQL:
SELECT sch.mth , sch.ctr_id , SUM( sch.pmt_amt - pmt.pmts ) OVER ( PARTITION BY sch.ctr_id ) AS outstanding FROM sch LEFT JOIN ( SELECT ctr_id , DATE_TRUNC( 'month' , dt ) AS mth , SUM( amt ) OVER ( PARTITION BY ctr_id ) AS pmts , COUNT( * ) AS pmt_cnt FROM pmt GROUP BY ctr_id , DATE_TRUNC( 'month' , dt ) ) AS pmt ON pmt.ctr_id = sch.ctr_id AND pmt.mth = sch.mth
為了解決第一種情況,如果上個月有超過 1 筆付款,並且兩次付款的總和大於預定金額,則將超出的金額應用到下個月。對於第二種情況,我們還必須考慮上期的未償金額。在偽 SQL 中:
SELECT sch.mth , sch.ctr_id , CASE WHEN LAG(pmt.pmt_cnt) OVER (ctr) > 1 AND pmt.pmts > sch.pmt_amt THEN pmt.pmts - sch.pmt_amt ELSE 0 END AS carryover , SUM( CASE WHEN LAG(outstanding) OVER (ctr) > 0 THEN ( LAG(outstanding) OVER (ctr) + sch.pmt_amt ) - pmt.pmts ELSE sch.pmt_amt - pmt.pmts - LAG(carryover) OVER (ctr) ) OVER ( ctr ) AS outstanding FROM sch LEFT JOIN ( SELECT ctr_id , DATE_TRUNC( 'month' , dt ) AS mth , SUM( amt ) OVER ( PARTITION BY ctr_id ) AS pmts , COUNT( * ) AS pmt_cnt FROM pmt GROUP BY ctr_id , DATE_TRUNC( 'month' , dt ) ) AS pmt ON pmt.ctr_id = sch.ctr_id AND pmt.mth = sch.mth WINDOW ctr AS ( PARTITION BY sch.ctr_id ORDER BY sch.mth )
視窗函式方法的主要問題是您需要返回同一視窗函式的先前值,但根據返回值計算目前值的方式不同。任何想法如何解決?遞歸 CTE 能解決它嗎?
編輯:感謝 Lennart 指出錯過的案例。我們還必須能夠解釋直接適用於本金的超額支付。例如,總是付錢 $ 5 extra every month or making a single $ 1000 付款。在任何一種情況下,下個月仍需支付相同的 145 美元,因為貸款需要重新攤銷以改變預定的付款金額。鑑於此要求,我們不能僅將所有付款相加並將其與迄今為止到期的預定付款進行比較。
在查看了自定義聚合函式之後,我意識到這是解決問題的合適方法。萬一其他人遇到這個問題並想看看我是如何解決的:
CREATE OR REPLACE FUNCTION sfunc_outstanding_amount_with_carry ( previous_row NUMERIC[] , -- previous row output current_row NUMERIC[] -- current row input ) RETURNS NUMERIC[] AS $$ DECLARE current_total NUMERIC ; carried_amount NUMERIC ; outstanding_amount NUMERIC ; remainder_amount NUMERIC ; BEGIN /* If the previous outstanding amount is zero, apply the least of the remainder of the previous row and the carryover towards the current value. */ IF ( previous_row[1] = 0 ) THEN carried_amount := LEAST( previous_row[2] , current_row[2] ) ; ELSE carried_amount := 0 ; END IF ; /* Calculate the current total and determine the remainder for the next row. */ current_total := previous_row[1] + current_row[1] - carried_amount ; outstanding_amount := GREATEST( current_total , 0 ) ; remainder_amount := LEAST( current_total , 0 ) ; remainder_amount := CASE SIGN(remainder_amount) WHEN 0 THEN 0 ELSE ( remainder_amount / -1 ) END ; RETURN ARRAY[ outstanding_amount , remainder_amount ] ; END ; $$ LANGUAGE PLPGSQL IMMUTABLE ; CREATE OR REPLACE FUNCTION finalfunc_outstanding_amount_with_carry ( current_row NUMERIC[] ) RETURNS NUMERIC AS $$ BEGIN RETURN current_row[1] ; END ; $$ LANGUAGE PLPGSQL IMMUTABLE ; CREATE AGGREGATE outstanding_amount_with_carry (NUMERIC[]) ( SFUNC = sfunc_outstanding_amount_with_carry , STYPE = NUMERIC[] , FINALFUNC = finalfunc_outstanding_amount_with_carry , INITCOND = '{0,0}' ) ;
用法是這樣的:
SELECT sch.mth , sch.ctr_id , CASE WHEN LAG(pmt.pmt_cnt) OVER (ctr) > 1 AND pmt.pmts > sch.pmt_amt THEN pmt.pmts - sch.pmt_amt ELSE 0 END AS carryover , OUTSTANDING_AMOUNT_WITH_CARRY( ARRAY[ sch.pmt_amt - pmt.pmts , CASE WHEN LAG(pmt.pmt_cnt) OVER (ctr) > 1 AND pmt.pmts > sch.pmt_amt THEN pmt.pmts - sch.pmt_amt ELSE 0 END ] ) OVER ( ctr ) AS outstanding FROM sch LEFT JOIN ( SELECT ctr_id , DATE_TRUNC( 'month' , dt ) AS mth , SUM( amt ) OVER ( PARTITION BY ctr_id ) AS pmts , COUNT( * ) AS pmt_cnt FROM pmt GROUP BY ctr_id , DATE_TRUNC( 'month' , dt ) ) AS pmt ON pmt.ctr_id = sch.ctr_id AND pmt.mth = sch.mth WINDOW ctr AS ( PARTITION BY sch.ctr_id ORDER BY sch.mth )
我發現https://hashrocket.com/blog/posts/custom-aggregates-in-postgresql的解釋在將此解決方案放在一起時非常有用。
這就是我想在評論中說的:
select ctr_id, mth, cumulative_pmt_amt , coalesce(cumulative_amt, 0) as cumulative_amt , coalesce(cumulative_amt, 0) - cumulative_pmt_amt as balance from ( select ctr_id, mth , sum(pmt_amt) over (partition by ctr_id order by mth) as cumulative_pmt_amt , ( select sum(amt) from pmt y where x.ctr_id = y.ctr_id and y.dt <= x.mth ) as cumulative_amt from sch x ) as t;
對於負餘額,您可以使用案例將其映射到 0。
順便說一句,很棒的第一篇文章