sql - 根据组和其他条件的 ID 的最小值和最大值

Question

我在 MS SQL Server 中有一个类似于下面的表。

      id | Timestamp | active
    -----+-----------+--------
      1  |    1:00   |    1
      1  |    2:00   |    1
      1  |    3:00   |    1
      1  |    4:00   |    0
      1  |    5:00   |    0
      1  |    6:00   |    1
      1  |    7:00   |    0
      1  |    8:00   |    0
      1  |    9:00   |    0
      1  |   10:00   |    1
      1  |   11:00   |    1
      1  |   12:00   |    0
      1  |   13:00   |    1
      2  |    2:00   |    1
      2  |    3:00   |    1
      2  |    4:00   |    0
      2  |    5:00   |    0
      3  |    8:00   |    0
      3  |    9:00   |    0
      4  |    1:00   |    1
      4  |    2:00   |    1
      5  |   16:00   |    0

我想做的是找出每个 ID 何时处于非事件状态(事件 = 0)多长时间。我试图做的是在 active = 0 时按 id 对其进行分组，并在最小和最大时间上执行 datediff。但这会给我一个 id 1 的结果，表示它在 12:00 离线 8 小时(12:00 - 4:00)。当我真正想要的是一个查询，它会给我以下结果集。

    id | approx. offline in hours |  at time
    ---+--------------------------+-----------
     1 |            1             |    5:00
     1 |            2             |    9:00
     1 |            0             |   12:00
     2 |            1             |    5:00
     3 |            0             |    9:00
     5 |            0             |   16:00

我最初尝试的错误查询是

SELECT id as [Inactive],
       DATEDIFF(hour, MIN(Timestamp), MAX(Timestamp)) as [approx. offline in hours],
       MAX(Timestamp) as [at time]
FROM table
WHERE active = 0
GROUP BY [Inactive]

但该查询的问题在于它跳过了中间的事件时间。我一直在看 THIS使用 PARTITION 提出和回答的问题，但看起来这个问题已经足够不同了，而且答案太具体到我无法理解的问题。

任何帮助表示赞赏。

Answer 1

解决此问题的一种方法(适用于任何数据库)是使用相关子查询。这个想法是为每个连续的事件值字符串分配一个组名。特定的组名是下一次值更改的时间。

select id, active, min(TimeStamp), max(TimeStamp)
from (select t.*,
             (select min(timeStamp) from t t2 where t2.id = t.id and t2.timeStamp > t.timeStamp and t2.active <> t.active
             ) groupName
      from t
     ) t
group by id, groupName, active

一个警告如何将时间戳转换为持续时间取决于数据库。由于您没有指定数据库，我会让您添加该逻辑。

此外，如果给定 id 的最后一条记录处于非事件状态，则组名为 NULL。那不是问题。