描述
系统：Postgres 13 + TimescaleDB
我有一个包含设备以300秒为间隔生成的错误代码的消息的时间序列。这个时间序列应该被聚合，以便由同一设备产生的后续错误代码（在几个连续的时间间隔内）被分组，并将时间间隔相加。
源格式
| ts | 代码 | 装置 |
|---|---|---|
| 2023年3月1日12时00分 | 四 | A |
| 2023年3月1日12时00分 | 三 | B |
| 2023年3月1日12时05分 | 三 | B |
| 2023-03-01 12：10：00 | 三 | B |
| 2023-03-01 12：10：00 | 四 | B |
| 2023-03-01 14：00：00 | 三 | A |
| 2023年3月1日14时30分 | 四 | A |
目标格式
| 有效区间 | 代码 | 装置 |
|---|---|---|
| （2023-03-01 12：00：00，2023-03-01 12：04：59） | 四 | A |
| （2023-03-01 12：00：00，2023-03-01 12：14：59） | 三 | B |
| （2023-03-01 12：10：00，2023-03-01 12：14：59） | 四 | B |
| （2023-03-01 14：00：00，2023-03-01 14：04：59） | 三 | A |
| （2023-03-01 14：30：00，2023-03-01 14：34：59） | 四 | A |
进度
我试过使用[LAG()/LEAD()](https://www.postgresql.org/docs/13/functions-window.html)和PARITION BY (code, device)，但是我无法使用条件只聚合结果行：
SELECT ts,
device,
code,
LEAD(ts) OVER (PARTITION BY device, code ORDER BY ts) as next_ts
FROM source_formatDB-Fiddle
Schema（PostgreSQL v13）
CREATE TABLE timeseries (
ts timestamptz,
code bigint,
device varchar
);
INSERT INTO timeseries VALUES ('2023-03-01 12:00:00', 1, 'A');
INSERT INTO timeseries VALUES ('2023-03-01 12:05:00', 1, 'A');
INSERT INTO timeseries VALUES ('2023-03-01 12:10:00', 1, 'A');
INSERT INTO timeseries VALUES ('2023-03-01 12:10:00', 2, 'A');
INSERT INTO timeseries VALUES ('2023-03-01 12:25:00', 1, 'A');
INSERT INTO timeseries VALUES ('2023-03-01 12:30:00', 1, 'A');
INSERT INTO timeseries VALUES ('2023-03-01 12:00:00', 1, 'B');
INSERT INTO timeseries VALUES ('2023-03-01 12:20:00', 1, 'B');
INSERT INTO timeseries VALUES ('2023-03-01 12:20:00', 3, 'B');
INSERT INTO timeseries VALUES ('2023-03-01 12:25:00', 3, 'B');查询#1（获取相同设备和代码的下一条消息的Timediff）
SELECT ts,
device,
code,
LEAD(ts) OVER (PARTITION BY device, code ORDER BY ts) - ts as diff_to_next_ts
FROM timeseries;预期结果
| 有效区间 | 装置 | 代码 |
|---|---|---|
| （2023-03-01T12：00：00.000Z，2023-03-01T12：10：00.000Z） | A | 1 |
| （2023年3月1日12时25分00秒，2023年3月1日12时30分00秒） | A | 1 |
| （2023-03-01T12：10：00.000Z，2023-03-01T12：10：00.000Z） | A | 第二章 |
| （2023-03-01T12：00：00.000Z，2023-03-01T12：00：00.000Z） | B | 1 |
| （2023-03-01T12：20：00.000Z，2023-03-01T12：20：00.000Z） | B | 1 |
| （2023-03-01T12：20：00.000Z，2023-03-01T12：25：00.000Z） | B | 三 |
View on DB Fiddle
我怎样才能包含条件，然后将结果代码消息的开始和结束“合并”到一个带有间隔的单行中？有没有更合适的方法可以使用？pgSQL函数会更合适吗？
1条答案
按热度按时间u3r8eeie1#
以下查询将生成指定的结果（时区可能不同，因为插入的时间不包括时区，但列的类型为
timestamptz）：查询的第一个CTE labeled_ends 确定时间序列中的每个消息是开始还是结束设备具有特定代码的时段。第二个CTE periods 是每个设备和代码的连续序列中最后一个消息的时间。最后一个
SELECT返回连续序列开始的每个时段的范围。考虑将封闭范围
tstzrange(periods.ts, periods.period_end, '[]') AS valid_interval更改为半开放范围tstzrange(periods.ts, periods.period_end + interval '5' minute, '[)') AS valid_interval。这样做有助于使用范围运算符来确定与时间序列粒度无关的重叠和邻接。将名称更改为 valid_period 也可能更具描述性，因为 interval 通常定义为未锚定的时间长度。