SQL Sonraki en iyi zaman damgası eşleşmesine sahip satır çiftlerini bulun

Aug 16 2020

Zorluğum, zaman damgasına göre bitişik olan satır çiftlerini bulmak ve yalnızca bir değer alanının minimum mesafesine sahip olan çiftleri tutmaktır (farkın pozitif değerleri)

Tablo measurement, zaman damgası ve değer içeren farklı sensörlerden veri toplar.

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

Bir sensörün değeri, zaman damgasından sonraki kaydın zaman damgasına kadar geçerlidir (aynı sensor_id).

Grafik gösterimi

Alttaki yeşil çizgi, sensör 1'lerin (mavi çizgi) ve sensör 2'nin (kırmızı çizgi) değerlerinin zaman içindeki mesafesini gösterir.

Amacım

  1. yalnızca zaman damgası mantığıyla eşleşen 2 sensörün kayıtlarını birleştirmek için (yeşil çizgiyi elde etmek için)
  2. yerel minimumları bulmak için
    • 12:01:00 (12:00:00 sensör 2 için kayıt yok)
    • 12:05:00
    • 12:08:00

Gerçek tablo bir PostgreSQL veritabanında bulunur ve 15 sensörün yaklaşık 5 milyon kaydını içerir.

Test verisi

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)
;

Benim yaklaşımım

2 rastgele sensör seçmek (belirli sensor_ids ile), kendi kendine birleştirme yapmak ve herhangi bir sensör 1'in kaydı için yalnızca sensör 2'nin önceki zaman damgası ile kaydını tutmaktı (sensör 1'in zaman damgası ile sensör 2'nin en büyük zaman damgaları <= sensör 2'nin zaman damgası) .

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
;

Ancak bu 12:00:00, sensör 2'nin verisinin olmadığı (büyük bir problem olmayan) bir çift döndürür
ve gerçek tabloda ifade yürütmesi saatler sonra bitmez (büyük problem).

Bazı benzer sorular buldum ama sorunumla uyuşmuyorlar

  • En yakın tarihte SQL Join
  • SQL, zaman damgası ve envanter seviyesine göre aynı tabloya katılın

Şimdiden teşekkürler!

Yanıtlar

2 GordonLinoff Aug 17 2020 at 00:30

İlk adım, her zaman damgasındaki farkı hesaplamaktır. Bir yöntem, yanal birleştirme ve koşullu toplama kullanır:

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;

Şimdi soru, farkın yerel minimuma ne zaman gireceğidir. Örnek verileriniz için yerel minimum değerlerin tümü bir zaman birimi uzunluğundadır. Bu, onları kullanabileceğiniz lag()ve lead()bulabileceğiniz anlamına gelir :

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);

Bu mantıklı bir varsayım olmayabilir. Bu nedenle, bu mantığı uygulamadan önce bitişik yinelenen değerleri filtreleyin:

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)

İşte bir db <> keman.

2 TheImpaler Aug 16 2020 at 16:14

Birkaç yan birleştirme kullanabilirsiniz. Örneğin:

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

Sonuç:

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'da çalışan örneğe bakın .

Ayrıca, tüm zaman damgalarını istiyorsanız , örneğin eşsiz olanlar dahil 12:00:00, şunları yapabilirsiniz:

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

Bu durumlarda mesafeyi hesaplamak mümkün değildir.

Sonuç:

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

Eksik değerlerin doldurulması, pencere işlevlerini ve iki sensörünüzle geçen her dakikanın Kartezyen çarpımını gerektirir.

invarsCte parametrelerini kabul eder.

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'in matrisini oluşturunsensor_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)
),

Bul mgrpbir yeni değer a edinilebilir her zamansensor_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
), 

En son değeri ileriye taşıyarak eksik değerleri enterpolate edin

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
)

distanceHesaplamayı gerçekleştirin ( sum()olabilirdi max()veya min()- hiçbir fark yaratmaz.

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;

Sonuçlar:

| 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)

Lütfen bu çalışan kemana bakın .

1 wildplasser Aug 16 2020 at 16:40

Pencere işlevleri ve komşuları kontrol etme. (kopyaları kaldırmak ve istikrarlı evlilik sorunu için bir bağ kırıcı icat etmek için fazladan bir anti-selfjoin'e ihtiyacınız olacak )


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
        ;

Sonuç:


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)