4

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?

  • 2
    Thank you for providing a proper test case. Too many questions fail to do so. – Erwin Brandstetter Sep 18 '17 at 17:28
  • This would be more straight forward if Pg implemented IGNORE NULLS – Evan Carroll Sep 18 '17 at 18:13
8

I would form groups with the window function count() and then take the first value for each group:

SELECT foo_label
     , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS fixed_foo_price
     , foo_date
FROM  (
   SELECT foo_label
        , count(foo_price) OVER (PARTITION BY foo_label ORDER BY foo_date) AS grp
        , foo_price
        , foo_date
   FROM   foo
   ) sub;

This works because count() only counts non-null values. So all rows with NULL end up in the same group as the last row with an actual value. Exactly what you need.

  • Clever, I like it. – Evan Carroll Sep 18 '17 at 18:10
  • I agree really clever! – Randomize Sep 18 '17 at 18:45
  • That got an "Ah HA! Clever" from me - upvoted :) – Giles May 17 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.