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（赤い線）の値の経時的な距離を示しています。

私の目的は

タイムスタンプロジックに一致する2つのセンサーのレコードのみを組み合わせる（緑色の線を取得する）
距離の極小値を見つけるには
- 12:01:00（12:00:00にセンサー2のレコードはありません）
- 12:05:00
- 12:08:00

実際のテーブルはPostgreSQLデータベースにあり、15個のセンサーの約500万件のレコードが含まれています。

テストデータ

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

私のアプローチ

（特定のsensor_idsによって）2つの任意のセンサーを選択し、自己結合を作成して、センサー1のレコードに対して、前のタイムスタンプ（センサー1のタイムスタンプ<=センサー2のタイムスタンプを持つセンサー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結合
SQLタイムスタンプと在庫レベルに基づいて同じテーブルを結合します

前もって感謝します！

回答

2 GordonLinoff Aug 17 2020 at 00:30

最初のステップは、各タイムスタンプでの差を計算することです。1つの方法は、横方向結合と条件付き集計を使用します。

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;

ここで問題となるのは、差が極小値に入るのはいつかということです。サンプルデータの場合、極小値はすべて1時間単位の長さです。つまり、それらを使用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フィドル。

また、のような一致しないタイムスタンプも含めて、すべてのタイムスタンプ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

欠落値を埋めるには、ウィンドウ関数と、2つのセンサーと交差する毎分のデカルト積が必要です。

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

ウィンドウ関数とネイバーのチェック。（重複を削除し、安定結婚問題のタイブレーカーを発明するには、追加の反自己結合が必要になります）