Sample Header Ad - 728x90

Carry over long sequence of missing values with Postgres

7 votes
2 answers
4525 views
I have a table like this: create table foo (foo_label text, foo_price int, foo_date date); insert into foo ( values ('aaa', 100, '2017-01-01'), ('aaa', NULL, '2017-02-01'), ('aaa', NULL, '2017-03-01'), ('aaa', NULL, '2017-04-01'), ('aaa', 140, '2017-05-01'), ('aaa', NULL, '2017-06-01'), ('aaa', 180, '2017-07-01') ); As you can see a few values on the foo_price column are missing. What I need is that missing values get filled up with the "previous" available value in this way: foo_label | fixed_foo_price | foo_date -----------+-----------------+------------ aaa | 100 | 2017-01-01 aaa | 100 | 2017-02-01 aaa | 100 | 2017-03-01 aaa | 100 | 2017-04-01 aaa | 140 | 2017-05-01 aaa | 140 | 2017-06-01 aaa | 180 | 2017-07-01 **My attempt:** select foo_label, (case when foo_price is null then previous_foo_price else foo_price end) as fixed_foo_price, foo_date from ( select foo_label, lag(foo_price) OVER (PARTITION BY foo_label order by foo_date::date) as previous_foo_price, foo_price, foo_date from foo ) T; As you can see from here: https://www.db-fiddle.com/#&togetherjs=s6giIonUxT It doesn't fill completely the '100' series. Any idea how can I get the wanted result?
Asked by Randomize (1203 rep)
Sep 18, 2017, 02:24 PM
Last activity: Jan 3, 2024, 10:24 AM