SQL Encontre pares de linhas com a próxima melhor correspondência de carimbo de data / hora
Meu desafio é encontrar pares de linhas adjacentes por carimbo de data / hora e manter apenas os pares com distância mínima de um campo de valor (valores positivos da diferença)
Uma tabela measurementcoleta dados de diferentes sensores com um carimbo de data / hora e um valor.
id | sensor_id | timestamp | value
---+-----------+-----------+------
1 | 1 | 12:00:00 | 5
2 | 2 | 12:01:00 | 6
3 | 1 | 12:02:00 | 4
4 | 2 | 12:02:00 | 7
5 | 2 | 12:03:00 | 3
6 | 1 | 12:05:00 | 3
7 | 2 | 12:06:00 | 4
8 | 2 | 12:07:00 | 5
9 | 1 | 12:08:00 | 6
O valor de um sensor é válido desde seu carimbo de data / hora até o carimbo de data / hora de seu próximo registro (mesmo sensor_id).
Representação gráfica
A linha verde inferior mostra a distância dos valores do sensor 1 (linha azul) e do sensor 2 (linha vermelha) ao longo do tempo.
Meu objetivo é
- para combinar apenas os registros de 2 sensores que correspondem à lógica do carimbo de data / hora (para obter a linha verde)
- para encontrar os mínimos locais dinstance em
- 12:01:00 (às 12:00:00 não há registro para o sensor 2)
- 12:05:00
- 12:08:00
A tabela real reside em um banco de dados PostgreSQL e contém cerca de 5 milhões de registros de 15 sensores.
Dados de teste
create table measurement (
id serial,
sensor_id integer,
timestamp timestamp,
value integer)
;
insert into measurement (sensor_id, timestamp, value)
values
(1, '2020-08-16 12:00:00', 5),
(2, '2020-08-16 12:01:00', 6),
(1, '2020-08-16 12:02:00', 4),
(2, '2020-08-16 12:02:00', 7),
(2, '2020-08-16 12:03:00', 3),
(1, '2020-08-16 12:05:00', 3),
(2, '2020-08-16 12:06:00', 4),
(2, '2020-08-16 12:07:00', 5),
(1, '2020-08-16 12:08:00', 6)
;
Minha abordagem
era escolher 2 sensores arbitrários (por certos sensor_ids), fazer uma auto-junção e reter para qualquer registro do sensor 1 apenas aquele registro do sensor 2 com o carimbo de data / hora anterior (carimbos de data / hora maiores do sensor 2 com carimbo de data e hora do sensor 1 <= carimbo de data e hora do sensor 2) .
select
*
from (
select
*,
row_number() over (partition by m1.timestamp order by m2.timestamp desc) rownum
from measurement m1
join measurement m2
on m1.sensor_id <> m2.sensor_id
and m1.timestamp >= m2.timestamp
--arbitrarily sensor_ids 1 and 2
where m1.sensor_id = 1
and m2.sensor_id = 2
) foo
where rownum = 1
union --vice versa
select
*
from (
select
*,
row_number() over (partition by m2.timestamp order by m1.timestamp desc) rownum
from measurement m1
join measurement m2
on m1.sensor_id <> m2.sensor_id
and m1.timestamp <= m2.timestamp
--arbitrarily sensor_ids 1 and 2
where m1.sensor_id = 1
and m2.sensor_id = 2
) foo
where rownum = 1
;
Mas isso retorna um par com 12:00:00onde o sensor 2 não tem dados (não é um grande problema)
e na mesa real a execução da instrução não termina após o expediente (grande problema).
Encontrei algumas perguntas semelhantes, mas não correspondem ao meu problema
- SQL Join no mais próximo menos que a data
- SQL Join mesma tabela com base na data e hora e nível de inventário
Desde já, obrigado!
Respostas
A primeira etapa é calcular a diferença em cada carimbo de data / hora. Um método usa uma junção lateral e agregação condicional:
select t.timestamp,
max(m.value) filter (where s.sensor_id = 1) as value_1,
max(m.value) filter (where s.sensor_id = 2) as value_2,
abs(max(m.value) filter (where s.sensor_id = 2) -
max(m.value) filter (where s.sensor_id = 1)
) as diff
from (values (1), (2)) s(sensor_id) cross join
(select distinct timestamp
from measurement
where sensor_id in (1, 2)
) t left join lateral
(select m.value
from measurement m
where m.sensor_id = s.sensor_id and
m.timestamp <= t.timestamp
order by m.timestamp desc
limit 1
) m
on 1=1
group by timestamp;
Agora a questão é quando a diferença entra em um mínimo local. Para seus dados de amostra, os mínimos locais são todos de uma unidade de tempo. Isso significa que você pode usar lag()e lead()encontrá-los:
with t as (
select t.timestamp,
max(m.value) filter (where s.sensor_id = 1) as value_1,
max(m.value) filter (where s.sensor_id = 2) as value_2,
abs(max(m.value) filter (where s.sensor_id = 2) -
max(m.value) filter (where s.sensor_id = 1)
) as diff
from (values (1), (2)) s(sensor_id) cross join
(select distinct timestamp
from measurement
where sensor_id in (1, 2)
) t left join lateral
(select m.value
from measurement m
where m.sensor_id = s.sensor_id and
m.timestamp <= t.timestamp
order by m.timestamp desc
limit 1
) m
on 1=1
group by timestamp
)
select *
from (select t.*,
lag(diff) over (order by timestamp) as prev_diff,
lead(diff) over (order by timestamp) as next_diff
from t
) t
where (diff < prev_diff or prev_diff is null) and
(diff < next_diff or next_diff is null);
Essa pode não ser uma suposição razoável a se fazer. Portanto, filtre os valores duplicados adjacentes antes de aplicar esta lógica:
select *
from (select t.*,
lag(diff) over (order by timestamp) as prev_diff,
lead(diff) over (order by timestamp) as next_diff
from (select t.*, lag(diff) over (order by timestamp) as test_for_dup
from t
) t
where test_for_dup is distinct from diff
) t
where (diff < prev_diff or prev_diff is null) and
(diff < next_diff or next_diff is null)
Aqui está um violino db <>.
Você pode usar algumas junções laterais. Por exemplo:
with
t as (select distinct timestamp as ts from measurement)
select
t.ts, s1.value as v1, s2.value as v2,
abs(s1.value - s2.value) as distance
from t,
lateral (
select value
from measurement m
where m.sensor_id = 1 and m.timestamp <= t.ts
order by timestamp desc
limit 1
) s1,
lateral (
select value
from measurement m
where m.sensor_id = 2 and m.timestamp <= t.ts
order by timestamp desc
limit 1
) s2
order by t.ts
Resultado:
ts v1 v2 distance
--------------------- -- -- --------
2020-08-16 12:01:00.0 5 6 1
2020-08-16 12:02:00.0 4 7 3
2020-08-16 12:03:00.0 4 3 1
2020-08-16 12:05:00.0 3 3 0
2020-08-16 12:06:00.0 3 4 1
2020-08-16 12:07:00.0 3 5 2
2020-08-16 12:08:00.0 6 5 1
Veja o exemplo de execução em DB Fiddle .
Além disso, se quiser todos os carimbos de data / hora , mesmo os incomparáveis 12:00:00, você pode fazer:
with
t as (select distinct timestamp as ts from measurement)
select
t.ts, s1.value as v1, s2.value as v2,
abs(s1.value - s2.value) as distance
from t
left join lateral (
select value
from measurement m
where m.sensor_id = 1 and m.timestamp <= t.ts
order by timestamp desc
limit 1
) s1 on true
left join lateral (
select value
from measurement m
where m.sensor_id = 2 and m.timestamp <= t.ts
order by timestamp desc
limit 1
) s2 on true
order by t.ts
Nesses casos, não é possível calcular a distância.
Resultado:
ts v1 v2 distance
--------------------- -- ------ --------
2020-08-16 12:00:00.0 5 <null> <null>
2020-08-16 12:01:00.0 5 6 1
2020-08-16 12:02:00.0 4 7 3
2020-08-16 12:03:00.0 4 3 1
2020-08-16 12:05:00.0 3 3 0
2020-08-16 12:06:00.0 3 4 1
2020-08-16 12:07:00.0 3 5 2
2020-08-16 12:08:00.0 6 5 1
O preenchimento de valores ausentes requer funções de janela e um produto cartesiano de cada minuto cruzado com seus dois sensores.
O invarscte aceita os parâmetros.
with invars as (
select '2020-08-16 12:00:00'::timestamp as start_ts,
'2020-08-16 12:08:00'::timestamp as end_ts,
array[1, 2] as sensor_ids
),
Crie a matriz de minutexsensor_id
calendar as (
select g.minute, s.sensor_id,
sensor_ids[1] as sid1,
sensor_ids[2] as sid2
from invars i
cross join generate_series(
i.start_ts, i.end_ts, interval '1 minute'
) as g(minute)
cross join unnest(i.sensor_ids) as s(sensor_id)
),
Encontre mgrppara cada vez que um novo valor estiver disponível em umsensor_id
gaps as (
select c.minute, c.sensor_id, m.value,
sum(case when m.value is null then 0 else 1 end)
over (partition by c.sensor_id
order by c.minute) as mgrp,
c.sid1, c.sid2
from calendar c
left join measurement m
on m.timestamp = c.minute
and m.sensor_id = c.sensor_id
),
Interpole os valores ausentes transportando o valor mais recente
interpolated as (
select minute,
sensor_id,
coalesce(
value, first_value(value) over
(partition by sensor_id, mgrp
order by minute)
) as value, sid1, sid2
from gaps
)
Faça o distancecálculo ( sum()poderia ter sido max()ou min()- não faz diferença.
select minute,
sum(value) filter (where sensor_id = sid1) as value1,
sum(value) filter (where sensor_id = sid2) as value2,
abs(
sum(value) filter (where sensor_id = sid1)
- sum(value) filter (where sensor_id = sid2)
) as distance
from interpolated
group by minute
order by minute;
Resultados:
| minute | value1 | value2 | distance |
| ------------------------ | ------ | ------ | -------- |
| 2020-08-16T12:00:00.000Z | 5 | | |
| 2020-08-16T12:01:00.000Z | 5 | 6 | 1 |
| 2020-08-16T12:02:00.000Z | 4 | 7 | 3 |
| 2020-08-16T12:03:00.000Z | 4 | 3 | 1 |
| 2020-08-16T12:04:00.000Z | 4 | 3 | 1 |
| 2020-08-16T12:05:00.000Z | 3 | 3 | 0 |
| 2020-08-16T12:06:00.000Z | 3 | 4 | 1 |
| 2020-08-16T12:07:00.000Z | 3 | 5 | 2 |
| 2020-08-16T12:08:00.000Z | 6 | 5 | 1 |
---
[View on DB Fiddle](https://www.db-fiddle.com/f/p65hiAFVT4v3TrjTPbrZnC/0)
Por favor, veja este violino funcionando .
Funções de janela e verificação dos vizinhos. (você precisará de um anti-selfjoin extra para remover as duplicatas e inventar um desempate para o problema de casamento estável )
SELECT id,sensor_id, ztimestamp,value
-- , prev_ts, next_ts
, (ztimestamp - prev_ts) AS prev_span
, (next_ts - ztimestamp) AS next_span
, (sensor_id <> prev_sensor) AS prev_valid
, (sensor_id <> next_sensor) AS next_valid
, CASE WHEN (sensor_id <> prev_sensor AND sensor_id <> next_sensor) THEN
CASE WHEN (ztimestamp - prev_ts) < (next_ts - ztimestamp) THEN prev_id ELSE next_id END
WHEN (sensor_id <> prev_sensor) THEN prev_id
WHEN (sensor_id <> next_sensor) THEN next_id
ELSE NULL END AS best_neigbor
FROM (
SELECT id,sensor_id, ztimestamp,value
, lag(id) OVER www AS prev_id
, lead(id) OVER www AS next_id
, lag(sensor_id) OVER www AS prev_sensor
, lead(sensor_id) OVER www AS next_sensor
, lag(ztimestamp) OVER www AS prev_ts
, lead(ztimestamp) OVER www AS next_ts
FROM measurement
WINDOW www AS (order by ztimestamp)
) q
ORDER BY ztimestamp,sensor_id
;
Resultado:
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 9
id | sensor_id | ztimestamp | value | prev_span | next_span | prev_valid | next_valid | best_neigbor
----+-----------+---------------------+-------+-----------+-----------+------------+------------+--------------
1 | 1 | 2020-08-16 12:00:00 | 5 | | 00:01:00 | | t | 2
2 | 2 | 2020-08-16 12:01:00 | 6 | 00:01:00 | 00:01:00 | t | t | 3
3 | 1 | 2020-08-16 12:02:00 | 4 | 00:01:00 | 00:00:00 | t | t | 4
4 | 2 | 2020-08-16 12:02:00 | 7 | 00:00:00 | 00:01:00 | t | f | 3
5 | 2 | 2020-08-16 12:03:00 | 3 | 00:01:00 | 00:02:00 | f | t | 6
6 | 1 | 2020-08-16 12:05:00 | 3 | 00:02:00 | 00:01:00 | t | t | 7
7 | 2 | 2020-08-16 12:06:00 | 4 | 00:01:00 | 00:01:00 | t | f | 6
8 | 2 | 2020-08-16 12:07:00 | 5 | 00:01:00 | 00:01:00 | f | t | 9
9 | 1 | 2020-08-16 12:08:00 | 6 | 00:01:00 | | t | | 8
(9 rows)