SQL ค้นหาคู่ของแถวที่มีการประทับเวลาที่ดีที่สุดถัดไป
ความท้าทายของฉันคือการค้นหาคู่ของแถวที่อยู่ติดกันด้วยการประทับเวลาและเก็บเฉพาะคู่ที่มีระยะห่างน้อยที่สุดของฟิลด์ค่า (ค่าบวกของความแตกต่าง)
ตาราง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 ตัวที่ตรงกับลอจิกการประทับเวลา (เพื่อให้ได้เส้นสีเขียว)
- เพื่อค้นหาค่าอาหารขั้นต่ำในท้องถิ่นที่
- 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 ตัว (โดย sensor_ids บางตัว) ทำการรวมตัวเองและเก็บไว้สำหรับบันทึกของเซ็นเซอร์ 1 เฉพาะบันทึกของเซ็นเซอร์ 2 ที่มีการประทับเวลาก่อนหน้า (การประทับเวลาที่ใหญ่ที่สุดของเซ็นเซอร์ 2 พร้อมการประทับเวลาของเซ็นเซอร์ 1 <= การประทับเวลาของเซ็นเซอร์ 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 เข้าร่วมตารางเดียวกันตามการประทับเวลาและระดับสินค้าคงคลัง
ขอบคุณล่วงหน้า!
คำตอบ
ขั้นตอนแรกคือการคำนวณความแตกต่างในการประทับเวลาแต่ละครั้ง วิธีหนึ่งใช้การรวมด้านข้างและการรวมตามเงื่อนไข:
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;
คำถามคือเมื่อใดที่ความแตกต่างจะป้อนค่าต่ำสุดในท้องถิ่น สำหรับข้อมูลตัวอย่างของคุณ minima ในเครื่องเป็นหน่วยเวลาเดียวทั้งหมด นั่นหมายความว่าคุณสามารถใช้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 <> fiddle
คุณสามารถใช้การรวมด้านข้างสองสามตัว ตัวอย่างเช่น:
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 ซอ
นอกจากนี้หากคุณต้องการการประทับเวลาทั้งหมดแม้แต่การประทับเวลาที่ไม่ตรงกัน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
การเติมค่าที่ขาดหายไปต้องใช้ฟังก์ชันของหน้าต่างและผลิตภัณฑ์คาร์ทีเซียนทุก ๆ นาทีที่ผ่านไปด้วยเซ็นเซอร์สองตัวของคุณ
invars
CTE ยอมรับพารามิเตอร์
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
),
สร้างเมทริกซ์ของminute
xsensor_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()
ไม่แตกต่างกัน
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)
โปรดดูซอที่ใช้งานได้นี้
ฟังก์ชั่นหน้าต่างและการตรวจสอบ 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)