Dividing and aggregating intervals into buckets

user_8544005 0
Viewed 234

I am successfully using PostgreSQL width_bucket(val, min, max, buckets) function to divide my dataset into equal-sized buckets. But my data contains time ranges in milliseconds (start_time and end_time). I am looking for a way to include the row into multiple buckets based on the time interval.

This is an example of where I am now. I have start and end buckets for each row:

 start_time |    end_time    | start_bucket |  end_bucket 
------------+----------------+--------------+------------
       0    |      492       |     1        |      1
     404    |      580       |     1        |      1
       0    |      628       |     1        |      1
     560    |      740       |     1        |      2
     644    |      720       |     1        |      2
      24    |      160       |     1        |      1
       0    |       88       |     1        |      1
     640    |     1268       |     1        |      2
     556    |      716       |     1        |      1
       0    |     2086       |     1        |      3

I am looking for an aggregated result:

   bucket   |    count   
------------+---------------
       1    |       10      
       2    |        4      
       3    |        1

I know how to achieve the result if I only take the start_time or end_time alone into consideration. Is there a way to aggregate what I have now into the desired result?

postgresql sql database
1 Answers

Use generate_series():

select gs.bucket, count(*)
from t cross join lateral
     generate_series(t.start_bucket, t.end_bucket) as gs(bucket)
group by gs.bucket
order by 1;
user_11440350
Related