eveo eveo - 4 months ago 10
SQL Question

exploding an sql table and inserting a row for each day where none exists

I have a table like so which I generated from modestly reworking this data + SQL to display by day, but I want to add in empty days as well with a running tally on the right hand side.

╔════════════╦════════╦═════════╦════════╗
║ day ║ num1 ║ num2 ║ tally ║
╠════════════╬════════╬═════════╬════════╣
║ 2016-06-10 ║ 9.99 ║ ║ 9.99 ║
║ 2016-06-12 ║ 136.00 ║ 9.99 ║ 145.99 ║
║ 2016-06-14 ║ ║ 145.99 ║ 145.99 ║
║ 2016-06-18 ║ 9.99 ║ 145.99 ║ 155.98 ║
║ 2016-06-19 ║ 210.00 ║ 145.99 ║ 365.98 ║
║ 2016-06-22 ║ 50.00 ║ 9.99 ║ 279.98 ║
║ 2016-06-28 ║ 69.99 ║ 59.99 ║ 349.97 ║
╚════════════╩════════╩═════════╩════════╝


I'm not sure how to make it so that the table transforms into:

╔════════════╦════════╦═════════╦════════╗
║ day ║ num1 ║ num2 ║ tally ║
╠════════════╬════════╬═════════╬════════╣
║ 2016-06-10 ║ 9.99 ║ ║ 9.99 ║
║ 2016-06-11 ║ ║ ║ 9.99 ║ <- new row with previous value
║ 2016-06-12 ║ 136.00 ║ 9.99 ║ 145.99 ║
║ 2016-06-13 ║ ║ ║ 145.99 ║ <- new row with previous value
║ 2016-06-14 ║ ║ 145.99 ║ 145.99 ║
║ 2016-06-15 ║ ║ ║ 145.99 ║ <- new row with previous value
║ 2016-06-16 ║ ║ ║ 145.99 ║ <- new row with previous value
║ 2016-06-17 ║ ║ ║ 145.99 ║ <- new row with previous value
║ 2016-06-18 ║ 9.99 ║ 145.99 ║ 155.98 ║
║ 2016-06-19 ║ 210.00 ║ 145.99 ║ 365.98 ║
║ 2016-06-20 ║ ║ ║ 365.98 ║ <- new row with previous value
║ 2016-06-21 ║ ║ ║ 365.98 ║ <- new row with previous value
║ 2016-06-22 ║ 50.00 ║ 9.99 ║ 279.98 ║
║ 2016-06-23 ║ ║ ║ 279.98 ║ <- new row with previous value
║ 2016-06-24 ║ ║ ║ 279.98 ║ <- new row with previous value
║ 2016-06-25 ║ ║ ║ 279.98 ║ <- new row with previous value
║ 2016-06-26 ║ ║ ║ 279.98 ║ <- new row with previous value
║ 2016-06-27 ║ ║ ║ 279.98 ║ <- new row with previous value
║ 2016-06-28 ║ 69.99 ║ 59.99 ║ 349.97 ║
╚════════════╩════════╩═════════╩════════╝

Answer

Give this a try (I assume your table is called tbl). I use generate_series to generate the missing rows, and use a couple of window functions to plug in the appropriate tally values in the new rows.

with all_dates as (
  SELECT day
   FROM generate_series
        ('2016-06-10'::date,
         '2016-06-28'::date,
         '1 day'::interval) day
), partitioned_data as (
  select d.day, t.num1, t.num2, t.tally,
         sum(case when t.tally is not null then 1 else 0 end) over (order by d.day) as partition_id
    from all_dates d
    left join tbl t
      on t.day = d.day
)
select t.day, t.num1, t.num2,
       first_value(t.tally) over (partition by t.partition_id) as tally
  from partitioned_data t
 order by t.day