如何编写SQL查询同一账号±60秒内使用不同IP的全部操作记录

注：你提供的CREATE TABLE语句存在几处小问题：action字段定义后多了冗余逗号、闭合括号误用为}、主键设为userID无法支持同用户有多条操作记录，实际使用时建议调整主键为自增ID或联合主键，不影响本次查询逻辑。

实现思路 #

我们需要先定位符合要求的用户ID（userID）：该用户存在至少两条操作记录满足IP不同、时间差绝对值≤60秒，再查询这些用户的所有操作记录，即可匹配预期输出。

通用SQL代码 #

以下为标准SQL写法，时间差计算可根据你使用的数据库替换为对应函数：

SELECT * 
FROM actions 
WHERE userID IN (
    SELECT DISTINCT a1.userID
    FROM actions a1
    INNER JOIN actions a2 
        ON a1.userID = a2.userID 
        -- 两条记录IP不同
        AND a1.deviceIP != a2.deviceIP
        -- 时间差绝对值不超过60秒
        AND ABS(EXTRACT(EPOCH FROM a1.actionTimestamp) - EXTRACT(EPOCH FROM a2.actionTimestamp)) <= 60
        -- 排除同一条记录和自身匹配的情况
        AND (a1.action, a1.actionTimestamp, a1.deviceIP) <> (a2.action, a2.actionTimestamp, a2.deviceIP)
)

主流数据库适配版本 #

MySQL适配 #

SELECT * 
FROM actions 
WHERE userID IN (
    SELECT DISTINCT a1.userID
    FROM actions a1
    INNER JOIN actions a2 
        ON a1.userID = a2.userID 
        AND a1.deviceIP != a2.deviceIP
        AND ABS(TIMESTAMPDIFF(SECOND, a1.actionTimestamp, a2.actionTimestamp)) <= 60
        AND (a1.action, a1.actionTimestamp, a1.deviceIP) <> (a2.action, a2.actionTimestamp, a2.deviceIP)
)

SQL Server适配 #

SELECT * 
FROM actions 
WHERE userID IN (
    SELECT DISTINCT a1.userID
    FROM actions a1
    INNER JOIN actions a2 
        ON a1.userID = a2.userID 
        AND a1.deviceIP != a2.deviceIP
        AND ABS(DATEDIFF(SECOND, a1.actionTimestamp, a2.actionTimestamp)) <= 60
        AND NOT (a1.action = a2.action AND a1.actionTimestamp = a2.actionTimestamp AND a1.deviceIP = a2.deviceIP)
)

结果验证 #

使用你提供的测试数据执行上述查询，会自动排除userID=70的所有记录（该用户两条操作记录IP完全相同，不满足筛选条件），返回结果与你给出的预期输出完全一致。

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