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:
1 2 |
|
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:
1 |
|
This difference represents the position within each group, allowing us to determine consecutive ranges.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
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!