PostgreSQL：根据另一列交替值合并为间隔

发布于 2025-02-14 00:26:11 字数 1634 浏览 0 评论 0原文

我有一个事件数据结构，带有timestamp和的操作。

我正在尝试将间隔合并在一起以获取时间范围，当设备为OFF时，同时还合并了重叠的间隔。

我确实有一个在我的服务器上执行此操作的代码，可以将其移植到pl/pgsql。但是我想知道使用SQL是否可以实现。

我知道如何在拥有重叠的间隔中合并重叠的间隔，但是我不确定如何消除 off off 或 on 彼此之后的操作，以便我可以轻松地构建OFF范围。

示例输入：

           time          |    action     |
-------------------------+---------------+
2022-07-09 16:36:05.990  |      OFF
2022-07-10 09:01:28.263  |      ON
2022-07-10 12:40:42.759  |      OFF
2022-07-10 13:21:12.208  |      ON
2022-07-10 13:21:12.261  |      OFF
2022-07-10 13:21:23.225  |      ON
2022-07-10 16:47:10.393  |      OFF
2022-07-11 13:45:09.033  |      OFF
2022-07-11 13:45:10.439  |      OFF
2022-07-11 13:45:10.441  |      OFF
2022-07-11 13:48:26.640  |      OFF
2022-07-11 13:48:28.070  |      OFF
2022-07-11 13:49:37.502  |      OFF
2022-07-11 13:49:37.503  |      OFF
2022-07-11 13:49:38.892  |      OFF
2022-07-11 13:49:38.895  |      OFF
2022-07-11 15:57:09.478  |      OFF
2022-07-11 15:58:00.995  |      ON
2022-07-11 15:58:01.048  |      OFF
2022-07-11 15:58:12.055  |      ON

所需的输出（范围可以在2列中）

           range                                   |    state     |
---------------------------------------------------+---------------+
2022-07-09 16:36:05.990 - 2022-07-10 09:01:28.263  |      OFF
2022-07-10 12:40:42.759 - 2022-07-10 13:21:12.208  |      OFF
2022-07-10 13:21:12.261 - 2022-07-10 13:21:23.225  |      OFF
2022-07-10 16:47:10.393 - 2022-07-11 15:58:00.995  |      OFF
2022-07-11 15:58:01.048 - 2022-07-11 15:58:12.055  |      OFF

原文

I have an event data structure with timestamp and action for each device.

I`m trying to merge intervals together to get the time ranges, when the device is OFF while also merging overlapping intervals.

I do have a code which does this on my server and I can port it to PL/pgSQL. But I was wondering if this is achievable using SQL.

I know how to merge overlapping intervals when I have them, but I'm not sure how to eliminate the duplicate OFF or ON actions following each other, so that I could easily build the OFF ranges.

Example input:

           time          |    action     |
-------------------------+---------------+
2022-07-09 16:36:05.990  |      OFF
2022-07-10 09:01:28.263  |      ON
2022-07-10 12:40:42.759  |      OFF
2022-07-10 13:21:12.208  |      ON
2022-07-10 13:21:12.261  |      OFF
2022-07-10 13:21:23.225  |      ON
2022-07-10 16:47:10.393  |      OFF
2022-07-11 13:45:09.033  |      OFF
2022-07-11 13:45:10.439  |      OFF
2022-07-11 13:45:10.441  |      OFF
2022-07-11 13:48:26.640  |      OFF
2022-07-11 13:48:28.070  |      OFF
2022-07-11 13:49:37.502  |      OFF
2022-07-11 13:49:37.503  |      OFF
2022-07-11 13:49:38.892  |      OFF
2022-07-11 13:49:38.895  |      OFF
2022-07-11 15:57:09.478  |      OFF
2022-07-11 15:58:00.995  |      ON
2022-07-11 15:58:01.048  |      OFF
2022-07-11 15:58:12.055  |      ON

Desired output (range can be in 2 columns)

           range                                   |    state     |
---------------------------------------------------+---------------+
2022-07-09 16:36:05.990 - 2022-07-10 09:01:28.263  |      OFF
2022-07-10 12:40:42.759 - 2022-07-10 13:21:12.208  |      OFF
2022-07-10 13:21:12.261 - 2022-07-10 13:21:23.225  |      OFF
2022-07-10 16:47:10.393 - 2022-07-11 15:58:00.995  |      OFF
2022-07-11 15:58:01.048 - 2022-07-11 15:58:12.055  |      OFF

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

悸初 2025-02-21 00:26:11

这是一种差距和岛屿问题。我们可以在此处使用“行数方法”中的差异，以及lead（）在 timestamp on off off 范围内找到 timestamp。

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn1,
              ROW_NUMBER() OVER (PARTITION BY action ORDER BY time) rn2,
              LEAD(time) OVER (ORDER BY time) lead_time
    FROM yourTable
)

SELECT MIN(time)::text || ' - ' || MAX(lead_time)::text AS range,
       'OFF' AS state
FROM cte
WHERE action = 'OFF'
GROUP BY rn1 - rn2
ORDER BY MIN(time);

” href =“ https://dbfiddle.uk/?rdbms = postgres_14&； fiddle = 9b5dd1ebe9b555517e837eefc09303030d47” rel =“ nofollow noreforler noreferrer”> demo

This is a type of gaps and islands problem. We can use the difference in row numbers method here, along with LEAD() to find to the ending ON timestamp for each OFF range.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn1,
              ROW_NUMBER() OVER (PARTITION BY action ORDER BY time) rn2,
              LEAD(time) OVER (ORDER BY time) lead_time
    FROM yourTable
)

SELECT MIN(time)::text || ' - ' || MAX(lead_time)::text AS range,
       'OFF' AS state
FROM cte
WHERE action = 'OFF'
GROUP BY rn1 - rn2
ORDER BY MIN(time);