Tengo una tabla en PostgreSQL que contiene los puntos GPS de los teléfonos móviles. Tiene una columna de números enteros que almacena la época (el número de segundos desde 1960). Quiero ordenar la tabla según el tiempo (columna de época), luego, dividir los viajes a sub viajes cuando no hay registro de GPS durante más de 2 minutos.

Lo hice con GeoPandas. Sin embargo, es demasiado lento. Quiero hacerlo dentro de PostgreSQL. ¿Cómo puedo comparar cada fila de la tabla ordenada con la fila anterior (para ver si la época tiene una diferencia de 2 minutos o más)? De hecho, no sé cómo comparar cada fila con la fila superior.

enter image description here

0
milad 14 mar. 2021 a las 00:30

2 respuestas

La mejor respuesta

Quiero ordenar la tabla según el tiempo (columna de época), luego, dividir los viajes a sub viajes cuando no hay registro de GPS durante más de 2 minutos.

Después de comparar con la fila anterior (o siguiente), con la función de ventana lag() (o lead()), forme grupos basados ​​en los espacios para obtener números de sub viajes:

SELECT *, count(*) FILTER (WHERE step) OVER (PARTITION BY trip ORDER BY timestamp_epoch) AS sub_trip
FROM  (
   SELECT *
       , (timestamp_epoch - lag(timestamp_epoch) OVER (PARTITION BY trip ORDER BY timestamp_epoch)) > 120 AS step
   FROM   tbl
   ) sub;

Lectura adicional:

1
Erwin Brandstetter 15 mar. 2021 a las 00:16

Puedes usar lag():

select t.*
from (select t.*, 
             lag(timestamp_epoch) over (partition by trip order by timestamp_epoch) as last_timestamp_epoch
      from t
     ) t
where last_timestamp_epoch < timestamp_epoch - 120
2
Gordon Linoff 13 mar. 2021 a las 21:32