寻求查询过去1小时内user_id出现超过1次的SQL脚本及解决现有脚本结果异常问题

首先，你的当前方法存在两个核心问题，导致结果不符合预期：

• 范围过大：第一个临时表#temp包含了所有历史上出现过多次的user_id的记录，而不是限定在过去1小时内的记录，这会引入大量无关数据。
• 自连接的匹配逻辑错误：DATEDIFF(hour, a.EVENTTIME, b.EVENTTIME) = 1只会筛选出时间差刚好整1小时的记录，而且自连接会让同一个user_id的多条记录两两匹配（比如3条记录会产生6条结果），导致数据量爆炸。

正确的解决方案 #

我们需要先限定时间范围，再统计该范围内的user_id出现次数，最后提取符合条件的记录。以下是两种常用的实现方式：

方式一：使用CTE（公共表表达式）分步筛选

-- 先筛选出过去1小时内出现次数超过1次的user_id
WITH hourly_duplicate_users AS (
    SELECT user_id
    FROM YourTable  -- 替换成你的实际表名
    WHERE event_time >= DATEADD(hour, -1, GETDATE())  -- 取当前时间往前1小时的范围，SQL Server语法
    GROUP BY user_id
    HAVING COUNT(*) > 1
)
-- 获取这些user_id在过去1小时内的所有记录
SELECT t.*
FROM YourTable t
JOIN hourly_duplicate_users hu ON t.user_id = hu.user_id
WHERE t.event_time >= DATEADD(hour, -1, GETDATE())
ORDER BY t.user_id, t.event_time;

方式二：使用窗口函数直接标记筛选

SELECT user_id, event_time
FROM (
    SELECT 
        user_id,
        event_time,
        -- 统计当前user_id在过去1小时内的记录总数
        COUNT(*) OVER (PARTITION BY user_id) AS hourly_record_count
    FROM YourTable
    WHERE event_time >= DATEADD(hour, -1, GETDATE())
) AS sub_query
WHERE hourly_record_count > 1
ORDER BY user_id, event_time;

注意事项 #

• 如果你的数据库不是SQL Server，需要调整时间函数：
  • MySQL：将DATEADD(hour, -1, GETDATE())替换为DATE_SUB(NOW(), INTERVAL 1 HOUR)
  • PostgreSQL：替换为CURRENT_TIMESTAMP - INTERVAL '1 hour'
• 这两种方法都会精准限定在过去1小时内，且不会产生多余的重复匹配结果，完全符合你“找出过去1小时内user_id出现次数超过1次的记录”的需求。

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