group consecutive rows

Question

I have the following table called Phases. It describes DRAM operations:

Transact    PhaseName   TransactionBegin
1           REQ         0   
1           RESP        25  
2           REQ         5
2           RESP        30
10          REQ         50
10          RESP        105
11          REQ         55
11          RESP        115
21          REQ         60
21          RESP        120
22          REQ         65
22          RESP        125
23          REQ         70
23          RESP        130
24          REQ         75
24          RESP        140
37          REQ         200
37          RESP        240
38          REQ         205
38          RESP        245
...

I need to find the time between the first REQ and last RESP for each group. A group is where all Transact are consecutive.

TransactGroup   Period
(1..2)          30
(10..11)        65
(21..24)        80
(37..38)        45

I would then be great if I could find the average of Period for: 1) all groups that count 2 transactions, 2) all groups that count 6 transactions.

Answer 1

I would approach this differently. First I would summarize the groups by TransAct and add an enumeration column. The difference between this column and Transact provides a grouping that you are looking for:

with p as (
      select Transact,
             max(case when PhaseName = 'REQ' then TransactionBegin end) as req,
             max(case when PhaseName = 'RESP' then TransactionBegin end) as resp
      from phases
      group by Transact
     ),
     pn as (
      select pn.*, (select count(*) from p p2 where p2.Transact <= p.Transact) as seqnum
      from p
     )
select min(Transact), max(Transact), max(resp) - min(resp)
from pn
group by (Transact - seqnum);

EDIT:

Without a with clause, the query loses a bit of its elegance. Here is what it looks like:

select min(Transact), max(Transact), max(resp) - min(resp)
from (select pn.*,
             (select count(distinct p2.Transact)
              from phases p2
              where p2.Transact <= p.Transact
             ) as seqnum
      from (select Transact,
                   max(case when PhaseName = 'REQ' then TransactionBegin end) as req,
                   max(case when PhaseName = 'RESP' then TransactionBegin end) as resp
            from phases p
            group by Transact
           ) p
     ) p
group by (Transact - seqnum);

Note that I changed the subquery slightly to use count(distinct). The subquery now operates on the main table, and it needs to count distinct ids rather than all rows to get the proper enumeration.