How to Find Minimum and Maximum Start and End Times for Consecutive Rows within Activity Groups?

Finding Minimum and Maximum Values for Consecutive Rows

You have a dataset with rows representing activities, grouped by name and ordered by row number. The goal is to find the minimum and maximum values of start and end times for consecutive rows within each activity group.

Solution Using Row Number Difference:

One approach is to use the difference between row numbers to identify consecutive rows of the same activity:

with cte as (
  select *,
    row_number() over (partition by name order by rn) as seqnum_n,
    row_number() over (partition by name, act order by rn) as seqnum_na
  from input
)
select name, act, min(startt), max(endd)
from cte
group by seqnum_n - seqnum_na, name, act;

Explanation:

1. Create a common table expression (CTE) called cte by adding two additional columns:

   • seqnum_n: Row number within each name group.
   • seqnum_na: Row number within each name-activity group.
2. Calculate the difference between seqnum_n and seqnum_na. Consecutive rows within each activity have the same difference value.
3. group by the difference value, name, and activity to aggregate the minimum and maximum values.

The above is the detailed content of How to Find Minimum and Maximum Start and End Times for Consecutive Rows within Activity Groups?. For more information, please follow other related articles on the PHP Chinese website!