MySql查询脚本用于计算每个月的活跃用户数

我需要查询Mysql以查找日期范围内每个月的活跃用户数.活动用户是在该月和之前的月份具有登录日志记录的用户(两个月不仅是其中一个).

这是表和测试数据的脚本.

CREATE TABLE `logger` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `UserId` INTEGER NULL DEFAULT NULL,
  `loginDate` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `logger` (`UserId`,`loginDate`) VALUES
('1001','20151109'),
('1002','20151103'),
('1003','20151111'),
('1002','20151205'),
('1003','20151208'),
('1001','20160103'),
('1002','20160105');

我需要这样的结果,范围从20151201到20160201

------------------------------
year |month |users |activeUsers
2015 |12    |2     |2 
2016 |01    |2     |1 // only uid 1002 have activity in past month 20151205

准备使用在线表http://sqlfiddle.com/#!9/e9881

你可以用一招来做到这一点.从每个日期添加一个月,然后您可以使用union all和aggregation计数:

select year(logindate), month(logindate), count(distinct userid)
from ((select logindate, userid
       from logger
      ) union all
      (select date_add(longdate, interval 1 month), userid
       from logger
      )
     ) l
group by year(logindate), month(logindate)
order by 1, 2;

编辑:

哦,我误解了这个问题.您需要连续两个月才能成为活跃用户.我知道用户登录会让用户活跃两个月.好的,您可以使用join或解决此问题exists:

select year(l.logindate), month(l.logindate), count(distinct l.userid)
from logger l
where exists (select 1
              from logger l2
              where l2.userid = l.userid and
                    year(date_sub(l.logindate, interval 1 month)) = year(l2.logindate) and
                    month(date_sub(l.logindate, interval 1 month)) = month(l2.logindate)
             )
group by year(l.logindate), month(l.logindate);