本文讨论了如何高效地从游戏用户登录记录中筛选出连续三天未登录的流失用户，通过比较两种不同的SQL查询方法，展示了优化后的查询如何显著提高性能。详细介绍了SQL查询的实现方式和性能对比，旨在帮助开发者提升数据处理效率。
项目需要查询游戏用户的流失用户，对于流失用户的定义为：当天登陆，之后三天未登录的用户视为流失用户，目前有数据表 login_info 分别存储了用户id，登陆时间（int 11）。
最简单的实现方式：SELECT DISTINCT uid from login_info WHERE login_time between $time1['begin'] AND $time1['end']
and uid not in (select DISTINCT uid from login_info where login_time between $time2['begin'] and $time2['end']) 其中的$time1 存储为一天的时间起止 $time2存储为流失的时间范围（这里是三天），但是实际当中查询速度相当慢。
群里找了个高手，给出了另外一种解决办法，最终sql如下：
select uid from (select uid,MAX(login_time) as max,MIN(login_time) as min FROM login_info where login_time between 1361721600 and 1362067199 GROUP BY uid ) as A
where max between 1361721600 AND 1361807999
查询时间大大缩短，不得不佩服高手啊！！！具体就不做解释了
最简单的实现方式：SELECT DISTINCT uid from login_info WHERE login_time between $time1['begin'] AND $time1['end']
and uid not in (select DISTINCT uid from login_info where login_time between $time2['begin'] and $time2['end']) 其中的$time1 存储为一天的时间起止 $time2存储为流失的时间范围（这里是三天），但是实际当中查询速度相当慢。
群里找了个高手，给出了另外一种解决办法，最终sql如下：
select uid from (select uid,MAX(login_time) as max,MIN(login_time) as min FROM login_info where login_time between 1361721600 and 1362067199 GROUP BY uid ) as A
where max between 1361721600 AND 1361807999
查询时间大大缩短，不得不佩服高手啊！！！具体就不做解释了
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
