Show all columns changing between two rows

Question

I am required to use PostgresSql. I am given the following table called money as input

Cash | Adhoc | Collateral | Total
 ---------------------------------
 20  |   30  |    40      |   90
 32  |   12  |    40      |   84
 10  |   12  |    40      |   62
 13  |   20  |    50      |   83

As the name suggests, total is the sum of cash, adhoc and collateral values of that row.

I require the follwing output table

ChangeType  |  ChangeAmount
---------------------------
    Cash    |       12
    Adhoc   |      -18
    Cash    |      -22
    Cash    |       3
    Adhoc   |       8
 Collateral |       10

This is stage 1. In the next stage, a new column called clientId is to be added and these changes are to be shown for each specific client. Say client1 is row 1, client 2 is row 2 and 3 and then client1 is row 4 again. Then the comparison table for client1 will be formed using row1 and row4.

The output table will be

  ChangeType  |  ChangeAmount |  ClientId
------------------------------------------
    Cash      |       7       |   client1
    Adhoc     |      -10      |   client1
 Collateral   |       10      |   client1
    Cash      |      -22      |   client2

I have been able to achieve what is chaging when "only one column" is changing between rows by using

SELECT
CASE WHEN ( (Cash - lag(Cash, 1)               
           OVER ( PARTITION BY clientId 
           ) ) != 0)  
     THEN CAST('Cash' AS Text)      
     WHEN ( (Adhoc - lag(Adhoc, 1)               
           OVER ( PARTITION BY clientId 
           ) ) != 0)  
     THEN CAST('Adhoc' AS Text)  
     WHEN ( (Collateral - lag(Collateral, 1)               
           OVER ( PARTITION BY clientId 
           ) ) != 0)  
     THEN CAST('Collateral' AS Text)
  END,
  Total - lag(Total,1)
  OVER ( PARTITION BY clientId )
     FROM money                                                 

I'm however lost how to show change for multiple columns changing in a row.

Answer 1

In some cases procedural methods are much easier than pure SQL. I think this is the case. Try the function below:

create or replace function show_money_changes()
returns table (change_type text, change_amount integer)
language plpgsql
as $$
declare
    prev record;
    curr record;
    frst boolean = true;
begin
    for curr in select * from money --order by id
    loop
        if not frst then
            if curr.cash <> prev.cash then
                return query select 'cash'::text, curr.cash - prev.cash;
            end if;
            if curr.adhoc <> prev.adhoc then
                return query select 'adhoc'::text, curr.adhoc - prev.adhoc;
            end if;
            if curr.collateral <> prev.collateral then
                return query select 'collateral'::text, curr.collateral - prev.collateral;
            end if;
        end if;
        prev = curr;
        frst = false;
    end loop;
end $$;

select * from show_money_changes()

Note: you should have a column (say id) in money table to unambiguously order the rows.

---

Pure SQL solution (assuming that the table has id column with consecutive numbers):

select * from (
    select 
        unnest(array['cash', 'adhoc', 'collateral']) change_type,
        unnest(array[m2.cash- m1.cash, m2.adhoc- m1.adhoc, m2.collateral- m1.collateral]) change_value
    from money m1
    join money m2 on m1.id+ 1 = m2.id
    ) alias
where change_value <> 0

You'll have to change condition

on m1.id+ 1 = m2.id

(joining the current row with the next row) according to your actual table definition.

You can use row_number() for this purpose. Let's say event_time is a column to order by, then:

with money_with_row_numbers as (
    select *, row_number() over (order by event_time) rn
    from money)
select * from (
    select 
        unnest(array['cash', 'adhoc', 'collateral']) change_type,
        unnest(array[m2.cash- m1.cash, m2.adhoc- m1.adhoc, m2.collateral- m1.collateral]) change_value
    from money_with_row_numbers m1
    join money_with_row_numbers m2 on m1.rn+ 1 = m2.rn
    ) alias
where change_value <> 0