postgresql - PostgreSQL：获取列值在两种状态之间变化的时间窗口

Question

我有以下信息可能有助于理解我的来源是什么，并且可能有人可以给我一个正确方向的提示，所以我得到了我期望的结果。

数据库来源：

+-------------------------------+-------------+---------------------------------------------+
| time                          | state       | entity_id                                   |
|-------------------------------+-------------+---------------------------------------------|
| 2020-11-21 22:08:30.697555+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-21 22:09:09.653517+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-21 22:10:10.100732+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-21 23:00:19.579074+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-21 23:50:28.929034+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 00:40:36.844207+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 01:30:46.625915+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 02:20:55.728533+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 03:11:04.69336+00  | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 04:51:25.005591+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 05:41:35.027022+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 08:28:55.070897+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 08:28:55.071764+00 | on          | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 09:16:44.351438+00 | on          | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 09:16:44.353371+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 10:06:58.250254+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 10:57:07.857459+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 11:47:17.45144+00  | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 12:37:25.463751+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 13:27:34.908661+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 14:17:41.562764+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 14:38:02.050999+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 14:38:02.052771+00 | on          | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 14:38:48.250308+00 | on          | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 14:38:48.251718+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 15:29:03.709037+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 16:19:13.114918+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 17:09:21.919555+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 17:59:29.769124+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 18:49:38.585074+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 19:00:22.724232+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 19:01:05.545263+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 21:20:01.554877+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 22:00:39.663975+00 | unavailable | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 22:00:42.898337+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 22:01:23.566599+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 22:10:08.907583+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 22:32:17.391582+00 | off         | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 22:32:56.633317+00 | off         | binary_sensor.bedroom_double_window_contact |

当前查询（这给了我重复）：

SELECT
    q1.start,
    q2.stop,
    q1.entity_id AS entity
FROM (
    SELECT 
        DISTINCT time as start,
        entity_id
    FROM ltss 
    WHERE 
        entity_id LIKE 'binary_sensor.bedroom_double_window_contact'
        AND state = 'on'
    ORDER BY start ASC
    ) q1
INNER JOIN (
    SELECT 
        entity_id, 
        time as stop,
        state
    FROM ltss
    WHERE state = 'off'
    ORDER BY time ASC
) q2 ON (q1.entity_id = q2.entity_id AND q2.stop > q1.start)
ORDER BY q1.start

我想接收值“on”后跟“off”的所有范围。

由于“on”和“off”有多个条目（因为其他一些列已更改），我想要最近的“on”但最早的“off”。

必须省略两个“开”和“关”状态之间的所有其他“开”值，以及任何没有前面“开”值的“关”状态。

如果最近没有可用的“关闭”值（因为尚未达到“关闭”状态），我想获取该行但使用nullfor stop。

希望人群可以在这里帮助我。

提前致谢！

编辑：根据要求，这是我希望从本文前面提供的数据中得到的输出。

+-------------------------------+-------------------------------+---------------------------------------------+
| start                         | end                           | entity_id                                   |
+-------------------------------+-------------------------------+---------------------------------------------+
| 2020-11-22 08:28:55.071764+00 | 2020-11-22 09:16:44.353371+00 | binary_sensor.bedroom_double_window_contact |
| 2020-11-22 14:38:02.052771+00 | 2020-11-22 14:38:48.251718+00 | binary_sensor.bedroom_double_window_contact |

标签： postgresqltimescaledb