mysql - 根据最近 90 天的登录计算活跃用户

Question

我正在尝试进行查询，该查询将生成一个列表，显示我们每月有多少活跃用户。我们将活跃用户定义为在过去 90 天内登录过的用户。

我可以很容易地定义我们现在拥有的活跃用户数量

SELECT COUNT(DISTINCT(user_id) FROM login_table
WHERE login_date BETWEEN DATE_SUB(login_date, INTERVAL 90 DAY) AND NOW())

当我必须按月计算我们拥有的用户数量时，我的问题就来了在这里，我必须多次计算一次登录。

如果我有一个用户在 1 月 10 日登录，并且再也没有登录过，那么这个用户在接下来的月份应该算作活跃用户:1 月、2 月、3 月和 4 月，即使我只有一个注册用户

示例数据:

login_date | user_id
2015-01-01 | 1
2015-02-10 | 1
2015-02-11 | 2
2015-02-13 | 1
2015-03-19 | 1

结果应该是这样的:

Date    | Active users
2015-01 | 1 
2015-02 | 2
2015-03 | 2 
2015-04 | 2 
2015-05 | 2 
2015-06 | 1 
2015-07 | 0 
2015-08 | 0 

有没有这样的计数？

Answer 1

如果您有一个包含所有所需日期的日历表，这样的问题就更容易解决。如果您没有这样的表，您可以使用如下查询来创建它:

create table `calendar` (
    `date` DATE NOT NULL,
    PRIMARY KEY (`date`)
)  
    select DATE_ADD('1900-01-01',INTERVAL t4.c*10000 + t3.c*1000 + t2.c*100 + t1.c*10 + t0.c DAY) as `date`
    from 
    (select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t0,
    (select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
    (select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
    (select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
    (select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4

这将创建一个日期从 1900-01-01 到 2173-10-15(10 万天)的表，并且仅消耗大约 2.5 MB。您可以根据需要进行调整。

使用日历表，您可以获得三个月的范围:

select 
    DATE_FORMAT(date_sub(c.date, INTERVAL 1 day), '%Y-%m') as month,
    date_sub(c.date, INTERVAL 3 month) as first_day,
    date_sub(c.date, INTERVAL 1 day)   as last_day
from calendar c
where day(c.date) = 1
  and c.date between '2015-02-01' and '2015-09-01'

结果:

| month   | first_day  | last_day   |
| 2015-01 | 2014-11-01 | 2015-01-31 |
| 2015-02 | 2014-12-01 | 2015-02-28 |
| 2015-03 | 2015-01-01 | 2015-03-31 |
| 2015-04 | 2015-02-01 | 2015-04-30 |
| 2015-05 | 2015-03-01 | 2015-05-31 |
| 2015-06 | 2015-04-01 | 2015-06-30 |
| 2015-07 | 2015-05-01 | 2015-07-31 |
| 2015-08 | 2015-06-01 | 2015-08-31 |

调整它，如果你真的想使用像 90 天的间隔。

现在它是一个简单的左连接登录表来得到你想要的:

select i.month as `Date`, count(distinct l.user_id) as `Active users`
from (
    select 
        date_format(date_sub(c.date, interval 1 day), '%Y-%m') as month,
        date_sub(c.date, interval 3 month) as first_day,
        date_sub(c.date, interval 1 day)   as last_day
    from calendar c
    where day(c.date) = 1
      and c.date between '2015-02-01' and '2015-09-01'
) i
left join login_table l on l.login_date between i.first_day and i.last_day
group by i.month

http://sqlfiddle.com/#!9/d1bb0/3