计算PostgreSQL中符合条件的行的时间差

Question

我有一个PostgreSQL表，其中我需要计算名称处于状态1的时间间隔。一个名称可以多次处于此状态，我需要每个单独间隔的时间。我正在使用一个临时表，在这个表中我保存了一个间隔的起始点和结束点，然后计算它的时间差。但我不喜欢这个解决方案，我认为一定有更好的方法来做。我希望能在这里找到一些SQL专家，他们可以向我展示一些神奇而简单的解决方案。

下面是表格的外观：

|name   |state |time                      |
|-------|------|--------------------------|
|one    | 1    |'2020-11-11 01:00:02.5+01'| (start of first interval)
|one    | 1    |'2020-11-11 01:00:04.5+01'| (end of first interval) = 2 seconds
|one    | 0    |'2020-11-11 01:00:05.0+01'|
|one    | 0    |'2020-11-11 01:00:05.5+01'|
|one    | 1    |'2020-11-11 01:00:10.5+01'| (start of second interval)
|one    | 1    |'2020-11-11 01:00:11.5+01'| 
|one    | 1    |'2020-11-11 01:00:12.5+01'| (end of second interval) = 2 seconds
|two    | 0    |'2020-11-11 01:00:13.0+01'|
|two    | 0    |'2020-11-11 01:00:14.5+01'|
|two    | 1    |'2020-11-11 01:00:15.0+01'| (start of third interval)
|two    | 1    |'2020-11-11 01:00:15.5+01'| (end of third interval) = 0.5 seconds
|two    | 0    |'2020-11-11 01:00:16.5+01'|

示例表的SQL脚本：

CREATE TABLE intervals(
    name char(10),
    state integer,
    time timestamptz
);

INSERT INTO intervals(name, state, time) VALUES
    ('one', 1, '2020-11-11 01:00:02.5+01'),
    ('one', 1, '2020-11-11 01:00:04.5+01'),
    ('one', 0, '2020-11-11 01:00:05.0+01'),
    ('one', 0, '2020-11-11 01:00:05.5+01'),
    ('one', 1, '2020-11-11 01:00:10.5+01'),
    ('one', 1, '2020-11-11 01:00:11.5+01'),
    ('one', 1, '2020-11-11 01:00:12.5+01'),
    ('two', 0, '2020-11-11 01:00:13.0+01'),
    ('two', 0, '2020-11-11 01:00:14.5+01'),
    ('two', 1, '2020-11-11 01:00:15.0+01'),
    ('two', 1, '2020-11-11 01:00:15.5+01'),
    ('two', 0, '2020-11-11 01:00:16.5+01');

Answer 1

这是一种缝隙和岛屿问题。在这种情况下，行号的差异应该是您想要的结果：

select name, min(time), max(time),
       max(time) - min(time) as duration
from (select i.*,
             row_number() over (partition by name order by time) as seqnum,
             row_number() over (partition by name, state order by time) as seqnum_2
      from intervals i
     ) i
where state = 1
group by name, (seqnum - seqnum_2), state;

Here是一个db<>fiddle。

关于行号差异的逻辑有点难以解释。如果运行子查询，您将看到具有相同名称和相邻state值的行的行号差异是如何恒定的。聚合只是根据差异进行聚合，差异对它们来说是恒定的。