Teniendo esta tabla a la mano:
SELECT * FROM mutable LIMIT 10; user_id | session_id | timestamp ---------+--------------------+------------------------ 180 | 179020080820120904 | 2008-08-20 12:09:04+01 180 | 179020080820120904 | 2008-08-20 12:09:07+01 180 | 179020080820120904 | 2008-08-20 12:10:35+01 180 | 179020080820120904 | 2008-08-20 12:10:37+01 180 | 179020080820120904 | 2008-08-20 12:10:39+01 180 | 179020080820120904 | 2008-08-20 12:10:41+01 180 | 179020080820120904 | 2008-08-20 12:10:43+01 180 | 179020080820120904 | 2008-08-20 12:10:45+01 180 | 179020080820120904 | 2008-08-20 12:10:47+01 180 | 179020080820120904 | 2008-08-20 12:10:49+01 (10 rows) Entonces, quiero modificar la tabla agregando una columna t_evolution que muestre el seguimiento de la duración de una ocurrencia de registro a la siguiente, considerando las columnas de timestamp de tiempo, así:
+---------------------------------------------------------+--------------------+ | user_id | session_id | timestamp | t_evolution | +---------------------------------------------------------+--------------------+ | 180 | 179020080820120904 | 2008-08-20 12:09:04+01 | 0 | | 180 | 179020080820120904 | 2008-08-20 12:09:07+01 | 3 | | 180 | 179020080820120904 | 2008-08-20 12:10:35+01 | 92 | | 180 | 179020080820120904 | 2008-08-20 12:10:37+01 | 94 | | 180 | 179020080820120904 | 2008-08-20 12:10:39+01 | 96 | | 180 | 179020080820120904 | 2008-08-20 12:10:41+01 | 98 | | 180 | 179020080820120904 | 2008-08-20 12:10:43+01 | 100 | | 180 | 179020080820120904 | 2008-08-20 12:10:45+01 | 102 | | 180 | 179020080820120904 | 2008-08-20 12:10:47+01 | 104 | | 180 | 179020080820120904 | 2008-08-20 12:10:49+01 | 106 | +---------------------------------------------------------+--------------------+ (10 rows)Puede restar la primera marca de tiempo de cada una de las marcas de tiempo y usar EXTRACT() para obtener la cantidad de segundos.
Con la función de ventana FIRST_VALUE() :
SELECT *, EXTRACT(EPOCH FROM ("timestamp" - FIRST_VALUE("timestamp") OVER (ORDER BY "timestamp"))) t_evolution FROM mutable En sus datos de muestra, todas las filas contienen el mismo valor para las columnas user_id y session_id . Si desea que la nueva columna realice un seguimiento de la duración de cada user_id de usuario y/o ID de session_id , puede cambiar la cláusula OVER a:
OVER (PARTITION BY user_id ORDER BY "timestamp")o:
OVER (PARTITION BY user_id, session_id ORDER BY "timestamp") Ver la demostración .
Resultados:
| user_id | session_id | timestamp | t_evolution | | ------- | ------------------ | ------------------------ | ----------- | | 180 | 179020080820120904 | 2008-08-20T12:09:04.000Z | 0 | | 180 | 179020080820120904 | 2008-08-20T12:09:07.000Z | 3 | | 180 | 179020080820120904 | 2008-08-20T12:10:35.000Z | 91 | | 180 | 179020080820120904 | 2008-08-20T12:10:37.000Z | 93 | | 180 | 179020080820120904 | 2008-08-20T12:10:39.000Z | 95 | | 180 | 179020080820120904 | 2008-08-20T12:10:41.000Z | 97 | | 180 | 179020080820120904 | 2008-08-20T12:10:43.000Z | 99 | | 180 | 179020080820120904 | 2008-08-20T12:10:45.000Z | 101 | | 180 | 179020080820120904 | 2008-08-20T12:10:47.000Z | 103 | | 180 | 179020080820120904 | 2008-08-20T12:10:49.000Z | 105 |