sql - vista - crear una columna de identificación basada en datos de actividad

crear vista con parametros sql server

Question

Tengo una mesa EVENTS

USER EVENT_TS EVENT_TYPE
abc 2016-01-01 08:00:00 Login
abc 2016-01-01 08:25:00 Stuff
abc 2016-01-01 10:00:00 Stuff
abc 2016-01-01 14:00:00 Login
xyz 2015-12-31 18:00:00 Login
xyz 2016-01-01 08:00:00 Logout

Lo que tengo que hacer es generar un campo de session para cada período de actividad para cada usuario. Además, si el usuario ha estado inactivo por un período igual o mayor que p_timeout (1 hora en este caso), entonces una nueva sesión comienza en la siguiente actividad. Los usuarios no siempre se desconectan de forma limpia, por lo que el cierre de sesión no es un paseo allí ...

Notas:

El cierre de sesión siempre termina una sesión
No tiene que ser un cierre de sesión o un inicio de sesión (porque el software)
El inicio de sesión es siempre una nueva sesión

Salida como

USER EVENT_TS EVENT_TYPE SESSION
abc 2016-01-01 08:00:00 Login 1
abc 2016-01-01 08:25:00 Stuff 1
abc 2016-01-01 10:00:00 Stuff 2
abc 2016-01-01 14:00:00 Login 3
xyz 2015-12-31 18:00:00 Login 1
xyz 2016-01-01 08:00:00 Logout 1

¿Alguna idea sobre cómo lograr esto?

Answer 1

Creo que esto hará el truco:

WITH EVENTS AS (SELECT ''abc'' usr, to_date(''2016-01-01 08:00:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''login'' event_type FROM dual UNION ALL
SELECT ''abc'' usr, to_date(''2016-01-01 08:25:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''Stuff'' event_type FROM dual UNION ALL
SELECT ''abc'' usr, to_date(''2016-01-01 10:00:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''Stuff'' event_type FROM dual UNION ALL
SELECT ''abc'' usr, to_date(''2016-01-01 14:00:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''login'' event_type FROM dual UNION ALL
SELECT ''xyz'' usr, to_date(''2015-12-31 18:00:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''login'' event_type FROM dual UNION ALL
SELECT ''xyz'' usr, to_date(''2016-01-01 08:00:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''Logout'' event_type FROM dual UNION ALL
SELECT ''def'' usr, to_date(''2016-01-01 08:00:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''Logout'' event_type FROM dual UNION ALL
SELECT ''def'' usr, to_date(''2016-01-01 08:15:00'', ''yyyy-mm-dd hh24:mi:ss'') event_ts, ''Logout'' event_type FROM dual)
SELECT usr,
event_ts,
event_type,
SUM(counter) OVER (PARTITION BY usr ORDER BY event_ts) session_id
FROM (SELECT usr,
event_ts,
event_type,
CASE WHEN LAG(event_type, 1, ''Logout'') OVER (PARTITION BY usr ORDER BY event_ts) = ''Logout'' THEN 1
WHEN event_type = ''Logout'' THEN 0
WHEN event_ts - LAG(event_ts) OVER (PARTITION BY usr ORDER BY event_ts) > 1/24 THEN 1
WHEN event_type = ''login'' THEN 1
ELSE 0
END counter
FROM EVENTS);
USR EVENT_TS EVENT_TYPE SESSION_ID
--- ------------------- ---------- ----------
abc 2016-01-01 08:00:00 login 1
abc 2016-01-01 08:25:00 Stuff 1
abc 2016-01-01 10:00:00 Stuff 2
abc 2016-01-01 14:00:00 login 3
def 2016-01-01 08:00:00 Logout 1
def 2016-01-01 08:15:00 Logout 2
xyz 2015-12-31 18:00:00 login 1
xyz 2016-01-01 08:00:00 Logout 1

Esta solución se basa en el cortocircuito lógico que tiene lugar en la expresión CASE y en el hecho de que event_type no es nulo. También asume que múltiples desconexiones en una fila se cuentan como sesiones separadas:

1. Si la fila anterior era una fila de cierre de sesión (y si no hay una fila anterior, es decir, para la primera fila del conjunto, trátela como si hubiera una fila de desconexión), queremos aumentar el contador en uno. (Los cierres de sesión finalizan la sesión, por lo que siempre tenemos una nueva sesión luego de un cierre de sesión).
2. Si la fila actual es un cierre de sesión, esto termina la sesión existente. Por lo tanto, el contador no debe aumentarse.
3. Si la hora de la fila actual es superior a una hora desde la fila anterior, aumente el contador en uno.
4. Si la fila actual es una fila de inicio de sesión, entonces es una nueva sesión, por lo tanto, incremente el contador en uno.
5. Para cualquier otro caso, no aumentamos el contador.

Una vez que hemos hecho eso, solo se trata de hacer un total acumulado en el mostrador.

Answer 2

Creo que esto puede hacer lo que necesites. Cambié "usuario" a "usr" en la entrada y "sesión" a "sess" en la salida; nunca uso palabras reservadas de Oracle para nombres de objetos.

Nota : como Boneist señaló a continuación, mi solución asignará un número de sesión de 0 a la primera sesión, si se trata de un evento de Logout (o una sucesión de Logout s derecho en la parte superior). Si esta situación puede ocurrir en los datos, y si el comportamiento deseado es iniciar el recuento de sesiones en 1 incluso en ese caso, entonces la definición de flag debe modificarse, por ejemplo, haciendo que flag = 1 cuando lag(event_ts) over (partition by usr order by event_ts) is null .

¡Buena suerte!

with
events ( usr, event_ts, event_type ) as (
select ''abc'', to_timestamp(''2016-01-01 08:00:00'', ''yyyy-mm-dd hh24:mi:ss''), ''Login'' from dual union all
select ''abc'', to_timestamp(''2016-01-01 08:25:00'', ''yyyy-mm-dd hh24:mi:ss''), ''Stuff'' from dual union all
select ''abc'', to_timestamp(''2016-01-01 10:00:00'', ''yyyy-mm-dd hh24:mi:ss''), ''Stuff'' from dual union all
select ''abc'', to_timestamp(''2016-01-01 14:00:00'', ''yyyy-mm-dd hh24:mi:ss''), ''Login'' from dual union all
select ''xyz'', to_timestamp(''2015-12-31 18:00:00'', ''yyyy-mm-dd hh24:mi:ss''), ''Login'' from dual union all
select ''xyz'', to_timestamp(''2016-01-01 08:00:00'', ''yyyy-mm-dd hh24:mi:ss''), ''Logout'' from dual
),
start_of_sess ( usr, event_ts, event_type, flag ) as (
select usr, event_ts, event_type,
case when event_type != ''Logout''
and
( event_ts >= lag(event_ts) over (partition by usr
order by event_ts) + 1/24
or event_type = ''Login''
or lag(event_type) over (partition by usr
order by event_ts) = ''Logout''
)
then 1 end
from events
)
select usr, event_ts, event_type,
count(flag) over (partition by usr order by event_ts) as sess
from start_of_sess
; 

Salida (las marcas de tiempo usan mi configuración NLS_TIMESTAMP_FORMAT actual):

USR EVENT_TS EVENT_TYPE SESS
--- --------------------------------- ---------- ------
abc 01-JAN-2016 08.00.00.000000000 AM Login 1
abc 01-JAN-2016 08.25.00.000000000 AM Stuff 1
abc 01-JAN-2016 10.00.00.000000000 AM Stuff 2
abc 01-JAN-2016 02.00.00.000000000 PM Login 3
xyz 31-DEC-2015 06.00.00.000000000 PM Login 1
xyz 01-JAN-2016 08.00.00.000000000 AM Logout 1
6 rows selected 

Answer 3

Para completar (para usuarios con Oracle 12 o superior), aquí hay una solución que utiliza MATCH_RECOGNIZE :

select usr, event_ts, event_type, sess
from events
match_recognize(
partition by usr
order by event_ts
measures match_number() as sess
all rows per match
pattern (strt follow*)
define follow as event_type = ''Logout''
or ( event_type != ''Login''
and prev(event_type) != ''Logout''
and event_ts < prev(event_ts) + 1/24
)
)
;

Aquí cubro un caso inusual: un evento de Logout después de otro evento de Logout . En tales casos, supongo que todas las sesiones de cierre de Logout consecutivas, independientemente de la cantidad y la distancia en el tiempo, pertenecen a la misma sesión. (Si se garantiza que estos casos no ocurrirán en los datos, tanto mejor).

Consulte también la Nota que agregué a mi otra respuesta (para Oracle 11 y abajo) con respecto a la posibilidad del primer evento para que un usr sea ​​un Logout (si eso es posible incluso en los datos de entrada).