如何用SQL查询删除3分钟间隔内为后续记录子串的事件数据？

实现方案 #

这个需求完全可以通过标准SQL实现，核心逻辑是用NOT EXISTS子句匹配删除规则，筛选出不需要删除的记录即可。

核心逻辑说明 #

我们需要排除的记录满足：存在任意一条后续记录，同时符合两个条件：

1. 后续记录的时间戳晚于当前记录，且两者时间间隔小于3分钟
2. 后续记录的label字段包含当前记录的label字段值
   反过来，我们只要保留不存在上述匹配后续记录的行即可。

参考SQL实现（MySQL版本） #

SELECT e1.event_id, e1.timestamp, e1.label
FROM events e1
WHERE NOT EXISTS (
    SELECT 1
    FROM events e2
    WHERE 
        -- 条件1：e2时间晚于e1，且间隔小于3分钟
        e2.timestamp > e1.timestamp
        AND TIMESTAMPDIFF(SECOND, e1.timestamp, e2.timestamp) < 180
        -- 条件2：e2的label包含e1的label
        AND e2.label LIKE CONCAT('%', e1.label, '%')
)
ORDER BY e1.timestamp;

其他数据库适配 #

如果使用其他数据库，仅需要调整时间差计算的函数即可：

• PostgreSQL版本替换时间差条件为：EXTRACT(EPOCH FROM (e2.timestamp - e1.timestamp)) < 180
• SQL Server版本替换时间差条件为：DATEDIFF(SECOND, e1.timestamp, e2.timestamp) < 180

结果验证 #

上述SQL执行后，输出结果和需求预期一致：

注：需求示例结果中test Engineer对应的timestamp为笔误，原表中该label对应的时间戳为2021-11-15 10:24:54。

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