Postgresql – Filling in missing dates with values from the month before

postgresql

I have a table like this:

CREATE TABLE my_data (label text, value integer, date date);
INSERT INTO my_data (label, value, date) VALUES 
('AAA', 10, '2014-06-01'),
('AAA', 30, '2014-09-01'),
('AAA', 40, '2014-10-01'),
('AAA', 50, '2015-02-01'),
('BBB', 20, '2014-11-01'),
('BBB', 10, '2015-02-01'),
('BBB', 70, '2015-04-01');

and I need to fill up the missing dates (imagine a time series) in this way:

label | value | date
------+-------+------------
AAA   | 10    | 2014-06-01
AAA   | 10    | 2014-07-01
AAA   | 10    | 2014-08-01
AAA   | 30    | 2014-09-01
AAA   | 40    | 2014-10-01
AAA   | 40    | 2014-11-01
AAA   | 40    | 2014-12-01
AAA   | 40    | 2015-01-01
AAA   | 50    | 2015-02-01
AAA   | 50    | 2015-03-01
AAA   | 50    | 2015-04-01
BBB   | 20    | 2014-11-01
BBB   | 20    | 2014-12-01
BBB   | 20    | 2015-01-01
BBB   | 10    | 2015-02-01
BBB   | 10    | 2015-03-01
BBB   | 70    | 2015-04-01

where the time range goes from '2014-06-01' to '2015-04-01'.
What I did so far is:

WITH 
md AS
(
SELECT *, LEAD(date) OVER (PARTITION BY label ORDER BY date) AS next_date FROM my_data
),
calendar AS
(
select date::date from generate_series('2014-06-01'::date, '2015-04-01'::date, '1 month'::interval) date
)
SELECT m.label, m.value, c.date
FROM calendar c
JOIN md m
    ON c.date BETWEEN m.date AND (m.next_date - interval '1 month') order by label, date;
 label | value |    date    
-------+-------+------------
 AAA   |    10 | 2014-06-01
 AAA   |    10 | 2014-07-01
 AAA   |    10 | 2014-08-01
 AAA   |    30 | 2014-09-01
 AAA   |    40 | 2014-10-01
 AAA   |    40 | 2014-11-01
 AAA   |    40 | 2014-12-01
 AAA   |    40 | 2015-01-01
 BBB   |    20 | 2014-11-01
 BBB   |    20 | 2014-12-01
 BBB   |    20 | 2015-01-01
 BBB   |    10 | 2015-02-01
 BBB   |    10 | 2015-03-01
(13 rows)

The returned values of the query do not fill up the whole time range (until 2015-04). How can I make the query work in that way?

UPDATE

I think I have fixed it:

WITH 
md AS
(
SELECT *, LEAD(date) OVER (PARTITION BY label ORDER BY date) AS next_date FROM my_data
),
calendar AS
(
select date::date from generate_series('2014-06-01'::date, '2015-04-01'::date, '1 month'::interval) date
)
SELECT m.label, m.value, c.date
FROM calendar c
JOIN md m
    ON c.date BETWEEN m.date AND 
    (CASE WHEN m.next_date IS NULL THEN date '2015-04-01' ELSE m.next_date - interval '1 month' END) order by label, date;

Best Answer

First it generates a series of date by label and then you can use either a lateral join:

with a as
(
    select   label, generate_series(min(date), '2015-04-01'::date, interval '1 month') dt
    from     my_data
    group by label
)
select    label,
          t1.value,
           dt
from      a
left join lateral (select t1.value
                   from   my_data t1
                   where  t1.label = a.label
                   and    t1.date <= a.dt
                   order by label, date desc
                   limit 1) t1 on true
order by  label, dt

or a subquery:

with a as
(
    select   label, generate_series(min(date), '2015-04-01'::date, interval '1 month') dt
    from     my_data
    group by label
)
select    label,
          (select t1.value
           from   my_data t1
           where  t1.label = a.label
           and    t1.date <= a.dt
           order by label, date desc
           limit 1),
           dt
from      a
order by  label, dt

label | value | dt                    
:---- | ----: | :---------------------
AAA   |    10 | 2014-06-01 00:00:00+01
AAA   |    10 | 2014-07-01 00:00:00+01
AAA   |    10 | 2014-08-01 00:00:00+01
AAA   |    30 | 2014-09-01 00:00:00+01
AAA   |    40 | 2014-10-01 00:00:00+01
AAA   |    40 | 2014-11-01 00:00:00+00
AAA   |    40 | 2014-12-01 00:00:00+00
AAA   |    40 | 2015-01-01 00:00:00+00
AAA   |    50 | 2015-02-01 00:00:00+00
AAA   |    50 | 2015-03-01 00:00:00+00
AAA   |    50 | 2015-04-01 00:00:00+01
BBB   |    20 | 2014-11-01 00:00:00+00
BBB   |    20 | 2014-12-01 00:00:00+00
BBB   |    20 | 2015-01-01 00:00:00+00
BBB   |    10 | 2015-02-01 00:00:00+00
BBB   |    10 | 2015-03-01 00:00:00+00
BBB   |    70 | 2015-04-01 00:00:00+01

dbfiddle here

Related Solutions

Postgresql – Single data type for imprecise date values, as allowed by ISO 8601

No, the interval type supports reduced precision but none of the other date/time types do.

Postgres allows you to roll your own with create type but unfortunately wont allow contraints to be added to the type which limits it's usefulness in this scenario. The best I can come up with requires you to repeat check constraints on every field where the fuzzy type is used:

create type preciseness as enum('day', 'month', 'year');
create type fuzzytimestamptz as (ts timestamptz, p preciseness);
create table t( id serial primary key,
                fuzzy fuzzytimestamptz
                    check( (fuzzy).ts is not null 
                           or ((fuzzy).ts is null and (fuzzy).p is not null) ),
                    check((fuzzy).ts=date_trunc('year', (fuzzy).ts) or (fuzzy).p<'year'),
                    check((fuzzy).ts=date_trunc('month', (fuzzy).ts) or (fuzzy).p<'month'),
                    check((fuzzy).ts=date_trunc('day', (fuzzy).ts) or (fuzzy).p<'day') );

insert into t(fuzzy) values (row(date_trunc('year', current_timestamp), 'year'));
insert into t(fuzzy) values (row(date_trunc('month', current_timestamp), 'month'));
insert into t(fuzzy) values (row(date_trunc('day', current_timestamp), 'day'));

select * from t;

 id |              fuzzy
----+----------------------------------
  1 | ("2011-01-01 00:00:00+00",year)
  2 | ("2011-09-01 00:00:00+01",month)
  3 | ("2011-09-23 00:00:00+01",day)

--edit - an example equality operator:

create function fuzzytimestamptz_equality(fuzzytimestamptz, fuzzytimestamptz)
                returns boolean language plpgsql immutable as $$
begin
  return ($1.ts, $1.ts+coalesce('1 '||$1.p, '0')::interval)
         overlaps ($2.ts, $2.ts+coalesce('1 '||$2.p, '0')::interval);
end;$$;
--
create operator = ( procedure=fuzzytimestamptz_equality, 
                    leftarg=fuzzytimestamptz, 
                    rightarg=fuzzytimestamptz );

sample query:

select *, fuzzy=row(statement_timestamp(), null)::fuzzytimestamptz as equals_now,
          fuzzy=row(statement_timestamp()+'1 day'::interval, null)::fuzzytimestamptz as equals_tomorrow,
          fuzzy=row(date_trunc('month', statement_timestamp()), 'month')::fuzzytimestamptz as equals_fuzzymonth,
          fuzzy=row(date_trunc('month', statement_timestamp()+'1 month'::interval), 'month')::fuzzytimestamptz as equals_fuzzynextmonth
from t;
 id |               fuzzy                | equals_now | equals_tomorrow | equals_fuzzymonth | equals_fuzzynextmonth
----+------------------------------------+------------+-----------------+-------------------+-----------------------
  1 | ("2011-01-01 00:00:00+00",year)    | t          | t               | t                 | t
  2 | ("2011-09-01 00:00:00+01",month)   | t          | t               | t                 | f
  3 | ("2011-09-24 00:00:00+01",day)     | t          | f               | t                 | f
  4 | ("2011-09-24 11:45:23.810589+01",) | f          | f               | t                 | f

Postgresql – Combine a generated series with each row from a select

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report.

Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query.

Here's a simplified example to show you how it works:

CREATE TABLE sparse_dates(
    bakedgood text primary key,
    firstbaked date not null
);

INSERT INTO sparse_dates (bakedgood, firstbaked) VALUES
('tart', '2012-02-01'),
('baguette', '2012-02-01'),
('cookie', '2012-03-01'),
('macaron', '2012-08-01');

To get the number of baked goods first baked on a given month:

SELECT monthtimestamp, count(bakedgood) 
FROM generate_series( 
    (SELECT min(firstbaked) FROM sparse_dates),
    (SELECT max(firstbaked) FROM sparse_dates),
    INTERVAL '1' MONTH) 
    AS monthtimestamp
LEFT OUTER JOIN sparse_dates ON (monthtimestamp = firstbaked)
GROUP BY monthtimestamp;

with output like:

     monthtimestamp     | count 
------------------------+-------
 2012-02-01 00:00:00+08 |     2
 2012-03-01 00:00:00+08 |     1
 2012-04-01 00:00:00+08 |     0
 2012-05-01 00:00:00+08 |     0
 2012-06-01 00:00:00+08 |     0
 2012-07-01 00:00:00+08 |     0
 2012-08-01 00:00:00+08 |     1
(7 rows)

Best Answer

Related Solutions

Postgresql – Single data type for imprecise date values, as allowed by ISO 8601

Postgresql – Combine a generated series with each row from a select

Related Question