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 (เส้นสีแดง) เมื่อเวลาผ่านไป

จุดมุ่งหมายของฉันคือ

  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 เข้าร่วมตารางเดียวกันตามการประทับเวลาและระดับสินค้าคงคลัง

ขอบคุณล่วงหน้า!

คำตอบ

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;

คำถามคือเมื่อใดที่ความแตกต่างจะป้อนค่าต่ำสุดในท้องถิ่น สำหรับข้อมูลตัวอย่างของคุณ 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

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 ซอ

นอกจากนี้หากคุณต้องการการประทับเวลาทั้งหมดแม้แต่การประทับเวลาที่ไม่ตรงกัน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
), 

สร้างเมทริกซ์ของminutexsensor_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

ฟังก์ชั่นหน้าต่างและการตรวจสอบ 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)