MySQL查询获取指定日期内间隔大于X秒的用户真实登录记录

核心逻辑：按用户分组后按登录时间升序排序，判断每条记录与同用户上一次有效登录的时间差，超过设定阈值X则标记为有效登录，否则判定为冗余记录过滤。

兼容MySQL 5.7/8.x版本的实现方案 #

-- 可自定义配置参数
SET @timeout = 10; -- 重复登录判定阈值，单位为秒，替换为实际需要的X值
SET @target_date = '2021-09-03'; -- 筛选的目标日期
SET @prev_user = NULL;
SET @prev_valid_time = NULL;

SELECT rowid, userid, dateandtime
FROM (
    SELECT
        rowid,
        userid,
        dateandtime,
        -- 标记当前记录是否为有效登录
        CASE
            WHEN @prev_user != userid THEN 1
            WHEN TIMESTAMPDIFF(SECOND, @prev_valid_time, dateandtime) >= @timeout THEN 1
            ELSE 0
        END AS is_valid,
        -- 更新上一次处理的用户ID
        @prev_user := userid,
        -- 仅当前记录为有效登录时，更新上一次有效登录时间
        @prev_valid_time := IF(
            CASE WHEN @prev_user != userid THEN 1 WHEN TIMESTAMPDIFF(SECOND, @prev_valid_time, dateandtime) >= @timeout THEN 1 ELSE 0 END = 1,
            dateandtime,
            @prev_valid_time
        )
    FROM login_log -- 替换为实际业务表名
    WHERE
        -- 范围查询可命中dateandtime字段索引，性能远优于DATE()函数写法
        dateandtime >= @target_date
        AND dateandtime < DATE_ADD(@target_date, INTERVAL 1 DAY)
    ORDER BY userid, dateandtime ASC
) AS filtered_log
WHERE is_valid = 1
ORDER BY rowid ASC;

注意事项 #

• 请将代码中的login_log替换为实际的业务表名
• 本次示例数据设置@timeout = 10即可完全匹配预期输出，可根据业务规则调整阈值
• 若dateandtime字段建有索引，建议保留现有范围查询的条件写法，避免用DATE(dateandtime) = 'xxx'导致索引失效

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