I have a main table where all my results will be written to. Each object that will be checked is identified by the item_id:
Checkdate item_id Price Cat A Price Cat B
2017-04-25 1 29.99 84.99
2017-04-24 1 39.99 89.99
2017-04-23 1 39.99 91.99
2017-04-25 2 42.99 88.99
2017-04-23 2 41.99 81.99
2017-04-22 2 50.99 81.99
2017-04-21 2 42.99 81.99
In the postgres query i select all results with the current_date = checkdate to provide the newest data:
Item Price Cat A Price Cat B
1 29.99 84.99
2 42.99 88.99
So far its not a problem for me. But now i want to compare these results with the previous results. Something like that:
Item Price Cat A Price Cat A Before Price Cat B Price Cat B Before
1 29.99 39.99 84.99 89.99
2 42.99 41.99 88.99 81.99
But I have no idea how to do that. These items doesn't exist on every day (item 2 doesn't exist on 2017-04-24 for example).
Can someone help me?
select
item_id,
min(price_cat_a) filter (where rn = 1) as a,
min(price_cat_a) filter (where rn = 2) as a_before,
min(price_cat_b) filter (where rn = 1) as b,
min(price_cat_b) filter (where rn = 2) as b_before
from (
select
item_id, price_cat_a, price_cat_b,
row_number() over (partition by item_id order by checkdate desc) as rn
from t
where checkdate <= current_date
) s
where rn <= 2
group by item_id
;
item_id | a | a_before | b | b_before
---------+-------+----------+-------+----------
1 | 29.99 | 39.99 | 84.99 | 89.99
2 | 42.99 | 41.99 | 88.99 | 81.99
You can use a lateral join:
SELECT today.item_id,
today."Price Cat A",
before."Price Cat A" AS "Price Cat A Before",
today."Price Cat B",
before."Price Cat B" AS "Price Cat B Before"
FROM main today
CROSS JOIN LATERAL
(SELECT "Price Cat A",
"Price Cat B"
FROM main
WHERE item_id = today.item_id
AND "Checkdate" < today."Checkdate"
ORDER BY "Checkdate" DESC
LIMIT 1
) before
WHERE today."Checkdate" = current_date
ORDER BY today.item_id;
These items doesn't exist on every day -- because of this, your original query has an error too (i.e. it won't contain all of your items).
If you are looking for the last (and the second last) checkdate, there is no need to use current_date (unless, there might be future data in your table; in that case just append where checkdate <= current_date to filter them out).
Finding the last row (within its group, i.e. in your case, it's item_id) is a typical greatest-n-per-group problem, and the second last is easy with the lag() window function:
select distinct on (item_id)
item_id,
price_cat_a,
price_cat_a_before,
price_cat_b,
price_cat_b_before
from (select *,
lag(price_cat_a) over w price_cat_a_before,
lag(price_cat_b) over w price_cat_b_before
from t
window w as (partition by item_id order by checkdate)) t
order by item_id, checkdate desc