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. I had thought a clever WINDOW function could be used, but I think I may be in need of the unimplemented "IGNORE NULL" 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 as the 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 information of 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
