如何查询符合条件事件前后2分钟内的所有关联事件？

我可以编写查询语句返回符合特定条件的事件，但能否进一步查询原查询返回的每个事件的timestamp前后2分钟内的所有事件？

例如，我的数据库中有如下事件数据：

SELECT Id,Name,StartDateTime,Length FROM Events ORDER BY StartDateTime DESC;

查询结果：

+--------+--------------+---------------------+--------+
| Id     | Name         | StartDateTime       | Length |
+--------+--------------+---------------------+--------+
| 256779 | Event-256779 | 2024-07-22 16:08:43 |  16.51 |
| 256777 | Event-256777 | 2024-07-22 16:08:32 |  18.16 |
| 256775 | Event-256775 | 2024-07-22 16:05:55 |  10.88 |
| 256772 | Event-256772 | 2024-07-22 16:02:57 |  20.13 |
| 256770 | Event-256770 | 2024-07-22 16:02:51 |  15.73 |
| 256768 | Event-256768 | 2024-07-22 16:02:26 |  23.91 |
| 256767 | Event-256767 | 2024-07-22 16:00:34 |  17.82 |
| 256766 | Event-256766 | 2024-07-22 16:00:31 |   9.99 |
| 256765 | Event-256765 | 2024-07-22 15:59:57 |  19.46 |
| 256760 | Event-256760 | 2024-07-22 15:53:19 |  16.09 |
| 256758 | Event-256758 | 2024-07-22 15:53:15 |  11.56 |
| 256753 | Event-256753 | 2024-07-22 15:38:37 |   8.72 |
| 256745 | Event-256745 | 2024-07-22 15:32:52 |  15.50 |
| 256744 | Event-256744 | 2024-07-22 15:32:51 |   6.51 |
| 256737 | Event-256737 | 2024-07-22 15:11:06 |  19.93 |
| 256729 | Event-256729 | 2024-07-22 15:01:17 |   8.98 |
| 256724 | Event-256724 | 2024-07-22 14:54:34 |   9.45 |
| 256722 | Event-256722 | 2024-07-22 14:52:11 |  10.01 |
| 256721 | Event-256721 | 2024-07-22 14:52:09 |   8.83 |
| 256717 | Event-256717 | 2024-07-22 14:35:07 |  17.93 |
+--------+--------------+---------------------+--------+

我需要先查询所有Length小于10的事件，同时返回这些事件前后2分钟内的所有事件。初始查询语句如下：

SELECT Id,Name,StartDateTime,Length FROM Events WHERE Length < 10 ORDER BY StartDateTime DESC;

查询结果：

+--------+--------------+---------------------+--------+
| Id     | Name         | StartDateTime       | Length |
+--------+--------------+---------------------+--------+
| 256766 | Event-256766 | 2024-07-22 16:00:31 |   9.99 |
| 256753 | Event-256753 | 2024-07-22 15:38:37 |   8.72 |
| 256744 | Event-256744 | 2024-07-22 15:32:51 |   6.51 |
| 256729 | Event-256729 | 2024-07-22 15:01:17 |   8.98 |
| 256724 | Event-256724 | 2024-07-22 14:54:34 |   9.45 |
| 256721 | Event-256721 | 2024-07-22 14:52:09 |   8.83 |
+--------+--------------+---------------------+--------+

期望得到的查询结果如下：

+--------+--------------+---------------------+--------+
| Id     | Name         | StartDateTime       | Length |
+--------+--------------+---------------------+--------+
| 256767 | Event-256767 | 2024-07-22 16:00:34 |  17.82 |
| 256766 | Event-256766 | 2024-07-22 16:00:31 |   9.99 |
| 256765 | Event-256765 | 2024-07-22 15:59:57 |  19.46 |
| 256753 | Event-256753 | 2024-07-22 15:38:37 |   8.72 |
| 256745 | Event-256745 | 2024-07-22 15:32:52 |  15.50 |
| 256744 | Event-256744 | 2024-07-22 15:32:51 |   6.51 |
| 256729 | Event-256729 | 2024-07-22 15:01:17 |   8.98 |
| 256724 | Event-256724 | 2024-07-22 14:54:34 |   9.45 |
| 256722 | Event-256722 | 2024-07-22 14:52:11 |  10.01 |
| 256721 | Event-256721 | 2024-07-22 14:52:09 |   8.83 |
+--------+--------------+---------------------+--------+

解决方案 #

方法1：子查询+时间范围匹配 #

通过子查询获取所有Length<10的事件的时间范围，然后匹配所有落在这些事件前后2分钟内的记录，用DISTINCT避免重复（如果某事件同时属于多个目标事件的时间范围）：

SELECT DISTINCT e.Id, e.Name, e.StartDateTime, e.Length
FROM Events e
WHERE EXISTS (
    SELECT 1
    FROM Events filter_e
    WHERE filter_e.Length < 10
    AND e.StartDateTime BETWEEN 
        DATE_SUB(filter_e.StartDateTime, INTERVAL 2 MINUTE) 
        AND DATE_ADD(filter_e.StartDateTime, INTERVAL 2 MINUTE)
)
ORDER BY e.StartDateTime DESC;

方法2：JOIN关联查询 #

将原表与筛选出的短事件表做关联，匹配时间在目标事件前后2分钟内的记录，同样用DISTINCT去重：

SELECT DISTINCT e.Id, e.Name, e.StartDateTime, e.Length
FROM Events e
JOIN Events filter_e ON 
    e.StartDateTime BETWEEN 
        DATE_SUB(filter_e.StartDateTime, INTERVAL 2 MINUTE) 
        AND DATE_ADD(filter_e.StartDateTime, INTERVAL 2 MINUTE)
WHERE filter_e.Length < 10
ORDER BY e.StartDateTime DESC;

方法3：窗口函数（适用于MySQL 8+、PostgreSQL等） #

利用窗口函数在时间范围内查找是否存在Length<10的事件，然后筛选出符合条件的记录：

WITH event_with_context AS (
    SELECT 
        Id, Name, StartDateTime, Length,
        MAX(CASE WHEN Length < 10 THEN 1 ELSE 0 END) OVER (
            ORDER BY StartDateTime
            RANGE BETWEEN INTERVAL 2 MINUTE PRECEDING AND INTERVAL 2 MINUTE FOLLOWING
        ) AS has_short_event
    FROM Events
)
SELECT Id, Name, StartDateTime, Length
FROM event_with_context
WHERE has_short_event = 1
ORDER BY StartDateTime DESC;

以上三种方法都能得到你期望的结果，可根据你的数据库版本和性能需求选择合适的方案。

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