基于相似时间戳分组统计COUNT(*)的SQL技术咨询

这个需求很典型，本质就是把时间相近的datetime1值合并到同一个分组，再结合datetime2和grp来统计事件数。下面我分几种主流数据库场景，给你具体的实现方案，你可以根据自己的环境调整：

固定时间间隔分组（比如X分钟内算相似） #

如果你的“一定范围”是固定的时间间隔（比如5分钟、10分钟），核心思路是把datetime1截断到最近的间隔起点，用这个截断后的值作为分组键。

MySQL/MariaDB 实现 #

SELECT 
    COUNT(*) AS event_count,
    -- 按10分钟间隔截断datetime1，替换600为对应间隔的秒数（5分钟=300，1分钟=60）
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(datetime1)/600)*600) AS grouped_datetime1,
    datetime2,
    grp
FROM tb1
GROUP BY grouped_datetime1, datetime2, grp;

解释：UNIX_TIMESTAMP把datetime转成秒数，除以间隔秒数取整后再转回datetime，这样同一间隔内的所有datetime1都会被归为同一个分组值。

PostgreSQL 实现 #

PostgreSQL的date_trunc函数可以更灵活地处理时间截断：

SELECT 
    COUNT(*) AS event_count,
    -- 按10分钟间隔截断datetime1，可替换10为你需要的分钟数
    date_trunc('hour', datetime1) + INTERVAL '10 minutes' * FLOOR(EXTRACT(minute FROM datetime1)/10) AS grouped_datetime1,
    datetime2,
    grp
FROM tb1
GROUP BY grouped_datetime1, datetime2, grp;

解释：先截断到小时，再按10分钟的倍数计算出当前时间所属的间隔起点，逻辑更直观。

SQL Server 实现 #

用DATEADD和DATEDIFF组合实现时间截断：

SELECT 
    COUNT(*) AS event_count,
    -- 按10分钟间隔截断datetime1，替换10为你需要的分钟数
    DATEADD(minute, DATEDIFF(minute, 0, datetime1)/10*10, 0) AS grouped_datetime1,
    datetime2,
    grp
FROM tb1
GROUP BY DATEADD(minute, DATEDIFF(minute, 0, datetime1)/10*10, 0), datetime2, grp;

解释：先计算从0时间到datetime1的总分钟数，按间隔取整后再转回datetime，实现分组对齐。

连续时间窗口分组（与相邻记录时间差在范围内算相似） #

如果你的“一定范围”是指当前记录与前一条记录的时间差不超过阈值（比如10分钟），需要用窗口函数来生成动态分组ID：

以MySQL 8.0+/PostgreSQL/SQL Server为例（支持CTE和窗口函数）：

WITH ranked_data AS (
    SELECT 
        *,
        -- 当当前datetime1与前一条的差值超过10分钟时，生成新分组标记
        SUM(CASE 
            WHEN TIMESTAMPDIFF(minute, LAG(datetime1) OVER (PARTITION BY grp, datetime2 ORDER BY datetime1), datetime1) > 10 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY grp, datetime2 ORDER BY datetime1) AS group_id
    FROM tb1
)
SELECT 
    COUNT(*) AS event_count,
    MIN(datetime1) AS group_start_time,
    MAX(datetime1) AS group_end_time,
    datetime2,
    grp
FROM ranked_data
GROUP BY group_id, datetime2, grp;

解释：

1. 先按grp和datetime2分区，按datetime1排序，用LAG获取前一条记录的datetime1
2. 计算当前记录与前一条的时间差，超过阈值则累加1，生成唯一的group_id
3. 最后按group_id、datetime2、grp统计，同时可以输出分组的时间范围

你可以根据自己的实际需求（固定间隔/连续窗口）和数据库类型选择对应方案，调整时间阈值参数即可。

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