如何按日期统计间隔超过20分钟的事件组数量？

现有事件数据如下：

+---------------------+------------+
| S                   | D          |
+---------------------+------------+
| 2024-07-21 07:01:48 | 2024-07-21 |
| 2024-07-21 07:01:49 | 2024-07-21 |
| 2024-07-21 07:02:03 | 2024-07-21 |
| 2024-07-21 07:09:50 | 2024-07-21 |
| 2024-07-21 07:10:18 | 2024-07-21 |
| 2024-07-21 07:10:40 | 2024-07-21 |
| 2024-07-21 12:12:01 | 2024-07-21 |
| 2024-07-21 12:12:18 | 2024-07-21 |
| 2024-07-21 12:12:28 | 2024-07-21 |
| 2024-07-21 12:12:57 | 2024-07-21 |
| 2024-07-21 12:13:21 | 2024-07-21 |
| 2024-07-21 12:19:59 | 2024-07-21 |
| 2024-07-21 12:20:28 | 2024-07-21 |
| 2024-07-21 12:20:42 | 2024-07-21 |
| 2024-07-21 17:03:37 | 2024-07-21 |
| 2024-07-21 17:04:17 | 2024-07-21 |
| 2024-07-21 17:04:28 | 2024-07-21 |
| 2024-07-21 17:04:40 | 2024-07-21 |
| 2024-07-21 19:41:51 | 2024-07-21 |
| 2024-07-21 19:42:30 | 2024-07-21 |
| 2024-07-21 19:42:32 | 2024-07-21 |
| 2024-07-21 19:55:22 | 2024-07-21 |
| 2024-07-21 19:55:31 | 2024-07-21 |
| 2024-07-21 19:55:59 | 2024-07-21 |
| 2024-07-21 19:56:07 | 2024-07-21 |
| 2024-07-21 20:04:12 | 2024-07-21 |
| 2024-07-21 20:04:48 | 2024-07-21 |
| 2024-07-21 20:05:01 | 2024-07-21 |
| 2024-07-21 20:05:19 | 2024-07-21 |
| 2024-07-22 07:07:56 | 2024-07-22 |
| 2024-07-22 07:07:57 | 2024-07-22 |
| 2024-07-22 07:08:23 | 2024-07-22 |
| 2024-07-22 07:08:40 | 2024-07-22 |
| 2024-07-22 07:08:51 | 2024-07-22 |
| 2024-07-22 07:16:09 | 2024-07-22 |
| 2024-07-22 07:16:44 | 2024-07-22 |
| 2024-07-22 07:17:06 | 2024-07-22 |
| 2024-07-22 07:17:07 | 2024-07-22 |
| 2024-07-22 12:15:24 | 2024-07-22 |
| 2024-07-22 12:15:34 | 2024-07-22 |
| 2024-07-22 12:15:53 | 2024-07-22 |
| 2024-07-22 12:16:05 | 2024-07-22 |
| 2024-07-22 12:25:11 | 2024-07-22 |
| 2024-07-22 12:26:00 | 2024-07-22 |
| 2024-07-22 12:26:14 | 2024-07-22 |
| 2024-07-22 15:08:46 | 2024-07-22 |
| 2024-07-22 15:08:55 | 2024-07-22 |
| 2024-07-22 15:09:23 | 2024-07-22 |
| 2024-07-22 15:09:49 | 2024-07-22 |
| 2024-07-22 15:10:33 | 2024-07-22 |
| 2024-07-22 15:11:06 | 2024-07-22 |
| 2024-07-22 15:18:54 | 2024-07-22 |
| 2024-07-22 15:19:34 | 2024-07-22 |
| 2024-07-22 15:19:57 | 2024-07-22 |
| 2024-07-22 19:13:16 | 2024-07-22 |
| 2024-07-22 19:29:23 | 2024-07-22 |
| 2024-07-22 19:29:24 | 2024-07-22 |
| 2024-07-22 19:30:03 | 2024-07-22 |
| 2024-07-22 19:30:56 | 2024-07-22 |
| 2024-07-22 19:41:08 | 2024-07-22 |
| 2024-07-22 19:42:06 | 2024-07-22 |
| 2024-07-22 19:42:07 | 2024-07-22 |
| 2024-07-22 19:42:29 | 2024-07-22 |
| 2024-07-22 19:42:34 | 2024-07-22 |
+---------------------+------------+

需要编写SQL查询，按日期（D字段）分组，统计彼此间隔超过20分钟的事件组数量，预期结果如下：

+------------+-------+
| D          | COUNT |
+------------+-------+
| 2024-07-22 |     4 |
| 2024-07-21 |     4 |
+------------+-------+

可以使用窗口函数标记事件组，再统计每组的数量：

WITH event_groups AS (
    SELECT 
        D,
        S,
        -- 标记当前事件与前一个事件间隔超过20分钟的情况
        CASE WHEN TIMESTAMPDIFF(MINUTE, LAG(S) OVER (PARTITION BY D ORDER BY S), S) > 20 
             THEN 1 
             ELSE 0 
        END AS is_new_group
    FROM your_table_name
),
group_ids AS (
    SELECT 
        D,
        -- 累加标记值生成组ID
        SUM(is_new_group) OVER (PARTITION BY D ORDER BY S) AS group_id
    FROM event_groups
)
SELECT 
    D,
    COUNT(DISTINCT group_id) AS COUNT
FROM group_ids
GROUP BY D
ORDER BY D DESC;

思路说明 #

1. 标记新组起点
   使用LAG()窗口函数获取同一日期内前一个事件的时间，计算与当前事件的时间差。如果差值超过20分钟，标记为新组的起点（值为1），否则为0。
2. 生成组ID
   对每个日期内的标记值进行累加，相同的累加值对应同一个事件组。第一个事件的累加值为0，之后每遇到一个新组起点，累加值加1。
3. 统计组数量
   按日期分组，统计每个日期内不同组ID的数量，即为对应的事件组总数。

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