SQL Server如何筛选1秒内无B、C事件伴随的A类事件时间戳

假设你的业务数据表名为event_log，字段timestamp为datetime2类型、event为字符串类型，查询语句如下：

SELECT [timestamp]
FROM event_log a
WHERE a.event = 'A'
AND NOT EXISTS (
    SELECT 1
    FROM event_log bc
    WHERE bc.event IN ('B', 'C')
    AND bc.[timestamp] BETWEEN DATEADD(SECOND, -1, a.[timestamp]) AND DATEADD(SECOND, 1, a.[timestamp])
)

逻辑说明：

• 外层查询先过滤出所有A类事件
• 内层子查询校验每个A事件的前后1秒区间内是否存在B、C类事件，不存在的A事件才会被返回
• 注意timestamp是SQL Server的内置关键字，所以查询时需要用方括号包裹规避语法冲突

针对示例数据的校验结果：
第一个A事件发生在2021-06-01 12:00:00.000，前后1秒区间为2021-06-01 11:59:59.000 ~ 2021-06-01 12:00:01.000，区间内存在B、C事件，被过滤；第二个A事件发生在2021-06-01 12:00:01.700，前后1秒区间为2021-06-01 12:00:00.700 ~ 2021-06-01 12:00:02.700，区间内仅存在D类事件，符合要求，最终返回结果和示例一致。

MySQL版本 #

SELECT `timestamp`
FROM event_log a
WHERE a.event = 'A'
AND NOT EXISTS (
    SELECT 1
    FROM event_log bc
    WHERE bc.event IN ('B', 'C')
    AND bc.`timestamp` BETWEEN DATE_ADD(a.`timestamp`, INTERVAL -1 SECOND) AND DATE_ADD(a.`timestamp`, INTERVAL 1 SECOND)
)

PostgreSQL版本 #

SELECT "timestamp"
FROM event_log a
WHERE a.event = 'A'
AND NOT EXISTS (
    SELECT 1
    FROM event_log bc
    WHERE bc.event IN ('B', 'C')
    AND bc."timestamp" BETWEEN a."timestamp" - INTERVAL '1 second' AND a."timestamp" + INTERVAL '1 second'
)

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