Thread: Carry forward last observation

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
suchinstances, I'd like to replace the NULL value with the most recent non-NULL value, but I can't find an efficient
wayto do this. I had thought a clever WINDOW function could be used, but I think I may be in need of the unimplemented
"IGNORENULL" parameter in the last_value() window function to do this. Any help or SQL trick would be greatly
appreciated. 

Here's an example:
CREATE TABLE stk_prc (dtidx INTEGER PRIMARY KEY, price REAL);
INSERT INTO stk_prc
SELECT x-100 as dtidx, CASE WHEN x IN (102,103,105) THEN NULL ELSE x END as price
FROM (SELECT generate_series(101,105) as x) z;

SELECT * FROM stk_prc;
dtidx | price
-------+-------
     1 |   101
     2 |
     3 |
     4 |   104
     5 |

And here is what I would like to see:
dtidx | price
-------+-------
     1 |   101
     2 |   101
     3 |   101
     4 |   104
     5 |   104


I was able to get the solution with the below query using a self join, but I'm hoping that this isn't the best answer
asthe query is slow and expensive for large tables. 

SELECT x.dtidx, p.price
FROM stk_prc p,
    (SELECT a.dtidx, max(b.dtidx) as lastidx
    FROM stk_prc a, stk_prc b
    WHERE a.dtidx>=b.dtidx AND b.price IS NOT NULL GROUP BY a.dtidx) x
WHERE p.dtidx=x.lastidx;

Thanks,

Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 20th Floor | Boston, MA | 02109
Direct: (617)392-8396

This e-mail, and any attachments hereto, are intended for use by the addressee(s) only and may contain information that
is(i) confidential information of Geode Capital Management, LLC and/or its affiliates, and/or (ii) proprietary
informationof Geode Capital Management, LLC and/or its affiliates. If you are not the intended recipient of this
e-mail,or if you have otherwise received this e-mail in error, please immediately notify me by telephone (you may call
collect),or by e-mail, and please permanently delete the original, any print outs and any copies of the foregoing. Any
dissemination,distribution or copying of this e-mail is strictly prohibited.  

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.