主页 / dba / 问题 / 209164
Accepted
Tiago Stapenhorst Martins
Asked: 2018-06-09 17:15:05 +0800 CST

如何从 PostgreSQL 10 中的时间序列表创建连接前驱和后继的图形？

  • 772

我有下表：

╔════╦═══════════╦═════════════════════╦═══════╗
║ id ║ sensor_id ║        time         ║ value ║
╠════╬═══════════╬═════════════════════╬═══════╣
║  1 ║         1 ║ 2018-01-01 00:00:01 ║     1 ║
║  2 ║         1 ║ 2018-01-01 00:00:02 ║     2 ║
║  3 ║         1 ║ 2018-01-01 00:00:03 ║     3 ║
║  4 ║         1 ║ 2018-01-01 00:00:03 ║     4 ║
║  5 ║         1 ║ 2018-01-01 00:00:04 ║     3 ║
║  6 ║         2 ║ 2018-01-01 00:00:01 ║     1 ║
║  7 ║         2 ║ 2018-01-01 00:00:01 ║     2 ║
║  8 ║         2 ║ 2018-01-01 00:00:02 ║     3 ║
║  9 ║         2 ║ 2018-01-01 00:00:03 ║     4 ║
║ 10 ║         2 ║ 2018-01-01 00:00:04 ║     5 ║
╚════╩═══════════╩═════════════════════╩═══════╝

CREATE TABLE sensor_time_series
(
    id SERIAL PRIMARY KEY,
    "time" TIMESTAMP NOT NULL,
    sensor_id INTEGER NOT NULL,
    value NUMERIC NOT NULL,
);

它是一个时间序列表，表示传感器在特定时间的值。是的，我知道奇怪的是“时间”在每个“sensor_id”中都不是唯一的，这是数据集中的错误。

我想要的是制作一个具有图形结构的新表/视图，将每个“sensor_id”样本连接到它在“时间”中的后继者。该表应如下所示：

╔════════════╦══════════════╗
║ current_id ║ successor_id ║
╠════════════╬══════════════╣
║          1 ║           2  ║
║          2 ║           3  ║
║          2 ║           4  ║
║          3 ║           5  ║
║          4 ║           5  ║
║          6 ║           8  ║
║          7 ║           8  ║
║          8 ║           9  ║
║          9 ║          10  ║
╚════════════╩══════════════╝

CREATE TABLE sensor_time_series_graph
(
    current_id INTEGER,
    successor_id INTEGER,
    FOREIGN KEY (current_id) REFERENCES sensor_time_series(id),
    FOREIGN KEY (successor_id) REFERENCES sensor_time_series(id)
);

第一个表中的两列（current_id 和 successor_id）FOREIGN KEY's id我如何在 PostgreSQL 10 中创建这样的东西？

我正在研究 PostgreSQL 窗口函数，我认为它们可以帮助我，但还没有意识到如何。

postgresql graph

2 个回答

  1. Best Answer
    SELECT t1.id
     , t2.id
FROM sensor_time_series t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id 
                                 AND t1."time" < t2."time"
LEFT JOIN sensor_time_series t3 ON  t1.sensor_id = t3.sensor_id 
                                AND t1."time" < t3."time" 
                                AND t3."time" < t2."time"
WHERE t3.id IS NULL;

    或者（感谢Lennart的想法）

    SELECT t1.id
     , t2.id
FROM ( SELECT id
            , sensor_id
            , lead("time") over(partition by sensor_id order by time) AS leading
       FROM sensor_time_series ) t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id 
                                 AND t1.id != t2.id
WHERE t2."time" = t1.leading;

    在最后一个查询中，连接/位置条件可以在这些部分之间自由移动以获得最佳可见性。

    小提琴

    • 3

  2. 如果 sensor_id 和时间是唯一的，你可以使用窗口函数引导：

    select id, lead(id) over(partition by sensor_id order by time) as successor_id 
from sensor_time_series;

    但是由于可以有多个后继者（如果你有几个连续的多次测量结果会快速增长），你将需要某种自连接。这与Akina 的解决方案略有不同，使用NOT EXISTS谓词而不是 a LEFT JOIN：

    SELECT t1.id, t2.id as successor_id
FROM sensor_time_series t1
JOIN sensor_time_series t2 
    ON t2.sensor_id = t1.sensor_id 
   AND t2.time > t1.time
WHERE NOT EXISTS (
    SELECT 1 
    FROM sensor_time_series t3
    WHERE t1.sensor_id = t3.sensor_id
      AND t3.time < t2.time 
      AND t3.time > t1.time
)

    如果你想包括没有后继者的观察，你可以使用LEFT JOIN：

    SELECT t1.id, t2.id as successor_id
FROM sensor_time_series t1
LEFT JOIN sensor_time_series t2 
    ON t2.sensor_id = t1.sensor_id 
   AND t2.time > t1.time
WHERE NOT EXISTS (
    SELECT 1 
    FROM sensor_time_series t3
    WHERE t1.sensor_id = t3.sensor_id
      AND t3.time < t2.time 
      AND t3.time > t1.time
)

    另一种选择是使用dense_rank()来确定继任者。使用 CTE 很方便：

    with t(id,sensor_id,rnk) as (
    select id, sensor_id
         , dense_rank() over (partition by sensor_id order by time) as rnk
    from sensor_time_series
)
select t1.id, t2.id
from t as t1
join t as t2
    on t1.sensor_id = t2.sensor_id
   and t2.rnk = t1.rnk+1;

    LEFT JOIN如果您想包括没有后继者的观察结果，请在 t1 和 t2 之间使用。

    • 2

相关问题