MySQL Workbench 如何筛选特定日期行、排除日期以统计今年2月后无活跃用户?
2月停活用户统计SQL实现方案
核心判定规则
先明确两个统计边界,避免结果偏差:
- 曾正常活跃:用户在2月1日前至少存在1次游戏关联记录(创建游戏
host=1、加入游戏join=1均算有效活跃) - 2月后无活动:用户所有关联记录中,2月1日0点之后不存在任何操作痕迹(包括建/入游戏、删除游戏关联的时间字段)
具体实现写法
写法1:聚合子查询计算用户最后活跃时间
逻辑最直观,先逐用户聚合全周期的最晚活动时间,再做时间筛选:
-- 替换时间占位符为实际统计年份的2月1日,如2024年就写'2024-02-01 00:00:00' SELECT COUNT(DISTINCT u.id) AS churned_user_count FROM users u INNER JOIN ( -- 子查询:逐用户聚合所有活动时间,取最晚值 SELECT gu.user_id, MAX(GREATEST( gu.established_at, IFNULL(gu.deleted_at, gu.established_at) )) AS last_active_time FROM game_users gu GROUP BY gu.user_id ) user_active_stat ON u.id = user_active_stat.user_id WHERE -- 最晚活跃时间早于2月,即2月后无任何活动 user_active_stat.last_active_time < '202X-02-01 00:00:00'
写法2:EXISTS/NOT EXISTS 条件筛选
数据量较大时性能更优,不需要全表聚合计算,直接通过存在性判断筛选:
SELECT COUNT(DISTINCT u.id) AS churned_user_count FROM users u WHERE -- 满足:2月前至少有1次活跃记录 EXISTS ( SELECT 1 FROM game_users gu_old WHERE gu_old.user_id = u.id AND gu_old.established_at < '202X-02-01 00:00:00' ) -- 排除:2月后存在任何活动记录的用户 AND NOT EXISTS ( SELECT 1 FROM game_users gu_new WHERE gu_new.user_id = u.id AND ( gu_new.established_at >= '202X-02-01 00:00:00' OR gu_new.deleted_at >= '202X-02-01 00:00:00' ) )
常见注意点
- 时区匹配:确认数据库存储时间的时区,和筛选用的2月1日时间保持一致,避免跨天误差
- 空值兼容:
deleted_at可能为空,聚合计算时必须做空值替换,否则会出现时间计算错误 - 身份筛选调整:如果需要单独统计创建游戏停活、加入游戏停活的用户,只需在子查询中增加
gu.host=1或gu.join=1的过滤条件即可
内容的提问来源于stack exchange,提问作者kuki18955
相关产品推荐
相关产品推荐

