【MySQL】计算时间段内满足是首次登录的用户（根据时间统计首次登录用户人数）

力扣题

1、题目地址

1107. 每日新用户统计

2、模拟表

Traffic 表：

Column Name	Type
user_id	int
activity	enum
activity_date	date

• 该表可能有重复的行。
• activity 列是 ENUM 类型，可能取 (‘login’, ‘logout’, ‘jobs’, ‘groups’, ‘homepage’) 几个值之一。

3、要求

• 编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。
• 以 任意顺序 返回结果表。

4、示例

输入：

Traffic 表：

user_id	activity	activity_date
1	login	2019-05-01
1	homepage	2019-05-01
1	logout	2019-05-01
2	login	2019-06-21
2	logout	2019-06-21
3	login	2019-01-01
3	jobs	2019-01-01
3	logout	2019-01-01
4	login	2019-06-21
4	groups	2019-06-21
4	logout	2019-06-21
5	login	2019-03-01
5	logout	2019-03-01
5	login	2019-06-21
5	logout	2019-06-21

输出：

login_date	user_count
2019-05-01	1
2019-06-21	2

解释：

请注意，我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01，因此他不算在 2019-06-21 的的统计内。

5、代码编写

要求分析

我们是要查询出 2019-06-30 的最近 90 天（包括 90 天）的首次登录用户，有四种情况

1. 最近 90 天只出现一次登录，90天以外没有，查询出来就是首次登录时间
2. 最近 90 天只出现一次登录，90天以外也出现登录，查询不出来
3. 最近 90 天出现多次登录，90天以外没有，需要进行筛选查询出 最小时间 那个才是首次登录时间
4. 最近 90 天出现多次登录，90天以外也出现登录，查询不出来

第一种写法

SELECT login_date, COUNT(one.user_id) AS user_count
FROM (
    SELECT user_id, MIN(activity_date) AS login_date
    FROM Traffic
    WHERE activity = 'login'
    AND activity_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
    AND user_id NOT IN (
    	SELECT user_id 
    	FROM Traffic 
    	WHERE activity = 'login' 
    	AND activity_date < DATE_SUB('2019-06-30', INTERVAL 90 DAY)
    )
    GROUP BY user_id
) AS one
GROUP BY login_date

代码分析：

• 查询最近 90 天

activity_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)

• 将最近 90 天以外的进行排除，对应 要求分析 里面的 2 和 4 的情况

user_id NOT IN (
	SELECT user_id 
	FROM Traffic 
	WHERE activity = 'login' 
	AND activity_date < DATE_SUB('2019-06-30', INTERVAL 90 DAY)
)

• 对应 要求分析 里面的 1 和 3 情况，主要是 3 情况

MIN(activity_date)

第二种写法（理解要求简化写法）

SELECT login_date, COUNT(one.user_id) AS user_count
FROM (
    SELECT user_id, MIN(activity_date) AS login_date
    FROM Traffic
    WHERE activity = 'login'
    GROUP BY user_id
    HAVING login_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
) AS one
GROUP BY login_date

代码分析：

• 首先找出各个用户的首次登录时间

SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
WHERE activity = 'login'
GROUP BY user_id

• 再看哪些是满足最近90天内的

SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
WHERE activity = 'login'
GROUP BY user_id
HAVING login_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)