SQL Tìm cặp hàng có dấu thời gian phù hợp nhất tiếp theo

Aug 16 2020

Thách thức của tôi là tìm các cặp hàng liền kề theo dấu thời gian và chỉ giữ lại các cặp đó với khoảng cách tối thiểu của trường giá trị (giá trị dương của sự khác biệt)

Một bảng measurementthu thập dữ liệu từ các cảm biến khác nhau với dấu thời gian và giá trị.

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

Giá trị của cảm biến có giá trị từ dấu thời gian của nó cho đến dấu thời gian của bản ghi tiếp theo (cùng sensor_id).

Biểu diễn đồ họa

Đường màu xanh lá cây phía dưới hiển thị khoảng cách của các giá trị của cảm biến 1 (đường màu xanh) và của cảm biến 2 (đường màu đỏ) theo thời gian.

Mục tiêu của tôi là

để chỉ kết hợp các bản ghi của 2 cảm biến phù hợp với logic dấu thời gian (để lấy đường màu xanh lục)
để tìm mức tối thiểu cục bộ bất thường tại
- 12:01:00 (lúc 12:00:00 không có bản ghi nào cho cảm biến 2)
- 12:05:00
- 12:08:00

Bảng thực nằm trong cơ sở dữ liệu PostgreSQL và chứa khoảng 5 triệu bản ghi của 15 cảm biến.

Dữ liệu thử nghiệm

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

Cách tiếp cận của tôi

là chọn 2 cảm biến tùy ý (bởi một số sensor_id nhất định), tự kết hợp và giữ lại bản ghi của cảm biến 1 chỉ ghi lại bản ghi của cảm biến 2 với dấu thời gian trước đó (dấu thời gian lớn nhất của cảm biến 2 với dấu thời gian của cảm biến 1 <= dấu thời gian của cảm biến 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
;

Nhưng điều đó trả về một cặp 12:00:00trong đó cảm biến 2 không có dữ liệu (không phải là vấn đề lớn)
và trên bảng thực, việc thực thi câu lệnh không kết thúc sau nhiều giờ (vấn đề lớn).

Tôi đã tìm thấy một số câu hỏi tương tự nhưng chúng không phù hợp với vấn đề của tôi

Tham gia SQL vào ngày gần nhất ít hơn
SQL Tham gia cùng một bảng dựa trên dấu thời gian và mức tồn kho

Cảm ơn trước!

Trả lời

2 GordonLinoff Aug 17 2020 at 00:30

Bước đầu tiên là tính toán sự khác biệt ở mỗi dấu thời gian. Một phương pháp sử dụng phép nối bên và phép kết hợp có điều kiện:

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;

Bây giờ câu hỏi là khi nào sự khác biệt trở thành mức tối thiểu cục bộ. Đối với dữ liệu mẫu của bạn, cực tiểu cục bộ đều dài một đơn vị thời gian. Điều đó có nghĩa là bạn có thể sử dụng lag()và lead()tìm chúng:

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

Đó có thể không phải là một giả định hợp lý để thực hiện. Vì vậy, hãy lọc ra các giá trị trùng lặp liền kề trước khi áp dụng logic này:

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)

Đây là một db <> fiddle.

2 TheImpaler Aug 16 2020 at 16:14

Bạn có thể sử dụng một vài phép nối bên. Ví dụ:

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

Kết quả:

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

Xem ví dụ đang chạy tại DB Fiddle .

Ngoài ra, nếu bạn muốn tất cả các dấu thời gian , ngay cả những dấu chưa từng có như thế 12:00:00, bạn có thể làm:

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

Tuy nhiên, trong những trường hợp đó, không thể tính khoảng cách.

Kết quả:

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

Việc điền các giá trị bị thiếu yêu cầu các chức năng cửa sổ và sản phẩm Descartes mỗi phút được kết hợp với hai cảm biến của bạn.

Các invarsCTE chấp nhận các thông số.

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

Tạo ma trận của 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)
),

Tìm mgrpmỗi khi có giá trị mới từ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
),

Nội suy các giá trị còn thiếu bằng cách chuyển tiếp giá trị gần đây nhất

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
)

Thực hiện distancephép tính ( sum()có thể là max()hoặc min()- nó không có gì khác biệt.

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;

Các kết quả:

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

Hãy xem trò chơi làm việc này .

1 wildplasser Aug 16 2020 at 16:40

Chức năng cửa sổ và kiểm tra neigbors. (bạn sẽ cần thêm một anti-self -join để loại bỏ các bản sao và phát minh ra dây buộc cho vấn đề hôn nhân ổn định )