CODEWITHSUNDEEP
sqlpostgresql
Alfred Balle
Alfred Balle

Reputation: 1195

Collapse rows into buckets in PostgreSQL

I have the following dataset in PostgreSQL:

row_id, wind_speed_category, timestamp
1, 3, 2021-01-20 10:00:01
2, 3, 2021-01-21 11:00:01
3, 3, 2021-01-21 12:00:01
4, 4, 2021-01-21 14:00:01
5, 4, 2021-01-23 10:00:01
6, 3, 2021-02-22 10:00:01

I would like to collapse the rows into "running" buckets, so getting a result somewhat like:

wind_speed_category, interval_seconds
3, 86400
4, 2764800
3, 0

The interval_seconds is based upon the interval of timestamp in first row in the bucket and last row + 1 in bucket.

I've managed to get this far: http://sqlfiddle.com/#!17/3c55e/1 But don't know how to collapse into buckets/partitions/windows.

Upvotes: 0

Views: 294

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

This is a gaps and islands problem, and the first thing needed to solve it is to order all records ascending or descending by timestamp, across the entire table. Once this is done, we can try the following:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) rn1,
              ROW_NUMBER() OVER (PARTITION BY wind_speed_category
                                 ORDER BY timestamp) rn2
    FROM yourTable
)

SELECT wind_speed_category,
       EXTRACT(EPOCH FROM (MAX(timestamp) - MIN(timestamp))) AS interval_seconds
FROM cte
GROUP BY wind_speed_category, rn1-rn2;

screen capture from demo link below

Demo

Upvotes: 2

Related Questions