MySQL 5.7如何统计每日登录时间差不超过5秒的同时间段用户列表

问题原因分析 #

你原来的SQL存在两个核心错误：

• DATEDIFF() 函数计算的是两个日期的天数差，不是秒差，完全不符合你要计算5秒内差值的需求
• 直接笛卡尔积关联会出现重复配对（比如1和2匹配、2和1匹配的重复数据），且没有做分组聚合，无法得到合并的用户ID列表

适配MySQL 5.7的正确SQL #

SELECT 
  GROUP_CONCAT(DISTINCT l.userid ORDER BY l.userid SEPARATOR ', ') AS userids,
  MIN(l.loginDateTime) AS loginDateTime
FROM logs l
WHERE EXISTS (
  SELECT 1 
  FROM logs l2 
  WHERE 
    l2.userid <> l.userid
    AND DATE(l.loginDateTime) = DATE(l2.loginDateTime)
    AND ABS(TIMESTAMPDIFF(SECOND, l.loginDateTime, l2.loginDateTime)) <= 5
)
GROUP BY 
  DATE(l.loginDateTime), 
  FLOOR(UNIX_TIMESTAMP(l.loginDateTime)/5)
HAVING COUNT(DISTINCT l.userid) >= 2;

逻辑说明 #

• 用TIMESTAMPDIFF(SECOND, 时间1, 时间2)计算两个时间的秒级差值，替换原来的DATEDIFF实现5秒差判断
• EXISTS子查询过滤掉没有相邻5秒内其他用户登录的无效记录
• 按「天+每5秒的时间窗口」分组，保证同组内的记录都落在同一个5秒区间内
• 用GROUP_CONCAT拼接同组的用户ID，DISTINCT去重，HAVING条件保证至少有2个用户才返回结果
• MIN(loginDateTime)取组内最早的登录时间作为展示时间，完全匹配你要求的输出格式

输出结果 #

针对你的样例数据，执行上述SQL会得到如下结果：

userids | loginDateTime
—————————————————————————————
1, 2    | 2018-08-23 22:01:00
1, 2, 3 | 2018-08-28 18:01:34

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