sql - 在 SQL 中聚合真/假序列

Question

我在 psql 中有一个表，其中包含 id 列、timestamp 列和 has_connection 列

 id |         ts          | has_connection 
----+---------------------+----------------
  1 | 2016-06-22 19:10:25 | t
  1 | 2016-06-22 19:10:26 | t
  1 | 2016-06-22 19:10:27 | f
  1 | 2016-06-22 19:10:28 | t
  1 | 2016-06-22 19:10:29 | f
  1 | 2016-06-22 19:10:30 | f
  1 | 2016-06-22 19:10:31 | t
  1 | 2016-06-22 19:10:32 | t

我的目标是获取 FALSE 或 FALSE 序列开始和结束的情况。

SELECT *,  CASE WHEN lag(has_connection) OVER (PARTITION BY id ORDER BY ts ASC) <>  has_connection  THEN 'change' ELSE 'no_change' END as change FROM test

 id |         ts          | has_connection |  change   
----+---------------------+----------------+-----------
  1 | 2016-06-22 19:10:25 | t              | no_change
  1 | 2016-06-22 19:10:26 | t              | no_change
  1 | 2016-06-22 19:10:27 | f              | change
  1 | 2016-06-22 19:10:28 | t              | change
  1 | 2016-06-22 19:10:29 | f              | change
  1 | 2016-06-22 19:10:30 | f              | no_change
  1 | 2016-06-22 19:10:31 | t              | change
  1 | 2016-06-22 19:10:32 | t              | no_change

上面的内容会告诉我翻转发生的地方，但是生成我需要的下面的输出的最有效方法是什么？

 id |     connection_end  | reconnect
----+---------------------+----------------
  1 | 2016-06-22 19:10:27 | 2016-06-22 19:10:28 
  1 | 2016-06-22 19:10:29 | 2016-06-22 19:10:31

这是数据:

CREATE TABLE test  (id INT, ts TIMESTAMP, has_connection BOOL);

INSERT INTO test (id, ts, has_connection)
VALUES
   (
      1,
      '2016-06-22 19:10:25',
      TRUE
  ),(
      1,
      '2016-06-22 19:10:26',
      TRUE
  ),(
      1,
      '2016-06-22 19:10:27',
      FALSE
  ),(
      1,
      '2016-06-22 19:10:28',
      TRUE
  ),(
      1,
      '2016-06-22 19:10:29',
      FALSE
  ),(
      1,
      '2016-06-22 19:10:30',
      FALSE
  ),(
      1,
      '2016-06-22 19:10:31',
      TRUE
  ),(
      1,
      '2016-06-22 19:10:32',
      TRUE
   );

Answer 1

你想给 false 分配一个分组，与下面的 true 相同。这是标准间隙和孤岛问题的变体。

您可以计算分组，方法是计算给定行上或之后的 true 值的数量。所有 false 都具有相同的值。以下 true(如果有)也是如此。

然后聚合:

select id,
       min(ts) as connection_end, max(ts) as reconnect
from (select t.*,
             count(*) filter (where has_connection) over (order by ts desc) as grp
      from test t
     ) t
group by id, grp
having not (array_agg(has_connection order by ts))[1]
order by min(ts);

Here是一个数据库<> fiddle 。

过滤这个有点棘手。内部子查询正确识别组。但是，这些组可以同时具有 true 和 false 值，因此您不能简单地过滤 has_connection。相反，一种方法是检查每个组中的第一个值是否为 false。

另一种方法是检查组中是否有任何 false 值。它们总是出现在任何 true 之前，因此两者是等价的。这被表述为:

having bool_or(not has_connection)

此外，这假设最后一个值始终为真。如果序列可以以 true 值结尾，那么这更准确:

select id, min(ts) as connection_end,
       max(ts) filter (where has_connection) as reconnect
from (select t.*,
             count(*) filter (where has_connection) over (order by ts desc) as grp
      from test t
     ) t
group by id, grp
having not (array_agg(has_connection order by ts))[1]
order by min(ts)