Create a repost of latest and second latest value-CodePudding

I have products audits table looks like this

id	product_id	column_updated	value	timestamp
1	product_1	name	Big Shoes.	"18 September 2022 6:42:50 PM GMT 05:30"
2.	product_1	name	Green Shoes	"18 September 2022 6:42:43 PM GMT 05:30"
3.	product_1	name	Big Green Shoes	"18 September 2022 6:43:43 PM GMT 05:30"

I want to show report of latest change happened on column in form like below

product_id	column_updated	latest_value	previous_value
product_1	name	Green Shoes	Big Green Shoes

I have prepared a query to fetch last 2 record but not sure how I can merge them to form a view like this?

my query is

select product_id, column_updated, value 
from audits 
where product_id = 'product_1' 
 and column_updated = 'name' 
order by timestamp desc 
limit 2;

Please suggest any approach for this, Thanks in advance!

CodePudding user response：

can you use lag to get the previous value? https://www.postgresqltutorial.com/postgresql-window-function/postgresql-lag-function/

 select product_id,
     lag(column_updated) over (
         partition by product_id order by timestamp desc
         ) as column_updated,
    column_updated as latest_value
    from ...

CodePudding user response：

This problem can be solved by combination of window functions and CTE:

with data as (
  select 
    product_id, 
    column_updated,
    value,
    lag(value) over (partition by product_id, column_updated order by updated_at asc) prev_value,
    row_number() over (partition by product_id, column_updated order by updated_at desc) rn,
    updated_at
 from log
) select 
    product_id, 
    column_updated,
    value,
    prev_value,
    updated_at
from data 
where rn = 1;

online sql editor

where lag give us previous value and row_number give ability to filter only last change

CodePudding user response：

You need to use LEADa swindow function to hget the latest and previous value

WITH CTE as
(select product_id,
   column_updated,
   value as latest_value,
     lead(value) over (
         partition by product_id,column_updated order by timestamp desc
         ) as previous_value,
       ROW_NUMBER() over (
         partition by product_id,column_updated order by timestamp desc
         ) rn
     
    from audits 
where product_id = 'product_1' 
 and column_updated = 'name')
SELECT product_id, column_updated,latest_value,previous_value FROM CTE WHERE rn = 1

product_id	column_updated	latest_value	previous_value
product_1	name	Big Green Shoes	Big Shoes.

SELECT 1

fiddle

CodePudding user response：

You don't need both row_number and lag like the other answers. You can do it with just row_number. Give it a row number and then join back with the prior value having row number 2.

WITH rownumbered AS (
  SELECT product_id, column_updated, value, updated_at
         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC) rn,
  FROM log
) 
SELECT d.product_id, d,column_updated, d.value, p.value as prevous_value
FROM rownumbered d
JOIN rownumbered p ON d.product_id = p.product_id and p.rn = 2
WHERE d.rn = 1;

You may want to group by product_id and name -- if that is the case it looks like this:

WITH rownumbered AS (
  SELECT product_id, column_updated, value, updated_at
         ROW_NUMBER() OVER (PARTITION BY product_id, column_updated ORDER BY updated_at DESC) rn,
  FROM log
) 
SELECT d.product_id, d,column_updated, d.value, p.value as prevous_value
FROM rownumbered d
JOIN rownumbered p ON d.product_id = p.product_id 
                  and d.column_updated = p.column_updated
                  and p.rn = 2
WHERE d.rn = 1;