SQL-在PostgreSQL中以组的形式查找连续时间序列中的记录
我有一个时间序列数据，我试图在其中找到在特定时间间隔内按顺序排列的连续记录，按每个连续记录系列分组。例如，如果每个集合（组）的记录之间的时间间隔为5分钟，则任何在5分钟内的记录下一条记录都将在集合中，任何超过5分钟的记录都将结束集合（组）。一个新的集合（组）将在5分钟内从接下来的两个记录中出现。
**分钟分隔不在表中，在查询中计算
| | | | | |
|----|---------------------|-----------------------------------|----------|------------------------------------------------------------|
| id | timestamp | minute separation (Calculated **) | group | notes |
| 1 | 2018-02-13T01:18:00 | 0 | Group 1 | |
| 2 | 2018-02-13T01:22:00 | 4 | Group 1 | |
| 3 | 2018-02-13T01:25:00 | 3 | Group 1 | |
| 4 | 2018-02-13T01:31:00 | 6 | No Group | breaks group 1 |
| 5 | 2018-02-13T01:38:00 | 7 | No Group | not within interval on either side |
| 6 | 2018-02-13T01:44:00 | 6 | Group 2 | Start of group 2 |
| 7 | 2018-02-13T01:47:00 | 3 | Group 2 | |
| 8 | 2018-02-13T01:48:00 | 1 | Group 2 | |
| 9 | 2018-02-13T01:49:00 | 1 | Group 2 | |
| 10 | 2018-02-13T01:51:00 | 2 | Group 2 | |
| 11 | 2018-02-13T01:57:00 | 6 | Group 3 | Breaks Group 2, included in next group as start of group 3 |
| 12 | 2018-02-13T01:59:00 | 2 | Group 3 | |
| 13 | 2018-02-13T02:01:00 | 2 | Group 3 | |
| 14 | 2018-02-13T02:02:00 | 1 | Group 3 | |
| 15 | 2018-02-13T02:08:00 | 6 | No Group | Breaks group 3 |
| 16 | 2018-02-13T02:15:00 | 7 | No Group | |
| 17 | 2018-02-13T02:22:00 | 7 | No Group | |
我可以按顺序找到2行之间的差异并提取它们，但我不确定如何提取一系列n 1行。
我的工作样本在这里：http://sqlfiddle.com/#！17/e9fa1/7
SQL Insert示例数据：
CREATE TABLE time_series (
id SERIAL UNIQUE,
name TEXT,
timestamp TIMESTAMPTZ
);
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:18:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:22:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:25:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:31:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:38:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:44:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:47:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:48:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:49:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:51:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:57:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T01:59:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:01:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:02:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:08:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:15:00');
INSERT into time_series (name,timestamp) VALUES ('Test','2018-02-13T02:22:00');
工作查询：
WITH my_series AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
FROM time_series
)
SELECT o1.id AS id1, o1.timestamp AS date1, o2.id AS id2, o2.timestamp AS date2, ABS(EXTRACT(EPOCH FROM (o1.timestamp - o2.timestamp))) AS diff
FROM my_series as o1 JOIN my_series as o2
ON o1.rn + 1 = o2.rn
WHERE ABS(EXTRACT(EPOCH FROM (o1.timestamp - o2.timestamp))) < 300;
我需要将每个组作为一个集合返回（这将起作用，因为我应该能够重复查询，记录查询结束的时间戳，以开始下一个查询，尽管这似乎不是很有效），或者返回标记为组的所有组，如下所示：
预期结果：
| | | | | |
|----|---------------------|-----------------------------------|----------|------------------------------------------------------------|
| id | timestamp | minute separation (Calculated **) | group | notes |
| 1 | 2018-02-13T01:18:00 | 0 | 1 | |
| 2 | 2018-02-13T01:22:00 | 4 | 1 | |
| 3 | 2018-02-13T01:25:00 | 3 | 1 | |
| 6 | 2018-02-13T01:44:00 | 6 | 2 | |
| 7 | 2018-02-13T01:47:00 | 3 | 2 | |
| 8 | 2018-02-13T01:48:00 | 1 | 2 | |
| 9 | 2018-02-13T01:49:00 | 1 | 2 | |
| 10 | 2018-02-13T01:51:00 | 2 | 2 | |
| 11 | 2018-02-13T01:57:00 | 6 | 3 | |
| 12 | 2018-02-13T01:59:00 | 2 | 3 | |
| 13 | 2018-02-13T02:01:00 | 2 | 3 | |
| 14 | 2018-02-13T02:02:00 | 1 | 3 | |
共有2个答案
我用月度分组数据实现了类似的功能。请让我知道，如果类似的解决方案可以适用于这种情况：
https://stackoverflow.com/a/59961217/7357308
我也有一个关于这个的博客：https://medium.com/@adib0073/quick-fix-to-importing-time-series-data-from-sql-server-93ad77f9b3d0
如果有帮助，请告诉我。
您想使用lag（）和led（）。我会枚举所有组，即使是只有一行的组：
select s.*,
sum( (timestamp >= prev_timestamp + interval '5 minute' or prev_timestamp is null)::int ) over (order by timestamp) as grp
from (select s.*,
lag(timestamp) over (order by timestamp) as prev_timestamp,
lead(timestamp) over (order by timestamp) as next_timestamp
from my_series s
) s;
对于你的实际问题，逻辑要复杂一点。我认为这就是诀窍：
select s.*,
(case when timestamp > prev_timestamp + interval '5 minute' and
timestamp < next_timestamp - interval '5 minute' and
then NULL
else sum( (timestamp >= prev_timestamp + interval '5 minute' and next_timestamp < timestamp + interval '5 minute')::int ) over (order by timestamp)
end) as grp
from (select s.*,
lag(timestamp) over (order by timestamp) as prev_timestamp,
lead(timestamp) over (order by timestamp) as next_timestamp
from my_series s
) s;
-
我试图在Oracle 11g中运行一个sql查询，它将下面给定的数据集转换为下一个数据集。 这样做的逻辑是start date1和end date1将是连续的。另外start_date2和end date2需要是连续的。如果在某些时候end date2与下一个start date2不匹配，那么需要添加一个具有相同id并且具有enddate2作为下一个start date1的新行。 非常感谢您的帮助
-
问题内容： 对于这个例子说，我有两个字段的表，和。 该表具有以下数据 我想回来 我想返回的结果是每个区域递增连续值的最长长度。对于。 我将如何在MS Sql 2005上执行此操作？ 问题答案： 一种方法是使用遍历每一行的递归CTE。如果该行符合条件（增加同一区域的订单号），则将链长增加一。如果没有，则启动一个新链： SQL Fiddle的实时示例。 另一种方法是使用查询查找“中断”，即以相同区域的
-
问题内容： 我有以下数组 我想找到连续为零的数组的开始和结束索引，输出上方的数组如下所示 我想做得尽可能高效。 问题答案： 这是一个相当紧凑的矢量化实现。我已经更改了一些要求，所以返回值有点“ numpythonic”：它创建一个形状为（m，2）的数组，其中m是零的“游程”数。第一列是每次运行中前0个的索引，第二列是运行后第一个非零元素的索引。（例如，此索引模式匹配切片的工作方式以及函数的工作方式
-
问题内容： 这是一个像这样的时间序列数据，称为df： 我想获取连续“值”为1的日期范围，因此如何获得最终结果，如下所示： 问题答案： 这应该做 每当Value更改时，value_grp将增加1。在下面，您可以提取分组结果
-
我正在处理一个大的时间序列，其中一列包含四个不同的传感器，一列包含测量值。我需要为属于同一时间的测量分配一个 id。问题是，每个设备的测量时间略有不同，因此我不能简单地按时间戳对它们进行分组。在按时间排序的数据框中，应分组的测量值可以通过唯一设备 ID 序列进行识别。这里的问题是，一次 4 台设备记录一个值，另一次 3 台设备记录一个值。我的数据如下所示。 您可以通过以下方式复制： 我需要为连续的
-
问题内容： 如果我有串，我要检查，如果它作为一个连续存在 串 中，我可以使用： 在非连续子 序列 的情况下，我可以使用什么？例： 问题答案： 我不知道是否有内置功能，但是手动操作相当简单