sql - 提取每 2 个样本之间时间差最小的数据

Question

我有一个带有示例时间戳的表：

create table #OREN_TEMP(TS datetime)

insert into #OREN_TEMP(TS)
    values 
    ('2019-10-25 06:20:07.000'),
    ('2019-10-25 06:20:15.000'),
    ('2019-10-25 06:20:19.000'),
    ('2019-10-25 06:20:26.000'),
    ('2019-10-25 06:20:26.000'),
    ('2019-10-25 06:20:34.000'),
    ('2019-10-25 06:20:42.000'),
    ('2019-10-25 06:20:51.000'),
    ('2019-10-25 06:20:59.000'),
    ('2019-10-25 06:21:07.000'),
    ('2019-10-25 06:21:15.000'),
    ('2019-10-25 06:21:19.000'),
    ('2019-10-25 06:21:26.000')

    select * from #OREN_TEMP

当一个样本与另一个样本之间的最短时间为 20 秒时，我被要求从表中提取数据。这意味着我需要提取以下内容：

('2019-10-25 06:20:07.000')
('2019-10-25 06:20:34.000')
('2019-10-25 06:20:59.000')
('2019-10-25 06:21:19.000')

我尝试执行以下操作：

declare @MIN_DATE datetime

select top 1 @MIN_DATE =TS from #OREN_TEMP
order by TS
select TS
from (select TS, row_number() over (partition by datediff(second,@MIN_DATE,TS) / 20 order by TS) as RowNum
      from #OREN_TEMP
    ) TMP
where TMP.ROWNUM=1

我得到了：

TS
2019-10-25 06:20:07.000
2019-10-25 06:20:34.000
2019-10-25 06:20:51.000 -- Not good - only 17 seconds from previous
2019-10-25 06:21:07.000 -- Not good - only 16 seconds from previous

但这并没有帮助，因为我根据第一个 TS 而不是最后一个来划分时间框架。

我想避免循环或光标。与表本身的内部连接是可以的。

我该怎么做？我使用的是 SQL Server 2012 标准版。

标签： sqlsql-serversql-server-2012

Answer 1

您可以为此使用递归 CTE。但它在 SQL Server 中效率不高。SQL Server 对递归 CTE 有许多限制，包括在子查询中没有聚合和递归引用。

所以，这里有一个版本：

with first_row as (
      select top (1) ot.*
      from oren_temp ot
      order by ts asc
     ),
     cte as (
      select ts, 1 as lev
      from first_row
      union all
      select ot.ts, lev + 1
      from oren_temp ot join
           cte
           on ot.ts >= dateadd(second, 20, cte.ts)
     )
select lev, min(ts)
from cte
group by lev;

还有一个 db<>fiddle。

不过，我会提醒您不要在行数过多的数据集上运行它。

编辑：

其实还有更有效的方法。在每个递归步骤中，我们可以ts使用row_number()：

with first_row as (
      select top (1) ot.*
      from oren_temp ot
      order by ts asc
     ),
     cte as (
      select ts, 1 as lev, convert(int, 1) as seqnum
      from first_row
      union all
      select ot.ts, lev + 1, convert(int, row_number() over (partition by lev order by ot.ts)) as seqnum
      from oren_temp ot join
           cte
           on ot.ts >= dateadd(second, 20, cte.ts)
       where seqnum = 1
     )
select lev, ts, seqnum
from cte
where seqnum = 1;

这对更大的数据应该更有效。而且，它也很酷。