SQL - Ayuda consulta SQL

 
Vista:

Ayuda consulta SQL

Publicado por Rodrigo (3 intervenciones) el 04/02/2015 19:40:33
Hola,

Quiero hacer una consulta SQL en mi base de datos pero no logro poder obtener el resultado deseado.

Tengo una tabla de log como la siguiente:

Tabla LOG:
Usuario Terminal Accion Fecha
A 1 k 12:31
B 2 j 12:32
A 1 h 12:33
A 1 logout 12:34
B 2 z 12:34
B 3 y 12:34
B 3 x 12:35
B 3 Logout 12:37
B 2 Logout 15:30

De la tabla quisiera sacar el tiempo que pasó desde la última acción del usuario en una terminal antes del logout (cierre de sesión).

El resultado que se quiere es:
Usuario A en terminal 1, desde la última acción hasta el logout pasó 1 minuto
Usuario B en terminal 2, desde la última acción hasta el logout pasaron 176 minutos
Usuario B en terminal 3, desde la última acción hasta el logout pasaron 2 minutos

Creo que es bastante clara la explicacion..
Se me hace dificil, me pueden ayudar?
Graciassssss infinitas



Un intento fallido que estaba probando:

select cierresession.usuario, cierresession.terminal, cierresession.accion, cierresession.fecha, MAX(acciones.fecha)

FROM
(select usuario, terminal, accion, fecha
FROM LOG log1
where accion = logout) cierresession,
(select usuario, terminal, accion, fecha
FROM LOG log2
where NOT (accion = 70 )) acciones

GROUP BY cierresession.usuario, cierresession.terminal, cierresession.accion, cierresession.fecha, acciones.fecha
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
sin imagen de perfil
Val: 806
Bronce
Ha mantenido su posición en SQL (en relación al último mes)
Gráfica de SQL

Ayuda consulta SQL

Publicado por leonardo_josue (1173 intervenciones) el 04/02/2015 21:56:31
Hola Rodrigo:

Creo que te estás complicando la vida... entre más simple sea tu consulta, será más probable que obtengas un resultado favorable.

Pero antes de comenzar con la explicación, deberías de aclararnos algunas cosas.

1. ¿Con qué BD's estás trabajando?, aunque la mayoría de los DBMS's están basados en SQL estándar, hay diferencias significativas en cuanto al manejo de funciones de fecha, por lo que es imposible darte una respuesta puntual.
2. ¿Qué tipo de dato almacenas en tu campo fecha? puedo suponer que se trata de un tipo DATETIME, pero eso sólo lo saben Dios y tú. Simplemente espero que no estés manejando este campo como varchar.
3. ¿Los tiempos pertenecen siempre a un mismo día o fecha?
4. ¿Un usuario tiene sólo un logout para cada una de las terminales o puede tener varios?
5. Si un usuario puede tener varios logout's, ¿te interesa la información de todos o sólo de la última?

Partiendo de lo que puedes responder a estas preguntas, hay muchas formas de obtener lo que quieres. Partamos de los datos que tienes de ejemplo y veamos si te puede servir de algo. El problema lo puedes plantear de muchas formas, pero en resumen se trata de encontrar dos registros:

Primero el registro que corresponde al logout para un usuario, en una terminar en específica.
Segundo, el último registro para una acción (distinta al logout) para ese usuario y esa terminal, ANTES DEL LOGOUT.

Entonces, esto lo puedes resolver con una subconsulta.

Primero obtienes todos los logout's para los usuarios y las terminales.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> SELECT * FROM tabla;
+---------+----------+--------+----------+
| Usuario | Terminal | Accion | Fecha    |
+---------+----------+--------+----------+
| A       |        1 | k      | 12:31:00 |
| B       |        2 | j      | 12:32:00 |
| A       |        1 | h      | 12:33:00 |
| A       |        1 | logout | 12:34:00 |
| B       |        2 | z      | 12:34:00 |
| B       |        3 | y      | 12:34:00 |
| B       |        3 | x      | 12:35:00 |
| B       |        3 | logout | 12:37:00 |
| B       |        2 | logout | 15:30:00 |
+---------+----------+--------+----------+
9 rows in set (0.00 sec)
 
mysql> SELECT usuario, terminal, fecha FROM tabla WHERE accion = 'logout';
+---------+----------+----------+
| usuario | terminal | fecha    |
+---------+----------+----------+
| A       |        1 | 12:34:00 |
| B       |        3 | 12:37:00 |
| B       |        2 | 15:30:00 |
+---------+----------+----------+
3 rows in set (0.00 sec)

Ahora, para cada registro obtienes la actividad anterior... esto puedes hacerlo con una subconsulta:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> SELECT
    ->    T1.usuario,
    ->    T1.terminal,
    ->    ( SELECT MAX(T2.fecha)
    ->      FROM tabla T2
    ->      WHERE T1.usuario = T2.usuario AND
    ->            T1.terminal = T2.terminal AND
    ->            T2.accion != 'logout' AND
    ->            T2.fecha <= T1.fecha) fecha_ultima_accion,
    ->    T1.fecha fecha_logout
    -> FROM tabla T1
    -> WHERE T1.accion = 'logout';
+---------+----------+---------------------+--------------+
| usuario | terminal | fecha_ultima_accion | fecha_logout |
+---------+----------+---------------------+--------------+
| A       |        1 | 12:33:00            | 12:34:00     |
| B       |        2 | 12:34:00            | 15:30:00     |
| B       |        3 | 12:35:00            | 12:37:00     |
+---------+----------+---------------------+--------------+
3 rows in set (0.00 sec)

Observa que la CONSULTA EXTERNA es la misma que puse al inicio, la subconsulta, simplemente obtiene la MAX(fecha) (es decir, la última accion) que para el usuario y la terminal, que haya ocurrido ANTES DEL LOGOUT.
Ahora, con estos datos, simplemente tengo que obtener la diferencia entre las fechas para saber cuanto tiempo transcurrió.

Eso te toca hacerlo a tí, pues depende completamente del motor de BD's que estés utilizando. haz la prueba y nos comentas.

Saludos
Leo.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Ayuda consulta SQL

Publicado por Rodrigo (3 intervenciones) el 05/02/2015 02:23:59
Hola Leo, muchas gracias por tu respuesta..

Respondo a tus consultas:

1. ¿Con qué BD's estás trabajando?, aunque la mayoría de los DBMS's están basados en SQL estándar, hay diferencias significativas en cuanto al manejo de funciones de fecha, por lo que es imposible darte una respuesta puntual.
Oracle
2. ¿Qué tipo de dato almacenas en tu campo fecha? puedo suponer que se trata de un tipo DATETIME, pero eso sólo lo saben Dios y tú. Simplemente espero que no estés manejando este campo como varchar.
Datetime
3. ¿Los tiempos pertenecen siempre a un mismo día o fecha?
No siempre, hay veces que pueden ser de un día al otro
4. ¿Un usuario tiene sólo un logout para cada una de las terminales o puede tener varios?
Tiene sólo logout después de un login, osea que puede tener varios logout por dia ya que puede iniciar sesión varias veces el usuario
5. Si un usuario puede tener varios logout's, ¿te interesa la información de todos o sólo de la última?
De todos. Ya que la consulta la necesito para saber si los usuarios se desloguearon o dejaron el equipo con la sesión abierta (después de tener la consulta haría una consulta para buscar los deslogueos que tuvieron una demora de más de 30 minutos por ejemplo)

Probaré tu consulta y te cuento!
Por lo que pude ver, puede ser que de error, ya que oracle pide que se haga un group by cuando se hace la funcion MAX.

saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Ayuda consulta SQL

Publicado por Rodrigo (3 intervenciones) el 05/02/2015 15:33:58
Hola de nuevo!

Me funciono bien al parecer.
Ahora intento restar fecha_logout menos fecha_ultima_accion pero me da 0,0003234 por ejemplo..

Intente poner

to_date(fecha_ultima_accion, 'DD/MM/YYYY HH24:MI:SS') - to_date(fecha_logout, 'DD/MM/YYYY HH24:MI:SS')

y me da error:

ORA-01861: literal does not match format string

A que se puede deber esto?
Copio un ejemplo de los valores de fecha que tengo:
24/10/2014 1:45:22
24/10/2014 2:35:29
24/10/2014 3:45:21
24/10/2014 10:58:22
24/10/2014 11:12:56

Sera porque la hora es con una cifra y con dos cifras en algunos casos? :/

Gracias desde yaa
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar