php - Mysql - 日期范围内的用户分析

Question

我有一个事件跟踪器表，其中包含 activity_id(主键，自动增量)、user_id、api_function 和 date_added 字段(请查找随附的屏幕截图)。

通过使用以下查询，我能够计算过去 28 天内每个用户的条目数:

SELECT COUNT( DISTINCT date(date_Added) ) AS day_of_activity, user_id
       FROM activity_tracker
       WHERE date_added >= DATE( NOW() ) - INTERVAL 28 DAY
       GROUP BY user_id
       LIMIT 0 , 30

喜欢:

days_of_activity    user_id
34  1
1   3
13  9
2  10
1  11
8  12

我需要跟踪具有以下特征的用户数量:

 more than 16 entries in the past 28 days
 between 6 to 16 in the past 28 days, 
 1 to 6 in the past 28 days, 
 no entries in the past 30 days, 
 no entries in the past 90 days and 
 no entries in the past 180 days. 

是否可以在单个 mysql 查询中执行此操作？

请帮助我。提前致谢。

Answer 1

请在下面找到答案:

SELECT 

SUM(
CASE WHEN day_of_activity>16  AND last_activity_date >= DATE(NOW()) - INTERVAL 28 DAY  
THEN 1 ELSE 0
END)
as daily_users_count,

SUM(
CASE WHEN day_of_activity>6 AND  day_of_activity <=16 AND last_activity_date >= DATE(NOW()) - INTERVAL 28 DAY 
THEN 1 ELSE 0
END)
as weekly_users_count,

SUM(
CASE WHEN day_of_activity>=1 AND  day_of_activity <=6 AND last_activity_date >= DATE(NOW()) - INTERVAL 28 DAY 
THEN 1 ELSE 0
END)
as monthly_users_count,

SUM(
CASE WHEN DATE_SUB(CURDATE(),INTERVAL 30 DAY) >= last_activity_date OR last_activity_date IS NULL 
THEN 1 ELSE 0
END)
as not_in_30,

SUM(
CASE WHEN DATE_SUB(CURDATE(),INTERVAL 90 DAY) >= last_activity_date OR last_activity_date IS NULL 
THEN 1 ELSE 0
END)
as not_in_90,

SUM(
CASE WHEN DATE_SUB(CURDATE(),INTERVAL 180 DAY) >= last_activity_date OR last_activity_date IS NULL  
THEN 1 ELSE 0
END)
as not_in_180

FROM (
SELECT COUNT(DISTINCT date(at.date_added)) as day_of_activity, a.user_id, max(at.date_added) as last_activity_date
FROM accounts a 
LEFT JOIN activity_tracker at ON a.user_id = at.user_id
WHERE a.user_role_id = 2
GROUP BY a.user_id
)temp