sql - SQL：按方向过滤行

Question

我有一个包含 2 列日期（时间戳）、状态（布尔值）的表。我有很多价值，例如：

| date                      | status    |
|-------------------------- |--------   |
| 2018-11-05T19:04:21.125Z  | true      |
| 2018-11-05T19:04:22.125Z  | true      |
| 2018-11-05T19:04:23.125Z  | true      |
....

我需要得到这样的结果：

| date_from                 | date_to                   | status    |
|-------------------------- |-------------------------- |--------   |
| 2018-11-05T19:04:21.125Z  | 2018-11-05T19:04:27.125Z  | true      |
| 2018-11-05T19:04:27.125Z  | 2018-11-05T19:04:47.125Z  | false     |
| 2018-11-05T19:04:47.125Z  | 2018-11-05T19:04:57.125Z  | true      |

所以，我需要过滤所有“相同”的值，并只返回状态真/假的时间段。

我创建这样的查询：

SELECT max("current_date"), current_status, previous_status
FROM (SELECT date as "current_date",
             status as current_status,
             (lag(status, 1) OVER (ORDER BY msgtime))::boolean AS previous_status
      FROM "table" as table
      ) as raw_data
group by current_status, previous_status

但作为回应，我只得到不超过 4 个值

标签： sqlpostgresqlwheregaps-and-islands

Answer 1

这是一个差距和孤岛问题。一种典型的方法是使用行号的差异：

select min(date), max(date), status
from (select t.*,
             row_number() over (order by date) as seqnum,
             row_number() over (partition by status order by date) as seqnum_s
      from t
     ) t
group by status, (seqnum - seqnum_s);