Estoy tratando de obtener la diferencia de tiempo en minutos entre la hora del evento de los datos a continuación que tienen el mismo ID de nodo y el código para que el ID de nodo tenga un recuento> 1
nodeid code event_time CAI0015 14961045 2017-04-22 21:22:00 CAI0024 14961045 2017-04-23 19:44:00 CAI0024 14961045 2017-04-23 09:07:00 CAI0040 14971047 2017-04-23 13:58:00 CAI0046 14961045 2017-04-23 11:19:00 CAI0050 14961045 2017-04-24 02:06:00la salida debería ser así:
nodeid code difference(min) CAI0024 14961045 637Use la fórmula extract(epoch from <later> - <earlier>) / 60 para obtener la diferencia de marca de tiempo en minutos.
Para seleccionar todas las permutaciones de diferencias (donde el ID de nodeid y el code son iguales) dentro de la tabla, use una autocombinación:
select nodeid, code, extract(epoch from e2.event_time - e1.event_time) / 60 difference from events e1 join events e2 using (nodeid, code) where e1.event_time < e2.event_time Sin embargo, esto parece no ser realmente útil, cuando tiene más de 2 filas para un determinado nodeid de code e ID de nodo. Para calcular la diferencia solo para los anteriores, use la función de ventana lag() :
select nodeid, code, extract(epoch from event_time - lag) / 60 difference from (select *, lag(event_time) over (partition by nodeid, code order by event_time) from events) e where lag is not null Nota : ambos le darán solo una fila para cada par de ID de nodeid y code , si solo tiene un máximo de 2 filas para todos los pares.
intente usar dense_rank para encontrar el próximo evento.
SELECT t_a.*, EXTRACT(EPOCH FROM (t_a.event_time - t_b.event_time)) FROM (SELECT nodeid, code, event_time, dense_rank() over (partition by node_id order by event_time) as rnk FROM table) t_a JOIN (SELECT nodeid, code, event_time, dense_rank() over (partition by node_id order by event_time) as rnk FROM table) t_b ON (t_a.nodeid=t_b.nodeid and t_a.rnk + 1 = t_b.rnk)