over - SQL Server: row_number particionado por timeout

sql get row_number

Question

Tengo una tabla con una serie de valores (IP varchar (15), DateTime datetime2). Cada fila corresponde a una solicitud HTTP hecha por un usuario. Quiero asignar números de sesión a estas filas. Diferentes direcciones IP tienen diferentes números de sesión. Al mismo IP se le debe asignar un nuevo número de sesión si la última solicitud es anterior a 30 minutos . Aquí hay un resultado de muestra:

IP,      DateTime,         SessionNumber, RequestNumber
1.1.1.1, 2012-01-01 00:01, 1,             1
1.1.1.1, 2012-01-01 00:02, 1,             2
1.1.1.1, 2012-01-01 00:03, 1,             3
1.1.1.2, 2012-01-01 00:04, 2,             1 --different IP => new session number
1.1.1.2, 2012-01-01 00:05, 2,             2
1.1.1.2, 2012-01-01 00:40, 3,             1 --same IP, but last request 35min ago (> 30min)

Las columnas 1 y 2 son entradas, 3 y 4 son las salidas deseadas. La tabla muestra dos usuarios.

Como la tabla subyacente es realmente grande, ¿cómo puede resolverse de manera eficiente ? Preferiría una pequeña cantidad constante de pases sobre los datos (uno o dos).

Answer 1

Aquí hay un par de intentos.

;WITH CTE1 AS
(
SELECT *,
IIF(DATEDIFF(MINUTE,
       LAG(DateTime) OVER (PARTITION BY IP ORDER BY DateTime),
       DateTime) < 30,0,1) AS SessionFlag
FROM Sessions
), CTE2 AS
(
SELECT *,
       SUM(SessionFlag) OVER (PARTITION BY IP 
                                  ORDER BY DateTime) AS IPSessionNumber
FROM CTE1
)
SELECT IP,
       DateTime,
       DENSE_RANK() OVER (ORDER BY IP, IPSessionNumber) AS SessionNumber,
       ROW_NUMBER() OVER (PARTITION BY IP, IPSessionNumber 
                              ORDER BY DateTime) AS RequestNumber
FROM CTE2

Esto tiene dos operaciones de clasificación (por IP, DateTime luego por IP, IPSessionNumber ) pero supone que el SessionNumber se puede asignar arbitrariamente siempre que se asigne un número de sesión único diferente a cada nueva sesión según la dirección IP / regla de 30 minutos.

Para asignar los SessionNumber s secuencialmente en orden cronológico. Usé lo siguiente.

;WITH CTE1 AS
(
SELECT *,
IIF(DATEDIFF(MINUTE,
       LAG(DateTime) OVER (PARTITION BY IP ORDER BY DateTime),
       DateTime) < 30,0,1) AS SessionFlag
FROM Sessions
), CTE2 AS(
SELECT *,
       SUM(SessionFlag) OVER (ORDER BY DateTime) AS GlobalSessionNo
FROM CTE1
), CTE3 AS(
SELECT *,
       MAX(CASE WHEN SessionFlag = 1 THEN GlobalSessionNo END) 
               OVER (PARTITION BY IP ORDER BY DateTime) AS SessionNumber
FROM CTE2)
SELECT IP,
       DateTime,
       SessionNumber,
       ROW_NUMBER() OVER (PARTITION BY SessionNumber 
                              ORDER BY DateTime) AS RequestNumber
FROM CTE3

Sin embargo, esto aumenta el número de operaciones de ordenamiento a 4.

Answer 2

Aquí hay una versión que usa una variable de tabla y row_number para crear una ID que se puede usar en un CTE recursivo. Podría valer la pena comparar el rendimiento con el cursor y una consulta (proporcionada por Martin).

CREATE TABLE #T
(
  IP varchar(15),
  DateTime datetime,
  ID int,
  primary key (IP, ID)
)

insert into #T(IP, DateTime, ID)
select IP, DateTime, row_number() over(partition by IP order by DateTime) 
from #sessionRequests

;with C as
(
  select IP,
         ID,
         DateTime,
         1 as Session
  from #T
  where ID = 1
  union all 
  select T.IP,
         T.ID,
         T.DateTime,
         C.Session + case when datediff(minute, C.DateTime, T.DateTime) >= 30 then 1 else 0 end
  from #T as T
    inner join C 
      on T.IP = C.IP and
         T.ID = C.ID + 1
)
SELECT IP,
       DateTime,
       dense_rank() over(order by IP, Session) as SessionNumber,
       row_number() over(partition by IP, Session order by DateTime) as RequestNumber
from C
order by IP, DateTime, SessionNumber, RequestNumber
option (maxrecursion 0)