文章/答案/技术大牛

发布

社区首页 >问答首页 >SQL查询按内部连续距离低于阈值的列的最大集合分组

问SQL查询按内部连续距离低于阈值的列的最大集合分组
EN

Stack Overflow用户

提问于 2015-08-17 13:27:07

回答 1查看 82关注 0票数 2

--一个附加的要求--使问题复杂化。见下文

我有一个2列的表( time和location )。

╔════════╦══════════╗
║ time   ║ location ║
╠════════╬══════════╣
║ 9:10   ║  1       ║
║ 9:20   ║ 3        ║
║ 9:35   ║ 3        ║
║ 9:40   ║ 3        ║
║ 10:10  ║  2       ║
║ 10:20  ║  2       ║
║ 10:40  ║  2       ║
║ 11:30  ║ 7        ║
║ 11:50  ║  9       ║
╚════════╩══════════╝

我想把它转换成一个描述“trips”的新表，在这个表中，如果没有用于trip的activity，则结束hour (=在next hour中没有其他time行)。

也就是说，每一个trip都会有一行，并计算出一些值(总步行距离、总行程时间等)。

就像这样：

╔═════════╦════════════════════════╗
║ trip_id ║ total_walking_distance ║
╠═════════╬════════════════════════╣
║ 1       ║ 3 (=|1-3|+|3-2|)       ║
║ 2       ║ 7(=|2-7|+|7-9|)        ║
╚═════════╩════════════════════════╝

所以我想要'group by time'，但不是由不同的时间值，而是由一个更复杂的条件。难道不可能不诉诸程序语言吗？

按时间排序似乎使问题更容易解决，因为我们只需要知道连续行何时相距超过一个小时，但排序后仍然不清楚如何将其转换为查询。

在编程语言中，它只是按时间排序，然后依次遍历行，当时间差大于1时，我们从当前行程开始一直查看到当前行，并执行计算，然后重新初始化开始到下一行。据我所知，这在SQL语言中是不可能的。

精化

如果一些时间连续的行有相同的位置，我只想计数它们一次:如果一个连续的行在同一个位置，那么只有第一个行应该在结果中。

这是通过首先添加时间和位置的滞后()列，并删除相同位置或同一时间的时间连续行，然后天真地进行分区来完成的。

(类似于以下查询：)

select      time,location
into cleaned_from_duplicate_time_loc
from            (select info.*,
                time - lag(time) over (partition by id order by time)    as diff_time,
                loc- lag(loc) over (partition by id order by time)       as diff_loc
                from info)  
                with_consecutive_differences_of_location_and_time
where (diff_loc is null or diff_loc<>0) and (diff_time>interval '0 hour' or diff_time is null)


select with_trip_start_boolean.*, sum(is_start_of_trip) over(order by id, time) as trip_id
 into with_trip_id
from
    (SELECT auxiliary_table_with_lag_diffs.*,
            case when diff_time> interval '1 hour' or diff_time is null then 1 else 0 end as is_start_of_trip
    FROM 
        (   --adding time_diffs for each id separately
          select cleaned_from_duplicate_time_loc.*,
            time - lag(time) over (partition by id order by time) as diff_time
          from cleaned_from_duplicate_time_loc
        )
     auxiliary_table_with_lag_diffs

    ORDER BY id, time) 
    with_trip_start_boolean

然而，有一个复杂的问题导致了错误:如果一个人在同一个location中停留了一段时间，然后继续移动，并且从他静止的最后一行到下一行的hour还不到一个，那么我们应该在结果中对固定行进行两次计数，既作为上一次旅行的结束，也作为下一次旅行的开始。这使得我们不可能进行彻底的清理--第一步，因为我们失去了最后一个“复制”行的必要信息，以防它离继任者不到一小时的路程。

如何正确处理重复的连续位置开始和结束旅行的情况？

postgresql

sql

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-08-17 13:40:21

基本上，您需要的是带有一些日期算法的lag()函数。具体来说，您需要一个新旅行开始的条件的累积和：

select row_number() over (order by min(time)) as trip_num,
       min(time) as trip_start, max(time) as trip_end,
       count(*) as num_stops,
       count(distinct location) as num_locations
from (select sum(case when time > prev_time + interval '1 hour' then 1 else 0 end) over
                 (order by time) as grp
      from (select t.*,
                   lag(time) over (order by time) as prev_time
            from table t
           ) t
     ) t
group by grp;

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/32051669

复制

相似问题

问SQL查询按内部连续距离低于阈值的列的最大集合分组
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL查询按内部连续距离低于阈值的列的最大集合分组EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL查询按内部连续距离低于阈值的列的最大集合分组
EN