SQL Server中按时间范围及条件统计相同产品行数

这个需求完全可以在SQL Server中实现，以下是分步实现方案：

1. 创建测试表并插入数据 #

注意timestamp是SQL Server的保留关键字，创建表时需用方括号包裹：

CREATE TABLE test_c (
    [timestamp] DATETIME,
    product VARCHAR(50),
    client_id INT,
    seconds INT
);

-- 插入示例测试数据（可根据实际场景调整）
INSERT INTO test_c ([timestamp], product, client_id, seconds)
VALUES
('2024-05-20 10:00:00', 'ProductA', 1, 9),
('2024-05-20 10:02:00', 'ProductA', 2, 5),
('2024-05-20 10:04:00', 'ProductA', 3, 7),
('2024-05-20 10:06:00', 'ProductA', 4, 8),
('2024-05-20 11:00:00', 'ProductB', 5, 10),
('2024-05-20 11:03:00', 'ProductB', 6, 6);

2. 生成时间窗口列并完成统计 #

通过窗口函数结合时间范围筛选，一次性生成before_5、after_5列，并统计符合条件的记录数：

SELECT
    [timestamp],
    product,
    client_id,
    seconds,
    DATEADD(MINUTE, -5, [timestamp]) AS before_5,
    DATEADD(MINUTE, 5, [timestamp]) AS after_5,
    CASE 
        WHEN seconds >= 8 THEN 
            COUNT(*) OVER (
                PARTITION BY product 
                ORDER BY [timestamp]
                RANGE BETWEEN INTERVAL 5 MINUTE PRECEDING AND INTERVAL 5 MINUTE FOLLOWING
            )
        ELSE NULL
    END AS same_product_count
FROM test_c;

代码说明： #

• DATEADD(MINUTE, -5, [timestamp])和DATEADD(MINUTE, 5, [timestamp])分别生成当前记录时间点的前后5分钟时间；
• 窗口函数COUNT(*) OVER (...)按product分组，通过RANGE BETWEEN限定统计范围为当前记录的前后5分钟；
• CASE语句仅在seconds >=8时返回统计结果，否则返回NULL。

预期结果示例 #

使用上述测试数据，查询结果如下：

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