具有自引用條件的滾動總和聚合

我有一個我認為是遞歸 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。

小提琴

順便說一句，很棒的第一篇文章

引用自：https://dba.stackexchange.com/questions/253583