MySQL查询近60天无活动闲置用户 SQL结果异常排查

• 计算日期间隔的子查询没有按用户维度分组，全局取了全表最新的发帖更新时间，所有用户返回的间隔值完全一致，无法体现单个用户的实际闲置时长
• 未实现60天无活动的筛选逻辑，仅统计发帖数量，也没有覆盖所有会随操作更新的日期字段，判断活跃状态的维度不全
• GROUP BY写法不符合SQL规范，非聚合字段tblpost.PostStatus_UpdatedDateTime未做聚合处理，返回的是分组内随机一条记录的时间，不是用户对应维度的最新时间
• 使用INNER JOIN关联发帖表和用户表，会直接漏掉从未发过帖、但收到过发帖邀请的沉默用户
• 排序逻辑错误，按发帖总数倒序无法实现「距离上次收到发帖邀请间隔最久优先」的需求

• 以用户主表为基础表，左关联所有操作相关的业务表，避免漏掉无对应操作记录的用户
• 用GREATEST()函数取单个用户所有操作日期字段的最大值，作为该用户的最后活跃时间
• 筛选最后活跃时间早于60天前的用户，即为最近60天无任何操作的闲置用户
• 单独计算每个用户距离上次收到发帖邀请的间隔天数，按该值倒序排列，即可优先返回间隔最久的用户

SELECT 
    c.ContactID,
    COUNT(p.PostID) AS TotalPostCount,
    -- 取所有操作日期的最大值作为最后活跃时间，字段按实际业务表补充即可
    GREATEST(
        IFNULL(MAX(p.PostStatus_UpdatedDateTime), '1970-01-01'),
        IFNULL(MAX(i.InviteSendTime), '1970-01-01'),
        IFNULL(MAX(c.LastLoginTime), '1970-01-01')
    ) AS LastActiveTime,
    MAX(i.InviteSendTime) AS LastReceiveInviteTime,
    DATEDIFF(CURDATE(), GREATEST(
        IFNULL(MAX(p.PostStatus_UpdatedDateTime), '1970-01-01'),
        IFNULL(MAX(i.InviteSendTime), '1970-01-01'),
        IFNULL(MAX(c.LastLoginTime), '1970-01-01')
    )) AS InactiveDays,
    DATEDIFF(CURDATE(), IFNULL(MAX(i.InviteSendTime), '1970-01-01')) AS DaysSinceLastInvite
FROM
    tblcontact_desc c
-- 左关联发帖表，保留无发帖记录的用户
LEFT JOIN tblpost p 
    ON c.ContactdescID = p.ContactdescID
-- 左关联发帖邀请表，关联键按实际表结构调整
LEFT JOIN tblpost_invite i 
    ON c.ContactID = i.ContactID
GROUP BY c.ContactID
HAVING 
    -- 筛选最近60天无任何活动的用户
    LastActiveTime < DATE_SUB(CURDATE(), INTERVAL 60 DAY)
-- 按上次收邀请的间隔倒序，间隔越久越靠前
ORDER BY DaysSinceLastInvite DESC;

• 所有涉及操作记录的日期字段（比如发帖、收邀请、登录、评论、资料修改等），都可以加到GREATEST()的参数列表中，会自动纳入最后活跃时间的计算范围
• 字段加IFNULL()是为了处理用户从未产生过某类操作时字段为NULL的情况，避免GREATEST()返回NULL导致判断失效
• 用日期大小判断代替天数计算做筛选，能更好的利用日期字段上的索引，查询效率更高
• 发帖邀请表的表名、关联键需要根据你实际的库表结构调整，示例中用tblpost_invite代指存储发帖邀请记录的表

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