4

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;
4

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

  • I tried to use it with an original table of ~100,000 values and it is very slow. Maybe a solution with a 'window function' could be better. – Randomize Jun 10 '17 at 18:43
0

This is a very conservative query, you can optimize some of the stuff out if you want -- like the conservative date-join that you may not require.

SELECT label, mo::date, first_value(value) OVER (PARTITION BY grp ORDER BY label, mo) AS value
FROM (
  SELECT t2.label, gs.mo, m.value, sum((m.value>0)::int) OVER (PARTITION BY t2.label ORDER BY mo) AS grp
  FROM (SELECT max(date) FROM my_data) AS t1
  CROSS JOIN (SELECT label, min(date) FROM my_data GROUP BY label) AS t2
  CROSS JOIN LATERAL generate_series(
    t2.min::timestamp without time zone,
    t1.max::timestamp without time zone,
    '1 month'
  ) AS gs(mo)
  LEFT JOIN my_data AS m
    ON m.label = t2.label
    AND date_trunc('month', gs.mo) = date_trunc('month', m.date)
) AS t
ORDER BY 1,2;

Let's dissect this. First we need to generate the date range.

SELECT t2.label, gs.mo
FROM (SELECT max(date) FROM my_data) AS t1
CROSS JOIN (SELECT label, min(date) FROM my_data GROUP BY label) AS t2
CROSS JOIN LATERAL generate_series(
  t2.min::timestamp without time zone,
  t1.max::timestamp without time zone,
  '1 month'
) AS gs(mo);
 label |           mo           
-------+------------------------
 AAA   | 2014-06-01 00:00:00-05
 AAA   | 2014-07-01 00:00:00-05
 AAA   | 2014-08-01 00:00:00-05
 AAA   | 2014-09-01 00:00:00-05
 AAA   | 2014-10-01 00:00:00-05
 AAA   | 2014-11-01 00:00:00-05
 AAA   | 2014-12-01 00:00:00-06
 AAA   | 2015-01-01 00:00:00-06
 AAA   | 2015-02-01 00:00:00-06
 AAA   | 2015-03-01 00:00:00-06
 AAA   | 2015-04-01 00:00:00-05
 BBB   | 2014-11-01 00:00:00-05
 BBB   | 2014-12-01 00:00:00-06
 BBB   | 2015-01-01 00:00:00-06
 BBB   | 2015-02-01 00:00:00-06
 BBB   | 2015-03-01 00:00:00-06
 BBB   | 2015-04-01 00:00:00-05
(17 rows)

Now, we're going to join that range back to the data set..

SELECT t2.label, gs.mo, m.value, sum((m.value>1)::int) OVER (PARTITION BY t2.label ORDER BY mo) AS grp
FROM (SELECT max(date) FROM my_data) AS t1
CROSS JOIN (SELECT label, min(date) FROM my_data GROUP BY label) AS t2
CROSS JOIN LATERAL generate_series(
  t2.min::timestamp without time zone,
  t1.max::timestamp without time zone,
  '1 month'
) AS gs(mo)
LEFT JOIN my_data AS m
  ON m.label = t2.label
  AND date_trunc('month', gs.mo) = date_trunc('month', m.date);
 label |           mo           | value | grp 
-------+------------------------+-------+-----
 AAA   | 2014-06-01 00:00:00-05 |    10 |   1
 AAA   | 2014-07-01 00:00:00-05 |       |   1
 AAA   | 2014-08-01 00:00:00-05 |       |   1
 AAA   | 2014-09-01 00:00:00-05 |    30 |   2
 AAA   | 2014-10-01 00:00:00-05 |    40 |   3
 AAA   | 2014-11-01 00:00:00-05 |       |   3
 AAA   | 2014-12-01 00:00:00-06 |       |   3
 AAA   | 2015-01-01 00:00:00-06 |       |   3
 AAA   | 2015-02-01 00:00:00-06 |    50 |   4
 AAA   | 2015-03-01 00:00:00-06 |       |   4
 AAA   | 2015-04-01 00:00:00-05 |       |   4
 BBB   | 2014-11-01 00:00:00-05 |    20 |   1
 BBB   | 2014-12-01 00:00:00-06 |       |   1
 BBB   | 2015-01-01 00:00:00-06 |       |   1
 BBB   | 2015-02-01 00:00:00-06 |    10 |   2
 BBB   | 2015-03-01 00:00:00-06 |       |   2
 BBB   | 2015-04-01 00:00:00-05 |    70 |   3
(17 rows)

A little trick there with sum((m.value>0)::int) OVER (PARTITION BY t2.label ORDER BY mo). This

  1. creates a bool out of the expression m.value>0
  2. casts the bool to an int (where 1 results if the value is >0)
  3. creates a running total with that by counting them.

Now all we have to do is retrieve the first value in that grp. So we wrap the whole thing in another select which produces

 label |     mo     | value 
-------+------------+-------
 AAA   | 2014-06-01 |    10
 AAA   | 2014-07-01 |    10
 AAA   | 2014-08-01 |    10
 AAA   | 2014-09-01 |    30
 AAA   | 2014-10-01 |    40
 AAA   | 2014-11-01 |    40
 AAA   | 2014-12-01 |    40
 AAA   | 2015-01-01 |    40
 AAA   | 2015-02-01 |    50
 AAA   | 2015-03-01 |    50
 AAA   | 2015-04-01 |    50
 BBB   | 2014-11-01 |    10
 BBB   | 2014-12-01 |    10
 BBB   | 2015-01-01 |    10
 BBB   | 2015-02-01 |    30
 BBB   | 2015-03-01 |    30
 BBB   | 2015-04-01 |    40
(17 rows)

See also, How do I generate a date series in PostgreSQL? for more information about why we use timestamp without time zone.

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.