Início / dba / Perguntas / 209164
Accepted
Tiago Stapenhorst Martins
Asked: 2018-06-09 17:15:05 +0800 CST

Como criar um gráfico conectando predecessores e sucessores no tempo a partir de uma tabela de séries temporais no PostgreSQL 10?

  • 772

Tenho a seguinte tabela:

╔════╦═══════════╦═════════════════════╦═══════╗
║ 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,
);

É uma tabela de séries temporais que representa o valor de um sensor em um momento específico . Sim, eu sei que é estranho que "time" não seja único dentro de cada "sensor_id", isso é um erro do dataset.

O que eu quero é fazer uma nova tabela/view com uma estrutura de gráfico, conectando cada amostra "sensor_id" ao seu sucessor em "time". A tabela deve ficar mais ou menos assim:

╔════════════╦══════════════╗
║ 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)
);

Ambas as colunas (current_id e successor_id) ID da FOREIGN KEY da primeira tabela Como posso criar algo assim no PostgreSQL 10?

Eu estava pesquisando as funções da janela do PostgreSQL e acho que elas podem me ajudar, mas ainda não percebi como.

postgresql graph

2 respostas

  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;

    ou (graças a Lennart pela ideia )

    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;

    Na última consulta, as condições de junção/onde podem ser movidas livremente entre essas seções para melhor visibilidade.

    violino

    • 3

  2. Se sensor_id e time fossem exclusivos, você poderia ter usado o lead da função de janela:

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

    mas como pode haver vários sucessores (se você tiver várias medições múltiplas consecutivas, o resultado crescerá rapidamente), você precisará de algum tipo de auto-junção. Esta é uma pequena variação da solução da Akina , usando um NOT EXISTSpredicado em vez de um 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
)

    Se você quiser incluir observações que não tenham um sucessor, você pode usar um 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
)

    Outra opção é usar dense_rank()para determinar os sucessores. É conveniente usar um 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;

    Use LEFT JOINentre t1 e t2 se quiser incluir observações sem sucessores.

    • 2

relate perguntas