sql - 如何确定几何区域内的平均停留时间

Question

我有一个包含设备坐标时间序列(时间戳、x、y、device_id)的数据库。如何确定 SQL 中一个区域和时间范围内多个设备的最长停留时间？

这是我拥有的数据的示例(请注意，这不是定期的)

|         ts         |  x  |  y  | device_id 
---------------------+-----+-----+-------------
 2015-10-24 14:12:39 | 0.5 | 0.5 | 1
 2015-10-24 14:12:40 | 0.5 | 0.5 | 1
 2015-10-24 14:12:51 | 0.5 | 0.5 | 1
 2015-10-24 14:12:52 | 1.0 | 1.0 | 1
 2015-10-24 14:12:53 | 1.5 | 1.5 | 1
 2015-10-24 14:12:56 | 1.0 | 1.0 | 1
 2015-10-24 14:12:57 | 0.5 | 0.5 | 1
 2015-10-24 14:12:58 | 0.5 | 0.5 | 1
 2015-10-24 14:12:39 | 0.5 | 0.5 | 2
 2015-10-24 14:12:40 | 0.5 | 0.5 | 2
 2015-10-24 14:12:41 | 1.0 | 1.0 | 2
 2015-10-24 14:12:42 | 1.5 | 1.0 | 2
 2015-10-24 14:12:45 | 2.0 | 1.0 | 2
 2015-10-24 14:12:48 | 2.0 | 1.0 | 2
 2015-10-24 14:12:49 | 2.0 | 1.0 | 2

此处设备 1 在区域 (0.0, 0.0) 至 (1.0, 1.0) 内的停留时间为 12 秒(14:12:39 至 14:12:51)和 1 秒(14:12: 57 至 14:12:58)。设备 2 在同一区域内的停留时间为 2 秒(14:12:39 到 14:12:41)。最大值为 12 秒，最小值为 1 秒，平均值为 5 秒。

我最初的想法是对 device_id 上的数据进行分区以获取每个设备的集合，然后将 min(ts) 和 max(ts) 之间的持续时间确定为该设备的最长周期，然后对该周期进行 max在所有设备上使用以下作为子查询。

SELECT (max(ts)-min(ts)) as longest_period, device_id
FROM readings
WHERE x < 1.0 AND x > 0.0 AND y < 1.0 AND y > 0.0
GROUP BY device_id, ts

Answer 1

当我写这个问题时，我开始扩展我自己的想法，后来进行了一些网络搜索，并阅读了 Postgres 文档(顺便说一句，这很棒；)我创建了以下查询，它似乎执行得非常好并且做了我想做的想。我承认它比我预期的要复杂一些。

select
  hour,
  (extract(epoch FROM max(t2.duration)))::int as max_dwell_time,
  (extract(epoch FROM min(t2.duration)))::int as min_dwell_time,
  (extract(epoch FROM avg(t2.duration)))::int as avg_dwell_time
from
  (
    select
      t1.device_id,
      date_trunc('hour', t1.ts) as hour,
      sum(t1.duration) as duration
    from
      (
        select
          distinct(ts),
          device_id,
          lag(ts) over (order by ts) as prev_ts,
          ts - lag(ts) over (order by ts) as duration
        from
          spatial_readings
        where
          x < 10.0 and y < 10.0
        order by device_id, ts
      ) t1
    where t1.duration < interval '60 second'
    group by t1.device_id, date_trunc('hour', t1.ts)
    order by t1.device_id
  ) as t2
group by hour
order by hour;

最内层查询 (t1) 使用 postgres 窗口函数确定时间戳之间的差距，中间查询汇总所有小于 60 秒阈值的持续时间，并按小时和设备分组。最外层的查询然后提取最大、最小和平均停留时间(以秒为单位)并显示每小时。

这是基于我的测试数据的示例结果

hour                 | max_dwell_time | min_dwell_time | avg_dwell_time 
---------------------+----------------+----------------+----------------
...
2015-10-24 14:00:00  |             77 |             21 |             49
2015-10-24 15:00:00  |            177 |            109 |            143
2015-10-24 16:00:00  |              4 |              4 |              4
2015-10-24 17:00:00  |             41 |             41 |             41
2015-10-24 18:00:00  |             18 |             18 |             18
...