时间戳

首页 > 解决方案 > 如何在sql中只聚合邻居记录？

sql - 如何在sql中只聚合邻居记录？

问题描述

假设我有一个名为位置的表，它包含源位置的记录以及记录该位置的时间。

ID source_id ts 位置
1 3134 2021-02-21 20:48:08.488359 ...
2 3134 2021-02-21 20:48:09.123422 ...
... ... ... ...

ts 是时间戳的缩写，位置是 postgis 格式，但对于这个问题并不重要。

我还有另一个叫做区域的表：

ID 区域
1 ...
2 ...
... ...

该区域是 postgis 格式的多边形。

例如，我可以测试一个点是否在一个区域内

SELECT locations.source_id, zones.id, locations.ts
from locations inner join
     zones
     on ST_Contains(zones.area, locations.position);

会告诉我哪个来源在哪个区域。

我想要的是一个产生以下输出的查询：

位置.source_id zone.id 在_zone_time
3134 1 1 分钟
3134 2 4 分钟
3134 1 2 分钟
... ... ...

所以它会告诉我源 3134 在区域 1 中持续了 1 分钟，之后它在区域 2 中持续了 4 分钟，然后又在区域 1 中持续了 2 分钟。

我如何以这种方式实现聚合？

标签： sqlpostgresqlpostgisaggregation

解决方案

您可以添加一个标志，然后将其视为间隙和孤岛问题。目前尚不清楚如何time计算，但想法是：

select source_id, id, min(ts), max(ts), max(ts) - min(ts) as diff
from (select l.source_id, z.id, l.ts,
             row_number() over (partition by l.source_id order by l.ts) as seqnumm
             row_number() over (partition by l.source_id, z.id order by l.ts) as seqnum_2
      from locations l inner join
           zones
           on ST_Contains(z.area, l.position)
     ) lz
group by (seqnum - seqnum_2);

这做了一些假设：

  • 所有点都在一个区域内。或者至少，您只关心区域内的点。
  • 积分一次只能在一个区域中。
  • 在区域中花费的时间是在该区域中的最早记录和最后记录之间的差异。

所有这些都是对你的问题的一个相当合理的解释，但可以放宽。如果您的要求不同，我建议您提出一个新问题，明确说明问题的细节。样本数据和相应的期望结果非常有帮助。

推荐阅读