I have a table that includes dates, groups and values:
date group subgroup value 2018-01-01 A 1 20 2018-01-04 A 1 70 2018-01-06 A 1 80
I would like to fill the dates that are missing by the group/subgroup combinations like this:
date group subgroup value 2018-01-01 A 1 20 2018-01-02 A 1 0 2018-01-03 A 1 0 2018-01-04 A 1 70 2018-01-05 A 1 0 2018-01-06 A 1 80
One way to do this is by cross joining to a table with a sequence of dates and then left joining back to the original table, however, I would like to avoid doing so as I have a large number of combinations all with different min and max dates and this type of solution would be rather “non-performant”.
Advertisement
Answer
If you want a different sequence for each group/subgroup combination then this is tricky.
However, in PrestoDB, you can use sequence() and unnest() which are almost as handy as generate_series() (okay, not really, but they do the same thing).
The query would look like this:
select gs.group, gs.subgroup, d.dte, coalesce(t.value, 0)
from (select group, subgroup, min(date) as mind, max(date) as maxd,
sequence(min(date), max(date)) as date_arr
from t
group by group, subgroup
) gs cross join
unnest(date_arr) d(dte) left join
t
on t.group = gs.grp and t.subgroup = gs.subgroup and t.date = d.dte
order by gs.group, gs.subgroup, d.dte;