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)