Início / dba / Perguntas / 193347
Accepted
GWR
Asked: 2017-12-18 08:37:55 +0800 CST

Agrupando sessões usando um carimbo DATETIME que pode se estender por um DIA

  • 772

Estou trabalhando com dados que têm um valor de id de sessão que é reciclado ao longo do tempo (ids de sessão asp do IIS para ser exato).

Estou tentando dar a eles uma sequência para que cada instância de um ASP_SESSION_ID não seja agrupada.

Por exemplo, os dados ficariam assim. Mesma ID de sessão, usada em agosto de 2016 e novamente em março de 2017.

DTTM                        SESSION_ID
2016-08-29 14:24:28.450     297692378
2017-04-13 23:54:53.760     297692378
2017-04-13 23:59:53.477     297692378
2017-04-14 00:04:52.897     297692378
2017-04-14 00:04:53.790     297692378

A princípio, pensei em agrupar apenas na data (no nível DAY), mas para o exemplo acima, observe como a segunda instância do ID da sessão abrange a meia-noite. Isso causaria um 3º grupo, quando na verdade é a mesma sessão.

Então, se eu pudesse classificá-los corretamente, seria:

DTTM                        SESSION_ID      RANK
2016-08-29 14:24:28.450     297692378       1
2017-04-13 23:54:53.760     297692378       2
2017-04-13 23:59:53.477     297692378       2
2017-04-14 00:04:52.897     297692378       2
2017-04-14 00:04:53.790     297692378       2

Aqui, ASP_SESSION_ID deve ser considerado uma nova instância de uma sessão quando > 20 minutos se passaram desde a última solicitação.

Então, como posso agrupar ou classificar o mesmo ASP_SESSION_ID de maneira diferente à medida que eles são reutilizados ao longo do tempo? por exemplo, se a próxima solicitação desse ASP_SESSION_ID for > 20 minutos da última, agrupe-a/classifique-a de forma diferente?

Só não sei como atacar o problema.

Aqui estão algumas declarações para gerar os dados acima:

CREATE TABLE #TEST
(
DTTM DATETIME, 
SESSION_ID INT
)

INSERT INTO #TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378
sql-server t-sql

1 respostas

  1. Best Answer
    CREATE TABLE TEST
(
DTTM DATETIME, 
SESSION_ID INT
)

INSERT INTO TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378 union
select '2017-04-14 00:44:53.790', 297692378
GO

    Primeiro, adicionei um novo registro apenas para verificar se ele dura mais de 20 minutos.

    select '2017-04-14 00:44:53.790', 297692378

    Então eu adicionei uma nova coluna chamada RANKpara armazenar o resultado final.

    ALTER TABLE TEST ADD [RANK] int;

GO

    Eu usei a função de janela LAG() para calcular DATEDIFF entre a linha atual e a próxima.

    SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST
GO

    DTTM | SESSION_ID | DIF_MIN
:------------------ | ---------: | ------:
29/08/2016 14:24:28 | 297692378 | 0
13/04/2017 23:54:53 | 297692378 | 327450
13/04/2017 23:59:53 | 297692378 | 5
14/04/2017 00:04:52 | 297692378 | 5
14/04/2017 00:04:53 | 297692378 | 0
14/04/2017 00:44:53 | 297692378 | 40

    Então eu usei um CURSOR apenas para calcular o campo RANK. Basicamente ele acumula minutos entre os registros, até atingir 20 ou mais minutos.

    DECLARE @dttm datetime,
        @session_id int,
        @diff_min int,
        @acm_diff int,
        @rank int,
        @last_dttm datetime;

SET @diff_min = 0;
SET @acm_diff = 0;
SET @rank = 0;
SET @last_dttm = NULL;

DECLARE curMin CURSOR FAST_FORWARD  FOR
SELECT DTTM, SESSION_ID,
       DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN
FROM   TEST

OPEN curMin;
FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;

WHILE @@FETCH_STATUS = 0  
BEGIN
    IF @last_dttm IS NULL OR @acm_diff + @diff_min > 20
    BEGIN
        SET @rank = @rank + 1;
        SET @acm_diff = 0;
    END
    ELSE
    BEGIN
        SET @acm_diff = @acm_diff + @diff_min;
    END
    
    UPDATE TEST
    SET    [RANK] = @rank
    WHERE  DTTM = @dttm
    AND    SESSION_ID = @session_id;
    
    SET @last_dttm = @dttm;
    
    FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min;
END

CLOSE curMin;

SELECT   DTTM, SESSION_ID,
         DATEDIFF(minute, DTTM, COALESCE(LEAD(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM)) DIF_MIN,
         [RANK]
FROM     TEST 
ORDER BY DTTM, SESSION_ID;
GO

    DTTM | SESSION_ID | DIF_MIN | CLASSIFICAÇÃO
:------------------ | ---------: | ------: | ---:
29/08/2016 14:24:28 | 297692378 | 327450 | 1
13/04/2017 23:54:53 | 297692378 | 5 | 2
13/04/2017 23:59:53 | 297692378 | 5 | 2
14/04/2017 00:04:52 | 297692378 | 0 | 2
14/04/2017 00:04:53 | 297692378 | 40 | 2
14/04/2017 00:44:53 | 297692378 | 0 | 3

    dbfiddle aqui

    • 2

relate perguntas