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

एक सेंसर का मान उसके टाइमस्टैम्प से उसके अगले रिकॉर्ड (उसी सेंसर_ड) के टाइमस्टैम्प तक मान्य होता है।

सचित्र प्रदर्शन

निचली हरी रेखा समय के साथ सेंसर 1 (नीली रेखा) और सेंसर 2 (लाल रेखा) मानों की दूरी दिखाती है।

मेरा उद्देश्य है

  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 निकटतम तिथि से कम पर सम्मिलित हों
  • एसक्यूएल स्टांप और इन्वेंट्री स्तर के आधार पर एक ही तालिका में शामिल हों

अग्रिम में धन्यवाद!

जवाब

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()-यह कोई फर्क नहीं पड़ता।

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

विंडो कार्य करता है और निगबोर की जांच करता है। (आपको डुप्लिकेट को निकालने के लिए एक अतिरिक्त एंटी-सेजॉइन की आवश्यकता होगी, और स्थिर विवाह समस्या के लिए टाई-ब्रेकर का आविष्कार करना होगा )


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)