4

my question is closely related to the following question asked in this thread generate_series for multiple record types in postgresql. The only difference is, that instead of only inserting missing values, I want to fill the gaps with the last previous value. To illustrate it with the same example. I have two tables.

CREATE TABLE pests(id,name)
AS VALUES
  (1,'Thrip'),
  (2,'Fungus Gnosts');

CREATE TABLE pest_counts(id,pest_id,date,count)
AS VALUES
  (1,1,'2015-01-01'::date,14),
  (2,2,'2015-01-02'::date,5);

The desired output should be nearly identical. However, I want to fill missing values with previous values, if possible. So the second entry for Thrip should be 14, since a previous value exists and the first value of Fungus Gnats should be 0, since no previous value exists. Is it possible to do this using Postgres?

expected results

name         | date       | count
-------------+------------+-------
Thrip        | 2015-01-01 | 14
Thrip        | 2015-01-02 | **14** <- fill with existing previous value.
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...
  • it helps if you provide the ddl too. – Evan Carroll Nov 9 '17 at 16:18
2

Example data with two rows added:

insert into pest_counts(pest_id, date, count) values
    (1, '2015-01-03', 10),
    (2, '2015-01-04', 7);

select * from pest_counts;

 id | pest_id |    date    | count 
----+---------+------------+-------
  1 |       1 | 2015-01-01 |    14
  2 |       2 | 2015-01-02 |     5
  3 |       1 | 2015-01-03 |    10
  4 |       2 | 2015-01-04 |     7
(4 rows)

Use count() as a window function to generate groups. Each not null value starts a new group:

select name, day, count, count(count) over (partition by name order by day) as grp
from (
    select id, name, day::date
    from pests
    cross join generate_series('2015-01-01'::timestamp, '2015-01-04', '1 day') day
) d
left join pest_counts on d.id = pest_id and day = date;

     name     |    day     | count | grp 
--------------+------------+-------+-----
 fungus gnats | 2015-01-01 |       |   0
 fungus gnats | 2015-01-02 |     5 |   1
 fungus gnats | 2015-01-03 |       |   1
 fungus gnats | 2015-01-04 |     7 |   2
 thrip        | 2015-01-01 |    14 |   1
 thrip        | 2015-01-02 |       |   1
 thrip        | 2015-01-03 |    10 |   2
 thrip        | 2015-01-04 |       |   2
(8 rows)

Now you can use sum() as a window function over partitions by names and generated groups:

select name, day, coalesce(count, sum(count) over (partition by name, grp order by day), 0) as count
from (
    select name, day, count, count(count) over (partition by name order by day) as grp
    from (
        select id, name, day::date
        from pests
        cross join generate_series('2015-01-01'::timestamp, '2015-01-04', '1 day') day
    ) d
    left join pest_counts on d.id = pest_id and day = date
) s;

     name     |    day     | count 
--------------+------------+-------
 fungus gnats | 2015-01-01 |     0
 fungus gnats | 2015-01-02 |     5
 fungus gnats | 2015-01-03 |     5
 fungus gnats | 2015-01-04 |     7
 thrip        | 2015-01-01 |    14
 thrip        | 2015-01-02 |    14
 thrip        | 2015-01-03 |    10
 thrip        | 2015-01-04 |    10
(8 rows)
  • this isn't safe because of dst. =( need to use timestamp without time zone – Evan Carroll Nov 9 '17 at 19:47
  • 1
    @EvanCarrollQWERHJKL - What isn't safe? What is dst. =(? – klin Nov 9 '17 at 20:19
  • 1
    daylight savings time. you may find this informative dba.stackexchange.com/q/175963/2639 – Evan Carroll Nov 9 '17 at 20:21
  • 1
    Ok, thanks. You've found a really nasty corner case, – klin Nov 9 '17 at 22:01
  • yea, just an interesting thing that I try to get right lest someone gets stung with Pg's date math on tz. – Evan Carroll Nov 9 '17 at 22:05

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.