根据过去90天的登录次数统计活跃用户 [英] Count active users based on logins last 90 days

问题描述

我正在尝试进行一个查询，该查询将导致显示一个列表，该列表逐月显示我们有多少活跃用户.我们将活动用户定义为最近90天内已登录的用户.

I am trying to make a query that will result in a list showing how many active users we have month by month. We define an active users as a user that have been logged in in the last 90 days.

我可以轻松地定义当前拥有的活跃用户的数量

I can easily define the number of active users we have right now by this

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

我的问题出在我必须逐月计算拥有的用户数量时 在这里，我必须数一次单次登录.

My problem comes when I have to count the amount of users we have month by month Here i have to count one single login several times.

如果我有一个用户在1月10日登录，并且从此不再登录，则该用户应在以下月份(即1月，2月，3月和4月)算作活动用户，即使我只有一个注册用户，用户

If I have a user that login at 10 of January, and never login again, this user should count as an active user in the following months: January, February, March and April, even though I only have one registration of the user

示例数据:

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

这应该导致类似这样的结果:

This should result in something like this:

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 

反正还有做这样的计数吗?

Is there anyway of doing a count like that?

推荐答案

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

Problems like this are easier to solve, if you have a calendar table with all the dates you need. If you don't have such a table you can create it with a query like this:

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(100K天)的表，并且仅消耗约2.5 MB.您可以根据需要进行调整.

That will create a table with dates from 1900-01-01 to 2173-10-15 (100K days) and consume only about 2.5 MB. You can adjust it to your needs.

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

Using the caledar table you can get three-month-ranges:

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天间隔之类的时间，请进行调整.

Adjust it, if you really want to use something like 90 days intervals.

现在，使用登录表进行简单的左连接即可获得所需的内容:

Now it's a simple left join with the login table to get what you want:

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

这篇关于根据过去90天的登录次数统计活跃用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！