Clever Geek Handbook

Recursive subtraction of the contribution from the balance series

Consider the following 2 tables:

TABLE A:

PIN | ENCOUNTER | BALANCE | REFERENCE_DATE 
------------------------------------------
P1  | ABC       | 100     | 11-19-2014     
P1  | HJI       | 300     | 11-20-2014     
P1  | PIY       | 700     | 11-21-2014     
P2  | CDO       | 200     | 11-20-2014     
P2  | NHG       | 200     | 11-21-2014    
P3  | CVB       | 500     | 11-20-2014
P3  | SJK       | 100     | 11-21-2014

TABLE B:

PIN | DEPOSIT
-------------
P1  | 1000
P2  | 400
P3  | 100

The original table B

DEPOSIT

value is subtracted from the earliest matching BALANCE

table in the table . If the difference is greater than 0, it will be subtracted from on the next line until the remainder is less than or equal to 0.A

REFERENCE_DATE

PIN

BALANCE

DEPOSIT

The result after subtracting the deposit from the balances will look like this. I have included another column that divides deposits for each appointment:

PIN | ENCOUNTER | BALANCE | REFERENCE_DATE | DEPOSITS_BREAKDOWN
---------------------------------------------------------------
P1  | ABC       | 0       | 11-19-2014     | 100
P1  | HJI       | 0       | 11-20-2014     | 300
P1  | PIY       | 100     | 11-21-2014     | 600
P2  | CDO       | 0       | 11-20-2014     | 200
P2  | NHG       | 0       | 11-21-2014     | 200
P3  | CVB       | 400     | 11-20-2014     | 100
P3  | SJK       | 100     | 11-21-2014     | 0

My Postgres version is 9.3. I am trying to formulate a request for this.

+3


source to share


2 answers


Set to 0 before DEPOSIT

coversBALANCE

As you clarified, you don't need the running amount BALANCE

, just set the value to 0 until DEPOSIT

it's spent:

SELECT PIN, ENCOUNTER
     , CASE WHEN last_sum >= DEPOSIT THEN BALANCE
            ELSE GREATEST (last_sum + BALANCE - DEPOSIT, 0) END AS BALANCE
     , REFERENCE_DATE
     , CASE WHEN last_sum >= DEPOSIT THEN 0
            ELSE LEAST (BALANCE, DEPOSIT - last_sum) END AS DEPOSITS_BREAKDOWN
FROM (
   SELECT a.*
        , COALESCE(sum(a.BALANCE) OVER (
                         PARTITION BY PIN ORDER BY a.REFERENCE_DATE
                         ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND 1 PRECEDING), 0) AS last_sum
        , COALESCE(b.DEPOSIT, 0) AS DEPOSIT
   FROM        table_a a
   LEFT   JOIN table_b b USING (pin)
   ) sub;

Returns exactly what you want.

SQL Fiddle.



  • I took the idea of ​​a simpler union from @vyegorov as commented.

  • LEFT JOIN

    to table_b

    - this eliminates the possibility of finding a string in table_b

    .

  • In the subquery, calculate the running amount BALANCE

    up to the last row ( last_sum

    ). To do this, use a custom frame in the window function. And COALESCE

    the default is 0 if there are no rows. Related answers with additional clarification for a custom frame:

  • In the final one, SELECT

    return the original BALANCE

    one if it last_sum

    is equal to or greater than DEPOSIT

    (it was spent). ELSE returns the remaining difference or 0 - the running sum BALANCE

    ( last_sum + BALANCE

    ) is less DEPOSIT

    .

Current amount

Previous (simpler) answer with BALANCE

as running amount (last line 500 instead of 100):

SELECT a.PIN, a.ENCOUNTER
     , GREATEST(sum(a.BALANCE) OVER (PARTITION BY PIN ORDER BY a.REFERENCE_DATE) 
                 - COALESCE(b.DEPOSIT, 0), 0) AS BALANCE
     , a.REFERENCE_DATE
FROM   table_a      a
LEFT   JOIN table_b b USING (pin);
+2


source


I came up with this query:

SELECT *,
       sum(balance) OVER w                          balance_accum,
       greatest(deposit - sum(balance) OVER w, 0)   deposit_new,
       greatest(sum(balance) OVER w - deposit, 0)   balance_new
  FROM table_a JOIN table_b USING(pin)
WINDOW w AS (PARTITION BY pin ORDER BY reference_date)
 ORDER BY pin, reference_date;

SQL Fiddle

As Erwin mentioned, in this assumption, the last line contains 500

, not 100

.

EDIT

And this query produces the desired output:



SELECT s.*,
       CASE WHEN min(deposit_new) OVER w = 0 THEN 0 
            ELSE least(min(deposit_new) OVER w, deposit_diff) END     deposit_used,
       balance -
         CASE WHEN min(deposit_new) OVER w = 0 THEN 0
              ELSE least(min(deposit_new) OVER w, deposit_diff) END   balance_real
  FROM
  (
    SELECT *,
           sum(balance) OVER w                                      balance_accum,
           greatest(coalesce(deposit,0) - sum(balance) OVER w, 0)   deposit_new,
           least(balance, coalesce(deposit,0))                      deposit_diff
      FROM table_a LEFT JOIN table_b USING(pin)
    WINDOW w AS (PARTITION BY pin ORDER BY reference_date)
  ) s
WINDOW w AS (PARTITION BY pin ORDER BY reference_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
 ORDER BY pin, reference_date;

What's here (subquery):

  • as Erwin suggests, LEFT JOIN

    and coalesce(deposit,0)

    are used to store records without deposits;
  • the total number of actual values ​​is balance

    calculated and subtracted from deposit

    ( deposit_new

    output column );
  • deposit_diff

    is the smallest of balance

    and deposit

    , it is used to adjust the balances in the outer part.

Outside:

  • check the minimum value deposit_new

    and if 0

    reached, then all further "use" is skipped;
  • otherwise take the smallest values deposit_new

    and deposit_diff

    ;
  • checks are performed on all previous lines in the group.

The subquery is necessary as I have to use the results of the window functions in logic.

SQL Fiddle 2

+1


source






More articles:


All Articles