sql-server - 将实例分配给滚动但重置 10 秒时间间隔

Question

测试数据设置:

create table #Data(ID INT IDENTITY PRIMARY KEY CLUSTERED, EventDateTime datetime, Instance int)

insert into #Data(EventDateTime)
values
('2018-10-16T01:37:23.173'),
('2018-10-16T01:37:31.447'),
('2018-10-16T01:37:36.577'),
('2018-10-16T01:37:45.457'),
('2018-10-16T01:37:48.860'),
('2018-10-16T01:38:06.407'),
('2018-10-16T01:38:11.030'),
('2018-10-16T01:38:15.470'),
('2018-10-16T01:38:19.133'),
('2018-10-16T01:38:27.830')

期望的输出(我能得到):

是否使用了古怪的更新:

declare @Instance int = 1
declare @StartDate datetime
select top 1 @StartDate = EventDateTime from #Data order by id asc

update t
set @Instance = Instance = case when ABS(datediff(MILLISECOND,t.EventDateTime, @StartDate)) < 10000 then @Instance else @Instance+1 end
, @StartDate = case when ABS(datediff(MILLISECOND,t.EventDateTime, @StartDate)) < 10000 then @StartDate else EventDateTime end
from #Data t

这就是它在这个特定示例中所做的，它是所需的逻辑:

1. 检查第 1 行，如果它在 @StartDate 的 10 秒内(是)，则保持 @InstanceID 和 @StartDate 相同并将 Instance 设置为 @InstanceID
2. 检查第 2 行，如果它在 @StartDate 的 10 秒内(仍然是原始的，所以是)然后保持 @InstanceID 和 @StartDate 相同并将 Instance 设置为 @InstanceID
3. 检查第 3 行，如果它在 @StartDate 的 10 秒内(仍然是原来的那个，所以否)然后将 @instanceID 递增 1 并将 Instance 设置为其，并将 @StartDate 重置为该记录的事务日期
4. 检查第 4 行，如果它在 @StartDate 的 10 秒内(现在是第 3 行的那个，所以是)然后保持 @InstanceID 和 @StartDate 相同并将 Instance 设置为 @InstanceID
5. 检查第 5 行，如果它在 @StartDate 的 10 秒内(仍然是第 3 行的那个，所以否)然后将 @instanceID 递增 1 并将 Instance 设置为其，并将 @StartDate 重置为该记录的事务日期<
6. 等等
7. 等等

问题:

这可以使用 while 循环、游标或古怪的更新来完成。是否有真正基于集合的方法来执行此操作(即没有提到的方法)？

Answer 1

递归 CTE 查询可能是一种解决方案。根本不需要更新。

DECLARE @OffsetMsec int = 10000;
WITH d1 (ID, EventDateTime, IsStarted, StartDate)
AS (
    SELECT TOP 1 ID, EventDateTime, 1, EventDateTime FROM #Data
    UNION ALL
    SELECT d2.ID, d2.EventDateTime,
        CASE WHEN ABS(DATEDIFF(MILLISECOND, d1.StartDate, d2.EventDateTime)) < @OffsetMsec
            THEN 0 ELSE 1 END AS IsStarted,
        CASE WHEN ABS(DATEDIFF(MILLISECOND, d1.StartDate, d2.EventDateTime)) < @OffsetMsec
            THEN d1.StartDate ELSE d2.EventDateTime END AS StartDate
    FROM #Data d2 INNER JOIN d1 ON d2.ID = d1.ID + 1
),
d2 (ID, EventDateTime, Instance)
AS (
    SELECT ID, EventDateTime, IsStarted, StartDate, 
        (SELECT SUM(IsStarted) FROM d1 d11 WHERE d11.ID <= d1.ID) AS Instance
    FROM d1
)
SELECT * FROM d2

结果

ID          EventDateTime           Instance
----------- ----------------------- -----------
1           2018-10-16 01:37:23.173 1
2           2018-10-16 01:37:31.447 1
3           2018-10-16 01:37:36.577 2
4           2018-10-16 01:37:45.457 2
5           2018-10-16 01:37:48.860 3
6           2018-10-16 01:38:06.407 4
7           2018-10-16 01:38:11.030 4
8           2018-10-16 01:38:15.470 4
9           2018-10-16 01:38:19.133 5
10          2018-10-16 01:38:27.830 5