SQL分组内逐行比较：计算同地点同日30分钟内关联事件数

要计算同一地点、同一日期下与当前事件时间差30分钟内的事件数量（排除自身），可以用两种高效的SQL实现方式：

方法一：窗口函数（推荐，性能更优） #

利用窗口函数按location和date分区，统计当前事件前后30分钟内的所有事件数，再减去自身的1个计数。

MySQL 版本 #

SELECT 
    event_id,
    location,
    date,
    event_time,
    COUNT(*) OVER (
        PARTITION BY location, date
        ORDER BY UNIX_TIMESTAMP(event_time)
        RANGE BETWEEN 1800 PRECEDING AND 1800 FOLLOWING
    ) - 1 AS n_within_30
FROM your_table;

• 1800是30分钟对应的秒数，通过UNIX_TIMESTAMP将时间转为秒数后，用RANGE范围匹配前后30分钟的事件。
• 最后减1是排除当前事件自身的计数。

PostgreSQL 版本 #

SELECT 
    event_id,
    location,
    date,
    event_time,
    COUNT(*) OVER (
        PARTITION BY location, date
        ORDER BY event_time
        RANGE BETWEEN INTERVAL '30 minutes' PRECEDING AND INTERVAL '30 minutes' FOLLOWING
    ) - 1 AS n_within_30
FROM your_table;

直接用INTERVAL语法定义时间范围，更直观。

方法二：自连接分组计数 #

通过自连接匹配同一地点、日期且时间差在30分钟内的事件，再分组统计数量。

SELECT 
    t1.event_id,
    t1.location,
    t1.date,
    t1.event_time,
    COUNT(t2.event_id) - 1 AS n_within_30
FROM your_table t1
LEFT JOIN your_table t2
    ON t1.location = t2.location
    AND t1.date = t2.date
    AND ABS(TIMESTAMPDIFF(MINUTE, t1.event_time, t2.event_time)) <= 30
GROUP BY t1.event_id, t1.location, t1.date, t1.event_time;

• LEFT JOIN确保所有原始事件都被保留，即使没有符合条件的其他事件。
• ABS(TIMESTAMPDIFF(MINUTE, ...)) <=30判断两个事件的时间差是否在30分钟以内。
• 减1同样是排除当前事件自身的计数。

结果验证 #

两种方法都能得到你预期的结果：比如event_id=000000的事件，同一地点A、日期2025-01-01中，符合时间范围的事件共4个，减1后得到n_within_30=3，与预期一致。

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