使用可以跨越一天的 DATETIME 標記對會話進行分組

我正在處理具有會話 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 

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

然後我使用 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
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在這裡

引用自：https://dba.stackexchange.com/questions/193347