Skip to content
Advertisement

Presto SQL – Expand by all dates/group combinations

I have a table that includes dates, groups and values:

I would like to fill the dates that are missing by the group/subgroup combinations like this:

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:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement