¿Cómo realizar consulta mysql comparando registros de tiempo?
Probablemente la pregunta sea confusa pero aquí se las aclaro. Tengo una tabla con una columna datetime donde se almacena la fecha y hora del registro, los registros de la tabla tienen distinto origen es decir, la tabla concentra los registros de varios endpoints remotos.
El diseño de la tabla es más o menos así:
id endpoint numero_personas fecha_hora
1 50 12 02-08-2019 06:20:23
2 50 23 02-08-2019 06:21:34
3 51 10 02-08-2019 06:21:13
4 50 19 02-08-2019 06:22:26
La consulta que necesito hacer es con base en la columna datetime para nos de este resultado:
fecha_hora suma_personas_endpoint50 suma_personas_endpoint51
02-08-2019 06:20:00 12 0
02-08-2019 06:21:00 23 10
02-08-2019 06:22:00 19 0
Para este ejemplo se agrupa el tiempo cada 1 minuto pero lo que se busca que se se agrupe según las necesidades del usuario por ejemplo cada 1, 5, 10 min, etc. Hasta el momento ya logre hacer la agrupación de un solo endpoint pero no he podido agregar el del segundo. Así esta mi consulta:
SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)*60) as fecha_hora,SUM(numero_personas) as suma_personas_endpoint50 FROM tabla WHERE endpoint=50 GROUP BY fecha_hora ORDER BY fecha_hora
¿Cómo puedo agregar la agrupación del segundo endpoint pero respetando un orden de tiempo y en caso de que en algún minuto no existan registros ya sea de uno o de los dos endpoint aun así me muestre la fila en ceros?.
Algo que encontré que te puede orientar: https://stackoverflow.com/questions/7674786/mysql-pivot-table
Quise armar un ejemplo concreto pero no me dio el tiempo... la clave, creo, está en usar una tabla pivot
Aquí está el SQL:
SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)*60) as fecha_hora, SUM( CASE WHEN endpoint = 50 THEN numero_personas ELSE null END ) as suma_personas_ep50, SUM( CASE WHEN endpoint = 51 THEN numero_personas ELSE null END ) as suma_personas_ep51 FROM t GROUP BY FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)*60) ;
Lo que te conviene hacer (para extenderlo a cualquier cantidad de end_points) es generar el texto SQL desde PHP
Cambia los "null" por 0 para lograr exactamente el resultado buscado :)
