Carry forward last observation

Поиск
Список
Период
Сортировка
От McGehee, Robert
Тема Carry forward last observation
Дата
Msg-id 17B09E7789D3104E8F5EEB0582A8D66FF25F1CDF56@MSGRTPCCRF2WIN.DMN1.FMR.COM
обсуждение исходный текст
Ответы Re: Carry forward last observation  (David Johnston <polobo@yahoo.com>)
Список pgsql-general
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.  



В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew Bartley
Дата:
Сообщение: Re: intagg
Следующее
От: David Johnston
Дата:
Сообщение: Re: postgresql query