基于日期时间列中的间隔时间阈值修改行的id值

k10s72fa 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(220)

我正在研究 geolife 数据集，其中包含文本文件中用户的时间戳gps跟踪( .plt ). 每个文本文件包含用户一次旅行的gps点。因此，我将数据集导入 postgres 使用python脚本。
因为文件是根据行程的开始时间用数字字符串命名的（例如，下表中包含行程的文件是 20070920074804.plt )，我给旅行证件( session_id )文件名（不带扩展名）。这是表中的原始gps trajectories .

user_id |    session_id     |       timestamp        |    lat    |    lon     | alt 
---------+-------------------+------------------------+-----------+------------+-----
      11 |    20070920074804 | 2007-09-20 07:48:04+01 |  28.19737 | 113.006795 |  71
      11 |    20070920074804 | 2007-09-20 08:07:09+01 | 28.197685 | 113.006792 |  87
      11 |    20070920074804 | 2007-09-20 08:07:10+01 | 28.197685 |  113.00679 |  87
      11 |    20070920074804 | 2007-09-20 14:03:50+01 | 28.197342 | 113.007422 |  62
      11 |    20070920074804 | 2007-09-20 14:04:59+01 | 28.197108 |  113.00734 |  62
      11 |    20070920074804 | 2007-09-20 14:05:01+01 | 28.197088 |  113.00727 |  62

出于分析目的，我创建了另一个表 trips_metrics 我从中计算出行指标 trajectories 并将结果插入到 trip_metrics . 在我计算的值中，有行程距离( haversine )和持续时间( start time - end time ).
然后我注意到一些奇怪的事情，一个用户 8hrs 旅行的距离 321m . 仔细查看trip文件，我注意到trip的时间出现了跳跃，建议中断trip（可能用户停留几个小时然后继续）。例如 row 3 以及 row 4 在上表中。
为了获得准确的行程时间，我需要对这些情况下的行程进行拆分，如果连续行之间的时间间隔超过30分钟，则应将其视为新的行程（因此是新的id）。
我打算加数字 ..02, ..03, .. 到行程的电流 session_id 在我的 trajectories 实际计算出行指标前的表（即修改 trajectories 表）。因此，对于上表中的示例，我想按以下方式拆分：

user_id |    session_id     |       timestamp        |    lat    |    lon     | alt 
---------+-------------------+------------------------+-----------+------------+-----
      11 |  20070920074804   | 2007-09-20 07:48:04+01 |  28.19737 | 113.006795 |  71
      11 |  20070920074804   | 2007-09-20 08:07:09+01 | 28.197685 | 113.006792 |  87
      11 |  20070920074804   | 2007-09-20 08:07:10+01 | 28.197685 |  113.00679 |  87
      11 |  2007092007480402 | 2007-09-20 14:03:50+01 | 28.197342 | 113.007422 |  62
      11 |  2007092007480402 | 2007-09-20 14:04:59+01 | 28.197108 |  113.00734 |  62
      11 |  2007092007480402 | 2007-09-20 14:05:01+01 | 28.197088 |  113.00727 |  62

注意我如何分配 session_id 对于新的行程（因为间隔时间超过30分钟）。
如何修改或更改原始gps表( trajectories )在 postgres ?
编辑
答：来自@gmb的答案中的第一个查询是有效的，但是它会给每一行i一个新的值 session_id 在 new_session_id 列。

+---------+----------------+------------------------+-----------+------------+-----+--------+------------------+
| user_id |   session_id   |       timestamp        |    lat    |    lon     | alt | is_gap |  new_session_id  |
+---------+----------------+------------------------+-----------+------------+-----+--------+------------------+
|      11 | 20070920074804 | 2007-09-20 07:48:04+01 | 28.19737  | 113.006795 |  71 |        |   20070920074804 |
|      11 | 20070920074804 | 2007-09-20 08:07:09+01 | 28.197685 | 113.006792 |  87 |      1 | 2007092007480401 |
|      11 | 20070920074804 | 2007-09-20 08:07:10+01 | 28.197685 | 113.00679  |  87 |      1 | 2007092007480402 |
|      11 | 20070920074804 | 2007-09-20 14:03:50+01 | 28.197342 | 113.007422 |  62 |      1 | 2007092007480403 |
|      11 | 20070920074804 | 2007-09-20 14:04:59+01 | 28.197108 | 113.00734  |  62 |      1 | 2007092007480404 |
|      11 | 20070920074804 | 2007-09-20 14:05:01+01 | 28.197088 | 113.00727  |  62 |      1 | 2007092007480405 |
+---------+----------------+------------------------+-----------+------------+-----+--------+------------------+

预期结果：

+---------+----------------+------------------------+-----------+------------+-----+--------+------------------+
| user_id |   session_id   |       timestamp        |    lat    |    lon     | alt | is_gap |  new_session_id  |
+---------+----------------+------------------------+-----------+------------+-----+--------+------------------+
|      11 | 20070920074804 | 2007-09-20 07:48:04+01 | 28.19737  | 113.006795 |  71 |        |   20070920074804 |
|      11 | 20070920074804 | 2007-09-20 08:07:09+01 | 28.197685 | 113.006792 |  87 |        |   20070920074804 |
|      11 | 20070920074804 | 2007-09-20 08:07:10+01 | 28.197685 | 113.00679  |  87 |      1 | 2007092007480401 |
|      11 | 20070920074804 | 2007-09-20 14:03:50+01 | 28.197342 | 113.007422 |  62 |      1 | 2007092007480401 |
|      11 | 20070920074804 | 2007-09-20 14:04:59+01 | 28.197108 | 113.00734  |  62 |      1 | 2007092007480401 |
|      11 | 20070920074804 | 2007-09-20 14:05:01+01 | 28.197088 | 113.00727  |  62 |      1 | 2007092007480401 |
+---------+----------------+------------------------+-----------+------------+-----+--------+------------------+

这个想法是给“新兴”之旅一个新的身份证 old_session_id + 01 . 如果遇到另一个新出现的行程，则应分配该行程 old_session_id + 02 等等。
b:第二个带update选项的查询包含语法错误：

update trajectories t
from (
    select 
        t.*,
        case when sum(is_gap) over(partition by session_id order by timestamp) > 0
            then session_id * 100 + sum(is_gap) over(partition by session_id order by timestamp)
            else session_id
        end new_session_id
    from (
        select
            t.*,
            (timestamp > lag(timestamp) over(partition by session_id order by timestamp))::int is_gap
        from trajectories t
    ) t
) t1
set session_id = t1.new_session_id
where t1.session_id = t.session_id and t1.timestamp = t.timestamp

ERROR:  syntax error at or near "from"
LINE 2: from (

sql postgresql window-functions Date gaps-and-islands

来源：https://stackoverflow.com/questions/62440276/modifying-the-id-value-of-a-row-based-on-interval-time-threshold-in-date-time-co

2条答案

按热度按时间

pwuypxnk1#

你可以用 lag() ，一个累加的总和来标识分段，然后用某种方法来咀嚼 session_id :

select (case when grp >= 1 then session_id * 100 + grp
             else session_id
        end) as new_session_id,
       t.*
from (select t.*,
             count(*) filter (where prev_ts < timestamp - interval '30 minute') over (partition by session_id, order by timestamp) as grp
      from (select t.*, 
                   lag(timestamp) over (partition by session_id order by timestamp) as prev_ts
            from trajectories t
           ) t
     ) t;

这是一把小提琴。

赞(0）回复(0）举报 2021-07-26

cedebl8k2#

这是一个缺口和孤岛问题。要检测时间戳差异大于30分钟的连续行，然后更改 session_id 相应地。
一种选择是使用 lag() ，然后是间隔的累计计数-然后可以使用该信息计算新的 session_id :

select 
    t.*,
    case when sum(is_gap) over(partition by session_id order by timestamp) > 0
        then session_id * 100 + sum(is_gap) over(partition by session_id order by timestamp)
        else session_id
    end new_session_id
from (
    select
        t.*,
        (timestamp > lag(timestamp) over(partition by session_id order by timestamp))::int is_gap
    from trajectories t
) t

你可以把它变成 update 声明（如需要）：

update trajectories t
set session_id = t1.new_session_id
from (
    select 
        t.*,
        case when sum(is_gap) over(partition by session_id order by timestamp) > 0
            then session_id * 100 + sum(is_gap) over(partition by session_id order by timestamp)
            else session_id
        end new_session_id
    from (
        select
            t.*,
            (timestamp > lag(timestamp) over(partition by session_id order by timestamp))::int is_gap
        from trajectories t
    ) t
) t1
where t1.session_id = t.session_id and t1.timestamp = t.timestamp

赞(0）回复(0）举报 2021-07-26

我来回答

基于日期时间列中的间隔时间阈值修改行的id值

2条答案

相关问题

热门标签

最新问答