Transformación de las correspondientes horas de inicio y fin en filas a columnas.
Tengo una tabla en este formato:
Usuario | Hora | estado |
---|---|---|
Usuario 1 | 2021-12-24 14:00:00 | conectar |
Usuario 2 | 2021-12-24 14:05:23 | conectar |
Usuario 2 | 2021-12-24 14:10:11 | desconectar |
Usuario 2 | 2021-12-24 14:10:17 | conectar |
Usuario 1 | 2021-12-24 19:35:22 | desconectar |
Usuario 2 | 2021-12-25 01:10:40 | desconectar |
Me gustaría tener una tabla con conectar y desconectar como columnas, una fila para cada sesión desde conectar hasta la siguiente desconexión por usuario:
Usuario | conectar | desconectar |
---|---|---|
Usuario 1 | 2021-12-24 14:00:00 | 2021-12-24 19:35:22 |
Usuario 2 | 2021-12-24 14:05:23 | 2021-12-24 14:10:11 |
Usuario 2 | 2021-12-24 14:10:17 | 2021-12-25 01:10:40 |
Podría crear esto en MySQL, MariaDB o MSSQL, dependiendo de dónde sea más fácil hacerlo. ¿Es posible hacerlo como una vista? Gran complemento, pero no es absolutamente necesario: columna “duración” que muestre la duración de cada sesión desde conectar hasta desconectar.
Si es más fácil, los tiempos de conectar/desconectar podrían ser (mili)segundos a partir del 1/1/1970.
davy.ai
Sí, es posible crear una vista en SQL que transforme la tabla original en el formato deseado. Aquí tienes un ejemplo de consulta que funciona en MySQL:
Esta vista utiliza la declaración
CASE
para pivotar los estadosconectado
ydesconectar
en columnas. También calcula laduración
de cada sesión utilizando la funciónTIMEDIFF
. La vista agrupa las sesiones porUsuario
,DATE(Tiempo)
y un número de sesión, el cual se incrementa cada vez que se encuentra un nuevo estadoconectado
. La agrupación asegura que cada fila en el conjunto de resultados corresponda a una sola sesión. Por último, la vista ordena las sesiones porUsuario
y tiempo deconectar
.La variable
@sesión
se utiliza para hacer un seguimiento del número de sesión. Puedes inicializarla antes de ejecutar la consulta conSET @sesión:=0;
.Ten en cuenta que se utiliza la función
DATE
para agrupar las sesiones por fecha. Si deseas agrupar las sesiones por algún otro intervalo de tiempo (por ejemplo, hora, minuto), puedes reemplazarDATE(Tiempo)
por la función apropiada (por ejemplo,HOUR(Tiempo)
).Una vez que hayas creado la vista, puedes consultarla como una tabla regular:
Esto te dará la tabla transformada con las columnas
conectar
,desconectar
yduración
.