SQL 다음으로 최적의 타임 스탬프 일치가있는 행 쌍 찾기
Aug 16 2020
내 과제는 타임 스탬프에 인접한 행 쌍을 찾고 값 필드의 최소 거리 (차이의 양수 값) 만있는 쌍만 유지하는 것입니다.
테이블 measurement은 타임 스탬프와 값을 사용하여 서로 다른 센서에서 데이터를 수집합니다.
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
센서의 값은 타임 스탬프에서 다음 레코드의 타임 스탬프 (동일한 sensor_id)까지 유효합니다.
그래픽 표현
아래쪽 녹색 선은 시간에 따른 센서 1 (파란색 선) 및 센서 2 (빨간색 선) 값의 거리를 보여줍니다.
내 목표는
- 타임 스탬프 로직과 일치하는 2 개 센서의 레코드 만 결합 (녹색 선을 얻기 위해)
- dinstance 지역 최소값을 찾으려면
- 12:01:00 (12:00:00에 센서 2에 대한 기록이 없음)
- 12:05:00
- 12:08:00
실제 테이블은 PostgreSQL 데이터베이스에 있으며 15 개 센서에 대한 약 5 백만 개의 레코드를 포함합니다.
테스트 데이터
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)
;
내 접근
임의의 센서 2 개를 선택하고 (특정 센서 ID에 의해) 센서 1의 레코드에 대해 자체 결합을 만들고 이전 타임 스탬프가있는 센서 2의 레코드 만 유지하는 것입니다 (센서 1의 타임 스탬프 <= 센서 2의 타임 스탬프가있는 센서 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
;
그러나 이는 12:00:00센서 2에 데이터가없는 (큰 문제가 아님) 쌍을 반환
하고 실제 테이블에서 명령문 실행이 몇 시간 후에 끝나지 않습니다 (큰 문제).
비슷한 질문을 찾았지만 내 문제와 일치하지 않습니다.
- 가장 가까운 날짜보다 작은 SQL 조인
- SQL 타임 스탬프 및 인벤토리 수준을 기반으로 동일한 테이블 조인
미리 감사드립니다!
답변
2 GordonLinoff Aug 17 2020 at 00:30
첫 번째 단계는 각 타임 스탬프에서 차이를 계산하는 것입니다. 한 가지 방법은 측면 조인 및 조건부 집계를 사용합니다.
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;
이제 문제는 차이가 지역 최소값을 입력하는시기입니다. 표본 데이터의 경우 국소 최솟값은 모두 한 시간 단위입니다. 즉, 다음을 사용 lag()하고 lead()찾을 수 있습니다.
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);
그것은 합리적인 가정이 아닐 수도 있습니다. 따라서이 논리를 적용하기 전에 인접한 중복 값을 필터링하십시오.
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)
여기 db <> 바이올린이 있습니다.
2 TheImpaler Aug 16 2020 at 16:14
두 개의 측면 결합을 사용할 수 있습니다. 예를 들면 :
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
결과:
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
DB Fiddle 에서 실행 예제를 참조하십시오 .
또한 모든 타임 스탬프 를 원하는 경우 ,, 같은 12:00:00일치하지 않는 타임 스탬프 도 다음과 같이 할 수 있습니다.
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
이 경우 거리를 계산할 수는 없습니다.
결과:
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
1 MikeOrganek Aug 16 2020 at 16:22
누락 된 값을 채우려면 창 함수와 두 개의 센서와 교차하는 매분의 데카르트 곱이 필요합니다.
invarsCTE는 매개 변수를 받아들입니다.
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
),
minutex 의 행렬 만들기sensor_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)
),
찾기 mgrp새로운 값이에서 사용할 수있는 모든 시간을sensor_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
),
가장 최근 값을 이월하여 누락 된 값을 보간합니다.
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
)
수행 distance계산 ( sum()수 있었다 max()또는 min()--it 차이가 없습니다.
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;
결과 :
| 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)
이 작동하는 바이올린을 참조하십시오 .
1 wildplasser Aug 16 2020 at 16:40
창 기능 및 neigbors 확인. (중복을 제거하고 안정적인 결혼 문제에 대한 타이 브레이커를 발명하려면 추가 안티 셀프 조인이 필요합니다 )
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
;
결과:
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)