Show all columns changing between two rows

Question

I need 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 implies, the total amount is the sum of money, adgori and collateral values ​​of this line.

I need a follwing output table

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

This is step 1. The next step is to add a new column named clientId, and these changes should be displayed for each specific client. Let's say client1 is row 1, client 2 is row 2 and 3, and then client1 is again row 4. Then the comparison table for client1 will be generated using rows1 and row4.

Output table will be

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

I managed to achieve what happens when “only one column” changes between rows 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 lost how to show the change for multiple columns changing in a row.

Answer 1

In some cases, procedural methods are much simpler than pure SQL. I think this is right. Try the following function:

 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: there should be a column in the money table (say id ) to uniquely arrange the rows.

---

Pure SQL solution (assuming the table has an id column with sequential 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 need to change the condition

 on m1.id+ 1 = m2.id 

(combining the current row with the next row) according to the actual definition of the table.

You can use row_number() for this purpose. Let say event_time is a column for order, 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