如何在SQL Server中为每行统计1秒内的样本数量？

问题背景 #

你有一个包含datetime列的大型数据集，需要生成一列统计每行timestamp前后1秒内的行数。你已用R实现但效率低下（使用了O(n²)的循环），希望在SQL Server中找到无循环的高效实现方式。

示例数据 #

先创建测试用的临时表和数据：

CREATE TABLE #Timestamps (
    timestamp DATETIME2(3)
);

INSERT INTO #Timestamps VALUES
('2011-01-01 11:11:01.200'),
('2011-01-01 11:11:01.300'),
('2011-01-01 11:11:01.400'),
('2011-01-01 11:11:01.500'),
('2011-01-01 11:11:03.000'),
('2011-01-01 11:11:04.000'),
('2011-01-01 11:11:15.000'),
('2011-01-01 11:11:30.000');

解决方案1：自关联分组计数 #

这是最直观的写法，通过表自关联匹配时间范围后分组统计：

SELECT 
    t1.timestamp,
    COUNT(t2.timestamp) AS sec_count
FROM #Timestamps t1
JOIN #Timestamps t2 
    ON t2.timestamp >= DATEADD(SECOND, -1, t1.timestamp)
    AND t2.timestamp <= DATEADD(SECOND, 1, t1.timestamp)
GROUP BY t1.timestamp
ORDER BY t1.timestamp;

解决方案2：使用APPLY子查询（更适合大数据集） #

APPLY会为每行执行一次子查询，SQL Server会优化执行计划，配合索引能大幅提升效率：

SELECT 
    t.timestamp,
    a.sec_count
FROM #Timestamps t
CROSS APPLY (
    SELECT COUNT(*) AS sec_count
    FROM #Timestamps
    WHERE timestamp >= DATEADD(SECOND, -1, t.timestamp)
      AND timestamp <= DATEADD(SECOND, 1, t.timestamp)
) a
ORDER BY t.timestamp;

性能优化提示 #

为timestamp列创建非聚集索引可以让上述查询的性能显著提升，尤其是处理百万级以上的数据集时：

CREATE NONCLUSTERED INDEX IX_Timestamps_Timestamp ON #Timestamps(timestamp);

执行结果 #

两种方法都会返回你期望的结果：

内容的提问来源于stack exchange，提问作者Andrew