How to Find Minimum and Maximum Values for Consecutive Rows Based on Specific Act Values?

Identifying Consecutive Row Groups for Aggregation

Issue:
Retrieve the minimum and maximum values of startt and endd for consecutive rows of specific act values. Each unique name defines a distinct sequence of act occurrences. The input provides no additional columns for row identification.

Approach:

To establish row identity within each name-act group, we utilize row numbers:

row_number() over (partition by name, act order by rn) as act_n
row_number() over (partition by name order by rn) as rn

These row numbers allow us to differentiate rows within a group and identify consecutive ranges of act values.

Aggregating for Min and Max:

To find the desired output for each name-act combination, we aggregate the data within consecutive row groups. The key calculation is the difference between the two row numbers:

rn - act_n diff

This difference represents the position within each group, allowing us to determine consecutive ranges.

select name, act, min(startt) startt, max(endd) endd
from (
  select
    name,
    act,
    row_number() over (partition by name order by name, startt) rn,
    row_number() over (partition by name, act order by name, startt) act_n,
    startt,
    endd
  from @t
)
group by (rn - act_n), name, act
order by name;

This query groups the data by the row number difference, providing the desired output.

The above is the detailed content of How to Find Minimum and Maximum Values for Consecutive Rows Based on Specific Act Values?. For more information, please follow other related articles on the PHP Chinese website!