sql - 分组连续行

Question

我有下表，称为阶段。它描述了 DRAM 操作:

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
...

我需要找出每个组的第一个 REQ 和最后一个 RESP 之间的时间。组是所有交易连续的地方。

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

如果我能找到 Period 的平均值，那就太好了:1) 计算 2 笔交易的所有组，2) 计算 6 笔交易的所有组。

Answer 1

我会以不同的方式处理这个问题。首先，我将按 TransAct 总结这些组并添加一个枚举列。此列与 Transact 之间的区别提供了您正在寻找的分组:

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);

编辑:

如果没有 with 子句，查询就会失去一点优雅。这是它的样子:

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);

请注意，我稍微更改了子查询以使用 count(distinct)。子查询现在在主表上运行，它需要计算不同的 ID 而不是所有行以获得正确的枚举。