How to aggregate time series data on a postgres table for abitrary time range such that the returned result has no more than 100 rows

The table statuses has 3 columns

• id (uuid, PK)
• time (timestamp with time zone, NOT NULL)
• duration (integer, NOT NULL)

The records it contains are not evenly distributed; there could be a single record in a day or thousands in some other day.

I need to aggregate the duration (average) for any arbitrary time range such that the returned result has no more than 100 records.

Had it been grouping by day or by hour, it would have been pretty straightforward. But what's throwing me off a bit is the arbitrary time range and finding that sweet spot so the records are within the limit.

Example:

Actual data:

Assuming, I want at most 10 output (instead of 100 as originally mentioned in the question), I would expect the following output

is this what you want (obviously you'd substitute 'x' and 'y' for the limits of your date range):

with data_set as (select duration from table where time between 'x' and  'y' LIMIT 100)
select avg(duration)
from data_set
;

If this isn't what you want, can you update your question with some sample data and the result you want to achieve?

Solution 2

The following code divides the range of timestamps into 10 equal buckets (using the WIDTH_BUCKET function) and then averages all the durations for records that fall into each bucket - and uses the minimum timestamp in each bucket as a (semi-arbitrary) time to assign to the average.

To actually get 10 records there needs to be data for each "bucket" - so data needs to be relatively evenly distributed between the min and max timestamp. If it isn't, you can create your own arbitrary-sized buckets using the 3 parameter version of WIDTH_BUCKET where the 3rd parameter is an array of the lower bounds for each bucket.

I've split the code into multiple CTEs to make it clearer what it is doing - you could probably combine some of them to shorten the code.

with data1 as (
  select 
    *, 
    cast(to_char((event_time):: TIMESTAMP, 'yyyymmddhhmiss') as BigInt) event_time_int 
  from 
    event_data
), 
data2 as (
  select 
    *, 
    (select min(event_time_int) from data1) min_ts, 
    (select max(event_time_int) from data1) max_ts 
  from data1
), 
data3 as (
  select 
    *, width_bucket(event_time_int, min_ts, max_ts, 10) bucket 
  from data2
) 
select 
  min(event_time), 
  avg(event_duration) 
from 
  data3 
group by 
  bucket;

Using window functions with lead.

create table job_table(line_id integer generated always as identity, ts timestamptz, duration integer);

insert into job_table(ts, duration) values
(' 2023-06-30 03:49:43+00 ', 96),
(' 2023-06-30 03:51:25+00 ', 132),
(' 2023-06-30 03:51:55+00 ', 124),
(' 2023-06-30 03:52:25+00 ', 102),
(' 2023-06-30 03:52:55+00 ', 119),
(' 2023-06-30 03:53:25+00 ', 136),
(' 2023-06-30 03:53:55+00 ', 92 ),
(' 2023-06-30 03:54:25+00 ', 86 ),
(' 2023-06-30 03:54:55+00 ', 99 ),
(' 2023-06-30 03:55:25+00 ', 105),
(' 2023-06-30 03:55:55+00 ', 94 ),
(' 2023-06-30 03:56:25+00 ', 76 ),
(' 2023-06-30 03:56:55+00 ', 77 ),
(' 2023-06-30 03:57:25+00 ', 66 ),
(' 2023-06-30 03:57:55+00 ', 80 ),
(' 2023-06-30 03:59:13+00 ', 98 ),
(' 2023-06-30 03:59:43+00 ', 201),
(' 2023-06-30 04:00:13+00 ', 652),
(' 2023-06-30 04:00:43+00 ', 154),
(' 2023-06-30 04:01:13+00 ', 272); 

select 
    ts, round((duration + lead(duration) over(order by ts))/2.0, 2) 
from 
    job_table 
where 
   ts between '2023-06-30 03:49:43+00' and ' 2023-06-30 04:01:13+00' 
limit 10;

           ts            | duration 
-------------------------+----------
 06/29/2023 20:49:43 PDT |   114.00
 06/29/2023 20:51:25 PDT |   128.00
 06/29/2023 20:51:55 PDT |   113.00
 06/29/2023 20:52:25 PDT |   110.50
 06/29/2023 20:52:55 PDT |   127.50
 06/29/2023 20:53:25 PDT |   114.00
 06/29/2023 20:53:55 PDT |    89.00
 06/29/2023 20:54:25 PDT |    92.50
 06/29/2023 20:54:55 PDT |   102.00
 06/29/2023 20:55:25 PDT |    99.50

I have this time series as input: 3 hour's worth of data, unevenly distributed:

SELECT
  TRUNC(ts,'hh') AS thehour
, count(*) as rc
FROM tseries
GROUP BY 1
ORDER BY 1;
-- out        thehour       | rc 
-- out ---------------------+----
-- out  2023-07-01 00:00:00 | 44
-- out  2023-07-01 01:00:00 | 35
-- out  2023-07-01 02:00:00 | 21

The data look like this: ...

         ts          |  nam   
---------------------+--------
 2023-07-01 00:00:00 | row 00
 2023-07-01 00:01:00 | row 01
 2023-07-01 00:03:00 | row 02
 2023-07-01 00:04:00 | row 03
[. . .]
 2023-07-01 02:42:00 | row 96
 2023-07-01 02:45:00 | row 97
 2023-07-01 02:47:00 | row 98
 2023-07-01 02:56:00 | row 99
(100 rows)

All I do is to get a row number column, which tells me whether the row is the first, second, third, etc row if we order by the timestamp. And a second step is to integer-divide this row number by 10 - in my example (and by 100 in your example) to get a grouping criterion. Like this:

  SELECT
    ROW_NUMBER() OVER(ORDER BY ts) AS rn
  , ROW_NUMBER() OVER(ORDER BY ts) / 10 AS grp
  , *
  FROM tseries
 rn  | grp |         ts          |  nam   
-----+-----+---------------------+--------
   1 |   0 | 2023-07-01 00:00:00 | row 00
   2 |   0 | 2023-07-01 00:01:00 | row 01
   3 |   0 | 2023-07-01 00:03:00 | row 02
   4 |   0 | 2023-07-01 00:04:00 | row 03
[. . .]
   9 |   0 | 2023-07-01 00:08:00 | row 08
  10 |   1 | 2023-07-01 00:10:00 | row 09
  11 |   1 | 2023-07-01 00:10:00 | row 10
[. . .]
  96 |   9 | 2023-07-01 02:39:00 | row 95
  97 |   9 | 2023-07-01 02:42:00 | row 96
  98 |   9 | 2023-07-01 02:45:00 | row 97
  99 |   9 | 2023-07-01 02:47:00 | row 98
 100 |  10 | 2023-07-01 02:56:00 | row 99

Then, I can group by the grp column in an outside query; and when I add a LEAD() function call to the inner query, I have the choice to subtract the minimal timestamp of the group from the maximal timestamp of the group, or from the maximal next_ts of the group:

WITH
w_rn AS (
  SELECT
    ROW_NUMBER() OVER w      AS rn
  , ROW_NUMBER() OVER w / 10 AS grp
  , LEAD(ts)     OVER w      AS next_ts
  , *
  FROM tseries
  WINDOW w AS (ORDER BY ts)
)
SELECT
  grp
, MIN(ts)                AS start_ts
, MAX(ts)                AS end_ts
, MAX(next_ts)           AS next_ts
, MAX(ts) - MIN(ts)      AS duration_inside
, MAX(next_ts) - MIN(ts) AS duration_outside
FROM w_rn
GROUP BY 1
ORDER BY 1;
 grp |      start_ts       |       end_ts        |       next_ts       | duration_inside | duration_outside 
-----+---------------------+---------------------+---------------------+-----------------+------------------
   0 | 2023-07-01 00:00:00 | 2023-07-01 00:08:00 | 2023-07-01 00:10:00 | 00:08:00        | 00:10:00
   1 | 2023-07-01 00:10:00 | 2023-07-01 00:27:00 | 2023-07-01 00:31:00 | 00:17:00        | 00:21:00
   2 | 2023-07-01 00:31:00 | 2023-07-01 00:43:00 | 2023-07-01 00:44:00 | 00:12:00        | 00:13:00
   3 | 2023-07-01 00:44:00 | 2023-07-01 00:52:00 | 2023-07-01 00:55:00 | 00:08:00        | 00:11:00
   4 | 2023-07-01 00:55:00 | 2023-07-01 01:10:00 | 2023-07-01 01:10:00 | 00:15:00        | 00:15:00
   5 | 2023-07-01 01:10:00 | 2023-07-01 01:35:00 | 2023-07-01 01:40:00 | 00:25:00        | 00:30:00
   6 | 2023-07-01 01:40:00 | 2023-07-01 01:48:00 | 2023-07-01 01:50:00 | 00:08:00        | 00:10:00
   7 | 2023-07-01 01:50:00 | 2023-07-01 01:59:00 | 2023-07-01 02:02:00 | 00:09:00        | 00:12:00
   8 | 2023-07-01 02:02:00 | 2023-07-01 02:21:00 | 2023-07-01 02:23:00 | 00:19:00        | 00:21:00
   9 | 2023-07-01 02:23:00 | 2023-07-01 02:47:00 | 2023-07-01 02:56:00 | 00:24:00        | 00:33:00
  10 | 2023-07-01 02:56:00 | 2023-07-01 02:56:00 | (null)              | 00:00:00        | (null)
(11 rows)

Please do scroll the last report to the right to see how it calculates.

The following query demonstrates using ntile to aggregate time series data with irregular intervals into buckets:

WITH statuses(start_time, duration) AS (
  VALUES ('2023-06-30 03:49:43+00'::timestamptz, 96),
         ('2023-06-30 03:51:25+00'::timestamptz, 132),
         ('2023-06-30 03:51:55+00'::timestamptz, 124),
         ('2023-06-30 03:52:25+00'::timestamptz, 102),
         ('2023-06-30 03:52:55+00'::timestamptz, 119),
         ('2023-06-30 03:53:25+00'::timestamptz, 136),
         ('2023-06-30 03:53:55+00'::timestamptz, 92),
         ('2023-06-30 03:54:25+00'::timestamptz, 86),
         ('2023-06-30 03:54:55+00'::timestamptz, 99),
         ('2023-06-30 03:55:25+00'::timestamptz, 105),
         ('2023-06-30 03:55:55+00'::timestamptz, 94),
         ('2023-06-30 03:56:25+00'::timestamptz, 76),
         ('2023-06-30 03:56:55+00'::timestamptz, 77),
         ('2023-06-30 03:57:25+00'::timestamptz, 66),
         ('2023-06-30 03:57:55+00'::timestamptz, 80),
         ('2023-06-30 03:59:13+00'::timestamptz, 98),
         ('2023-06-30 03:59:43+00'::timestamptz, 201),
         ('2023-06-30 04:00:13+00'::timestamptz, 652),
         ('2023-06-30 04:00:43+00'::timestamptz, 154),
         ('2023-06-30 04:01:13+00'::timestamptz, 272)),
buckets AS (
  SELECT statuses.*, ntile(10) OVER (ORDER BY statuses.start_time) AS bucket
    FROM statuses)
SELECT buckets.bucket, min(buckets.start_time) AS start_time, avg(buckets.duration) AS mean_duration
  FROM buckets
  GROUP BY buckets.bucket
  ORDER BY buckets.bucket;

The argument to ntile determines the maximum number of buckets. There will never be more buckets than data points. Running the preceding query results in the following output:

 bucket |       start_time       |    mean_duration     
--------+------------------------+----------------------
1 | 2023-06-29 23:49:43-04 | 114.0000000000000000
2 | 2023-06-29 23:51:55-04 | 113.0000000000000000
3 | 2023-06-29 23:52:55-04 | 127.5000000000000000
4 | 2023-06-29 23:53:55-04 |  89.0000000000000000
5 | 2023-06-29 23:54:55-04 | 102.0000000000000000
6 | 2023-06-29 23:55:55-04 |  85.0000000000000000
7 | 2023-06-29 23:56:55-04 |  71.5000000000000000
8 | 2023-06-29 23:57:55-04 |  89.0000000000000000
9 | 2023-06-29 23:59:43-04 | 426.5000000000000000
10 | 2023-06-30 00:00:43-04 | 213.0000000000000000
(10 rows)

Adding a time range to the buckets subquery efficiently limits the data points included in the buckets.