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).