Oracle SQL查询：找出创建时间差小于2小时的用户行

原始数据 #

假设表名为user_activity，数据如下：

需求 #

找出同一用户（按name+Phone唯一标识）下，created date与其他记录时间差小于2小时的所有行，示例中仅返回Joe的两条记录。

解决方案1：自连接查询 #

SELECT DISTINCT a.*
FROM user_activity a
JOIN user_activity b
  ON a.name = b.name
  AND a.Phone = b.Phone
  AND a.rowid != b.rowid
  AND ABS(CAST(a."created date" AS TIMESTAMP) - CAST(b."created date" AS TIMESTAMP)) < INTERVAL '2' HOUR;

说明

• 通过自连接匹配同一用户的不同记录，用rowid排除自身
• 将created date转为TIMESTAMP类型计算时间差，判断是否小于2小时
• 用DISTINCT避免因双向匹配产生重复行

解决方案2：窗口函数（更高效） #

WITH ranked_logs AS (
  SELECT 
    name,
    Phone,
    "last login",
    "created date",
    LAG(CAST("created date" AS TIMESTAMP)) OVER (PARTITION BY name, Phone ORDER BY "created date") AS prev_created,
    LEAD(CAST("created date" AS TIMESTAMP)) OVER (PARTITION BY name, Phone ORDER BY "created date") AS next_created
  FROM user_activity
)
SELECT name, Phone, "last login", "created date"
FROM ranked_logs
WHERE 
  (prev_created IS NOT NULL AND CAST("created date" AS TIMESTAMP) - prev_created < INTERVAL '2' HOUR)
  OR (next_created IS NOT NULL AND next_created - CAST("created date" AS TIMESTAMP) < INTERVAL '2' HOUR);

说明

• 用LAG获取同一用户的上一条记录创建时间，LEAD获取下一条记录创建时间
• 直接判断当前记录与前后记录的时间差是否小于2小时
• 无需去重，执行效率比自连接更高

注意事项 #

• 如果created date是字符串类型，也可以用TO_TIMESTAMP("created date", 'DD-MON-YYYY HH24:MI:SS')替代CAST，确保格式匹配
• 必须用name+Phone作为用户唯一标识，避免混淆同名不同手机号的用户

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