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

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秒，最小值是15秒，平均值是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窗口函数确定时间戳之间的间隔，中间的查询汇总所有小于60s阈值的持续时间，并按小时和设备分组。然后，最外层的查询提取最大、最小和平均停留时间(以秒为单位)，并显示每小时。

下面是一个基于我的测试数据的示例结果

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

Answer 2

这似乎回答了你的问题，至少对于一个设备和区域:

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
ORDER BY longest_period DESC
LIMIT 1;

您可以轻松地在ts上添加任何您想要的限制。