Using generate_serires with partition by generate triple duplicate row

I have a table which look like this:

 dt                     type
-----------------------------
2019-07-01 10:00:00       A
2019-07-01 10:15:00       A
2019-07-01 11:00:00       A
2019-07-01 08:30:00       B
2019-07-01 08:45:00       B
2019-07-01 09:30:00       B

Each type has it own dt value but each type should have a consecutive 15 minute range dt. But some row are missing. So, I used generate_strings() to add date and partition by to do it based on each type column by using this:

SELECT 
generate_series(min(dt) over (partition by type),
                max(dt) over (partition by type), interval '15 minute')
, type
FROM t

which I generate datetime in dt column based on in min to max dt with a range of 15 minutes.

This is what I expect to get:

dt                     type
-----------------------------
2019-07-01 10:00:00       A
2019-07-01 10:15:00       A
2019-07-01 10:30:00       A
2019-07-01 10:45:00       A
2019-07-01 11:00:00       A
2019-07-01 08:30:00       B
2019-07-01 08:45:00       B
2019-07-01 09:00:00       B
2019-07-01 09:15:00       B
2019-07-01 09:30:00       B

But what I got as a result is like the expected one but it return triple for each type and datetime.

E.g.

dt                     type
-----------------------------
2019-07-01 10:00:00       A
2019-07-01 10:15:00       A
2019-07-01 10:30:00       A
2019-07-01 10:45:00       A
2019-07-01 11:00:00       A
2019-07-01 10:00:00       A
2019-07-01 10:15:00       A
2019-07-01 10:30:00       A
2019-07-01 10:45:00       A
2019-07-01 11:00:00       A
2019-07-01 10:00:00       A
2019-07-01 10:15:00       A
2019-07-01 10:30:00       A
2019-07-01 10:45:00       A
2019-07-01 11:00:00       A
2019-07-01 08:30:00       B
. . .

This also happened to type B as well.

So, from my query, what do I need to change to get the expected result?

Answer

You just want to run generate_series() over the aggregation:

SELECT type, generate_series(min_dt, max_dt, interval '15 minute')
FROM (SELECT type, MIN(dt) as min_dt, MAX(dt) as max_dt
      FROM t
      GROUP BY type
     ) t;

The window functions start by adding the min and max value to each row. Then each row gets its own series.

Advertisement

Answer