时间戳

首页 > 解决方案 > SQL：如何确定同一位置的对象有多长（雪花）

sql - SQL：如何确定同一位置的对象有多长（雪花）

问题描述

我正在尝试确定对象在同一位置的时间，以及在该时间段内它首次出现在该位置的时间。如果它离开该位置并返回，我需要另一行进行新的时间计算。

数据：

对象 ID 约会时间 纬度 朗
23 2021 年 5 月 2 日 12:00 38.09 20.99
23 2021 年 5 月 2 日 16:00 40.11 30.34
23 2021 年 5 月 2 日 23:00 40.11 30.34
23 2021 年 5 月 3 日 12:00 40.11 30.34
23 2021 年 5 月 3 日 16:00 40.11 30.34
23 2021 年 5 月 3 日 23:00 39.88 29.00
23 2021 年 5 月 4 日 12:00 39.88 29.00
23 2021 年 5 月 4 日 16:00 20.77 11.66
23 2021 年 5 月 4 日 23:00 40.11 30.34
23 2021 年 5 月 5 日 12:00 40.11 30.34
23 2021 年 5 月 5 日 16:00 20.77 11.66

期望的结果：

对象 ID 开始时间 纬度 朗 营业时间
23 2021 年 5 月 2 日 16:00 40.11 30.34 24:00
23 2021 年 5 月 3 日 23:00 39.88 29.00 13:00
23 2021 年 5 月 4 日 23:00 40.11 30.34 13:00

我的真实数据没有一致的 DateTime 频率，我在这里使用它以使其更易于阅读。真实数据也有多个 ObjectID。条目之间对象的位置被认为是未知的。

谢谢！

标签： sqllocationsnowflake-cloud-data-platformlatitude-longitude

解决方案

这是一种差距和孤岛问题。对于这个版本，行号的差异可能是最好的解决方案：

select objectid, lat, lng, min(datetime), max(datetime),
       datediff(minute, min(datetime), max(datetime)) / 60.0 as hours_diff
from (select t.*,
             row_number() over (partition by objectid, lat, lng order by datetime) as seqnum_2,
             row_number() over (partition by objectid order by datetime) as seqnum
      from t
     ) t
group by objectid, lat, lng, (seqnum - seqnum_2)
having count(*) > 1;

这将返回对象位于某个位置的每个时间段。

推荐阅读