Re: Carry forward last observation

McGehee, Robert wrote
> Hello,
> I have a table with dates and stock prices. Some of the prices are NULL
> because the stock did not trade on that day. In such instances, I'd like
> to replace the NULL value with the most recent non-NULL value, but I can't
> find an efficient way to do this.

Cannot speak to efficiency but something like this may work for you:

self-contained SQL:

WITH input_src (id, idx, price) AS (
VALUES (1,1,'101'),(1,2,NULL),(1,3,NULL),(1,4,'104'),(1,5,NULL)
)
, construct_possibles AS (
SELECT *, array_agg(price) OVER (
PARTITION BY id
ORDER BY idx
ROWS 3 PRECEDING --# attempts to limit size of array by only going back a
limited number of days
) AS possibles
FROM input_src
ORDER BY idx ASC
)
SELECT * --, some_function_to_get_most_recent_nonnull_value(possibles)
FROM construct_possibles
;

HTH

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Carry-forward-last-observation-tp5759988p5760005.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.