timescale_density_example.sql

/* This query yields space count aggregates at 10 minute resolution for a given set of spaces over a three day period,,
   where each 10 minute bucket is represented in the output, even if that bucket contains no data.  The query first
   selects data from a sparse 1 minute cagg and unions that data to a set of empty records, generated with the Postgres
   generate_series function, then rolls up the unioned records into 10 minute aggregates.

   The union against the set of empty records ensures that all 10 minute intervals are represented in the final results.
   This step is necessary as the 1 minute data is sparse, meaning a given 10 minute interval could contain no data, and
   the time_bucket_gapfill function does not register that a bucket needs to be injected if no records exist within
   an interval.

 */
select und.space_id,
       time_bucket('10m', und.inner_bucket)                                                        as bucket,
       min(und.occupancy_min)                                                                      as occupancy_min,
       max(und.occupancy_max)                                                                      as occupancy_max,
       first(und.first_occupancy, und.inner_bucket) filter (where und.first_occupancy is not null) as first_occupancy,
       last(und.last_occupancy, und.inner_bucket) filter (where und.last_occupancy is not null)    as last_occupancy
from (select c1m.bucket          as inner_bucket,
             c1m.space_id        as space_id,
             c1m.occupancy_min   as occupancy_min,
             c1m.occupancy_max   as occupancy_max,
             c1m.first_occupancy as first_occupancy,
             c1m.last_occupancy  as last_occupancy
      from cav_space_counts_1m c1m
      where c1m.bucket between '2022-05-22 13:00:00+0000' and '2022-05-25 13:00:00+0000'
        and c1m.space_id in (997969122178368367, 997969123637986180)
      union
      select time_bucket_gapfill('10m', generate_series,
                                 '2022-05-22 13:00:00+0000',
                                 '2022-05-25 13:00:00+0000') as inner_bucket,
             space_id,
             null                                            as occupancy_min,
             null                                            as occupancy_max,
             null                                            as first_occupancy,
             null                                            as last_occupancy
      from generate_series('2022-05-22 13:00:00+0000'::timestamptz,
                           '2022-05-25 13:00:00+0000'::timestamptz,
                           '10m')
               join unnest(array [997969122178368367, 997969123637986180]) as space_id on true) as und
group by und.space_id, bucket
order by bucket;