Postgresql How to Find Maximum and Minimum Value in A set Order by Time

user_19049945 0
Viewed 77

I have below example table order by Time:

Example Table

I want to calculate the maximum time and minimum time for an Area.

If I use max(Time) over (partition by Area), it will get following resuls:

Unwanted Results

The problem is - for Strathfield, what I used will get the max and min time of all rows that are Strathfield.

How I want the group or partition to set up is -

Row 1-6 is a group; Row 7-11 is a group; Row 12 is a group; Row 13 is a group; Row 14 is a group; Row 15 is a group; Row 16-19 is a group; Row 20-27 is a group.

Then I can calculate the max and min time for each group.

Expected results as follow:

Expected Results

Is this possible in Postgresql? How do I achieve that? Thanks.

postgresql sql
1 Answers

This is a gaps and islands problem, and one approach uses the difference in row numbers method:

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

SELECT Time, Area,
       MAX(Time) OVER (PARTITION BY Area, rn1 - rn2) AS MaxTime,
       MIN(Time) OVER (PARTITION BY Area, rn1 - rn2) AS MinTime
FROM cte
ORDER BY Time;
user_18632290
Related