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
एक सेंसर का मान उसके टाइमस्टैम्प से उसके अगले रिकॉर्ड (उसी सेंसर_ड) के टाइमस्टैम्प तक मान्य होता है।
सचित्र प्रदर्शन
निचली हरी रेखा समय के साथ सेंसर 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 मनमाना सेंसर (कुछ सेंसर_डायस द्वारा) चुनना था, किसी भी सेंसर 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 निकटतम तिथि से कम पर सम्मिलित हों
- एसक्यूएल स्टांप और इन्वेंट्री स्तर के आधार पर एक ही तालिका में शामिल हों
अग्रिम में धन्यवाद!
जवाब
पहला कदम प्रत्येक टाइमस्टैम्प में अंतर की गणना करना है। एक विधि पार्श्व जुड़ने और सशर्त एकत्रीकरण का उपयोग करती है:
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 <> फिडल है।
आप पार्श्व जोड़ों के एक जोड़े का उपयोग कर सकते हैं। उदाहरण के लिए:
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
अनुपलब्ध मानों के उल्लंघन के लिए विंडो फ़ंक्शंस और आपके दो सेंसर के साथ पार किए गए हर मिनट के कार्टेशियन उत्पाद की आवश्यकता होती है।
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
X का मैट्रिक्स बनाएँ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()
-यह कोई फर्क नहीं पड़ता।
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)
कृपया इस काम करने वाली बेला देखें ।
विंडो कार्य करता है और निगबोर की जांच करता है। (आपको डुप्लिकेट को निकालने के लिए एक अतिरिक्त एंटी-सेजॉइन की आवश्यकता होगी, और स्थिर विवाह समस्या के लिए टाई-ब्रेकर का आविष्कार करना होगा )
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)