How to Find Minimum and Maximum startt and endd Values within Consecutive Subsets of Identical act Values?

Consecutive Row Subset Min and Max Values

Seeking a query that identifies and aggregates startt and endd values within consecutive subsets of identical act values, this question delves into data manipulation and aggregation techniques.

To approach this problem, we can utilize the following steps:

1. Assign Unique Row Numbers:
   Partition the data by name and assign sequential row numbers to each act value within each name group. This allows us to distinguish consecutive rows.
2. Compute Row Differences:
   Calculate the difference between the row numbers for each act (i.e., rn - act_n). This difference represents the position of the act value within the consecutive subset.
3. Group by Act Consecutive Positions:
   Aggregate the data using the row difference as the grouping key. This groups together rows that belong to the same consecutive subset.
4. Find Minimum and Maximum:
   Within each grouped subset, compute the minimum and maximum values of startt and endd. This provides the desired output.

The following query implements this approach:

with cte as (
  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 input
)
select
    name
    ,act
    ,min(startt) startt
    ,max(endd)   endd
    ,min(rn)     min_rn
    ,max(rn)     max_rn
from cte
group by 
    name
    ,act
    ,rn - act_n
order by 
    name
    ,min(rn)

The above is the detailed content of How to Find Minimum and Maximum startt and endd Values within Consecutive Subsets of Identical act Values?. For more information, please follow other related articles on the PHP Chinese website!