Tengo una tabla principal donde se escribirán todos mis resultados. Cada objeto que se verificará se identifica mediante el 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.99En la consulta de postgres, selecciono todos los resultados con current_date = checkdate para proporcionar los datos más recientes:
Item Price Cat A Price Cat B 1 29.99 84.99 2 42.99 88.99Hasta ahora no es un problema para mí. Pero ahora quiero comparar estos resultados con los resultados anteriores. Algo como eso:
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.99Pero no tengo idea de cómo hacer eso. Estos elementos no existen todos los días (el elemento 2 no existe el 2017-04-24, por ejemplo).
¿Alguien me puede ayudar?
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.99Puedes usar una unión lateral:
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;Estos artículos no existen todos los días ; por eso, su consulta original también tiene un error (es decir, no contendrá todos sus artículos).
Si está buscando la última (y la penúltima) fecha de checkdate , no es necesario usar la current_date (a menos que haya datos futuros en su tabla; en ese caso, simplemente agregue where checkdate <= current_date para filtrarlos).
Encontrar la última fila (dentro de su grupo, es decir, en su caso, es item_id ) es un problema típico de los n más grandes por grupo , y el penúltimo es fácil con la función de ventana lag() :
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