主页 / dba / 问题 / 193347
Accepted
GWR
Asked: 2017-12-18 08:37:55 +0800 CST

使用可以跨越一天的日期时间戳对会话进行分组

  • 772

我正在处理具有随时间回收的会话 ID 值的数据（确切地说是来自 IIS 的 asp 会话 ID）。

我试图给他们一个序列，这样 ASP_SESSION_ID 的每个实例就不会组合在一起。

例如，数据看起来像这样。相同的会话 ID，在 2016 年 8 月使用，然后在 2017 年 3 月再次使用。

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

起初我想只按日期分组（在 DAY 级别），但对于上面的示例，请注意会话 ID 的第二个实例是如何跨越午夜的。这将导致第三组，而实际上是同一个会话。

因此，如果我能正确地对它们进行排名，那将是：

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

此处，当自上次请求以来超过 20 分钟后，ASP_SESSION_ID 应被视为会话的新实例。

那么，随着时间的推移，我如何对相同的 ASP_SESSION_ID 进行分组或对其进行不同的排名？例如，如果该 ASP_SESSION_ID 的下一个请求距离上一个请求 > 20 分钟，则将其分组/排名不同？

我只是不确定如何解决这个问题。

以下是生成上述数据的一些语句：

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 个回答

  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

    首先，我添加了一条新记录，只是为了检查它是否运行了 20 多分钟。

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

    然后我添加了一个名为RANK存储最终结果的新列。

    ALTER TABLE TEST ADD [RANK] int;

GO

    我使用 LAG() 窗口函数来计算当前行和下一行之间的 DATEDIFF。

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

    DTTM | 会话ID | DIF_MIN
:-------------------- | ----------: | ------:
29/08/2016 14:24:28 | 297692378 | 0
2017 年 4 月 13 日 23:54:53 | 297692378 | 327450
2017 年 4 月 13 日 23:59:53 | 297692378 | 5个
14/04/2017 00:04:52 | 297692378 | 5个
14/04/2017 00:04:53 | 297692378 | 0
2017 年 4 月 14 日 00:44:53 | 297692378 | 40

    然后我使用了一个 CURSOR 来计算 RANK 字段。基本上它会在记录之间累积分钟数，直到达到 20 分钟或更长时间。

    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 | 会话ID | DIF_MIN | 秩
:-------------------- | ----------: | ------: | ---：
29/08/2016 14:24:28 | 297692378 | 327450 | 1个
2017 年 4 月 13 日 23:54:53 | 297692378 | 5 | 2个
2017 年 4 月 13 日 23:59:53 | 297692378 | 5 | 2个
14/04/2017 00:04:52 | 297692378 | 0 | 2个
14/04/2017 00:04:53 | 297692378 | 40 | 2个
2017 年 4 月 14 日 00:44:53 | 297692378 | 0 | 3个

    dbfiddle在这里

    • 2

相关问题