统计在某个时段以内,连续登录N天以上的独立用户IP数量(以下我的N是常量2,这个可以自己随意修改)
SELECT uid,Ip,login_time,COUNT(DISTINCT Ip) as IpCount
FROM (
SELECT COUNT(diff) dless,uid,login_time,Ip
FROM (
SELECT ddate-rn diff,uid,login_time,Ip
FROM (
SELECT @p:=@p+1 rn,uid,DATEDIFF(login_time,'1971-01-01') ddate,login_time,Ip
FROM (
SELECT a.PlayerId uid,date(a.Times) login_time,Ip
FROM crazy_demigod.login_log a WHERE UNIX_TIMESTAMP(a.Times)>=UNIX_TIMESTAMP('2013-01-01 00:00:00') AND UNIX_TIMESTAMP(a.Times)<=UNIX_TIMESTAMP('2013-02-18 23:59:59')
GROUP BY uid,date(a.Times)
ORDER BY uid,date(a.Times)
)x,(SELECT @p:=0)tmt
)x
)x GROUP BY diff,uid
)x WHERE dless>=2 GROUP BY login_time LIMIT 0,12;
设计思路:
1、借助用户变量 @p,使用(SELECT @p:=0)tmt进行初始化赋值。为了尽量将语句控制在一句内,需要这样赋值,而使用 SET @p=0; 会产生太多可变的结果,不够稳定,而且使用 SQLyog 测试的结果和直接用代码运行(可用phpMyAdmin测试)的结果又是有出入的,最后总结使用以上方法最妥当。折腾一天的代价总结出来的啊!汗!(以上取别名为rn)
2、借助 DATEDIFF 函数求出所有符合条件记录的登录时间与指定时间的天数差(命名为ddate)
3、再在外层查询中得到ddate与rn之间的差,求这个差的目的在于,rn是递增的,若ddate这个天数差也是递增的(只有连续的天数才会递增),则连续的登录日期得到的结果一同一个数值(命名为diff)
4、得到这个 diff 则可以用其与 uid 一同进行分组,统计得到连续登录的天数和(这个和命名为 dless)
5、有了这个 dless 就知道了哪些用户连续登录的天数,最后至于是取独立IP数量,还是取用户数量就因人而异,自己看着修改吧,哈哈,完工