sql-server - SQL Server : row_number partitioned by timeout

Question

我有一个包含一系列 (IP varchar(15), DateTime datetime2) 值的表。每一行对应于用户发出的 HTTP 请求。我想为这些行分配 session 编号。不同的IP地址有不同的 session 号。 如果最后一次请求超过 30 分钟，应为同一 IP 分配新的 session 号。以下是示例输出:

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)

第 1 列和第 2 列是输入，第 3 列和第 4 列是所需的输出。该表显示两个用户。

由于底层表确实很大，如何有效解决这个问题？我更喜欢少量恒定数量的数据传递(一次或两次)。

Answer 1

这里有一些尝试。

;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

这有两个排序操作(按 IP, DateTime 然后按 IP, IPSessionNumber)，但确实假设 SessionNumber 可以任意分配只要根据 IP 地址/30 分钟规则为每个新 session 分配不同的唯一 session 编号即可。

按时间顺序依次分配SessionNumber。我使用了以下内容。

;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

但这会将排序操作的数量增加到 4 个。