mysql-php statistics seven-day retention rate

The database has about 50,000 login details. How to check the table and calculate the seven-day retention rate more efficiently?

Reply content:

The database has about 50,000 login details. How to check the table and calculate the seven-day retention rate more efficiently?

Combined registration array key values ​​are in datetime year, month and day format;
For example: 2016-08-11

<code>$arr = [
    '2016-08-11' => [2,3,5,6,111,333,23],//存储当天注册的用户id
    '2016-08-12' => [],
    ....
]
</code>

Check the retention rate of users on 2016-08-11

<code> select count(*) from operation_log where client_id in() and login_time bewteen 1 and 2
</code>

Number of logins on 2016-08-17/Number of logged-in users on 2016-08-11 = Retention rate

You can do whatever you want with more than 50,000, and direct PHP array processing will not consume too much memory.
You can consider using memcache or redis to store intermediate results.