【MySQL】巧用 DATE_SUB 函数判断时间是否连续

力扣题

1、题目地址

1454. 活跃用户

2、模拟表

表 Accounts：

Column Name	Type
id	int
name	varchar

• id 是该表主键（具有唯一值的列）
• 该表包含账户 id 和账户的用户名.

表 Logins：

Column Name	Type
id	int
login_date	date

• 该表可能包含重复项.
• 该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

3、要求

活跃用户 是指那些至少连续 5 天登录账户的用户。

编写解决方案, 找到 活跃用户 的 id 和 name。

返回的结果表按照 id 排序 。

结果表格式如下例所示。

示例 1：

输入：
Accounts 表：

id	name
1	Winston
7	Jonathan

Logins 表：

id	login_date
7	2020-05-30
1	2020-05-30
7	2020-05-31
7	2020-06-01
7	2020-06-02
7	2020-06-02
7	2020-06-03
1	2020-06-07
7	2020-06-10

输出：

id	name
7	Jonathan

解释：
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次，所以，Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次，6 天中有 5 天是连续的，所以，Jonathan 是活跃用户.

进阶问题：
如果活跃用户是那些至少连续 n 天登录账户的用户，你能否写出通用的解决方案?

4、代码编写

我的代码

逻辑是找到利用窗口函数，对不同 id 进行分组，再对各组里面的根据登录时间顺序排序，取前四天的时间到当前时间记录个数，只要个数满足大于等于 5 就满足条件，上面有说到 Logins 表是有可能出现重复的情况，所以需要提前去重。

SELECT DISTINCT two.id, three.name
FROM (
    SELECT *, COUNT(*) OVER (PARTITION BY id ORDER BY login_date range BETWEEN interval 4 day preceding AND current row) AS num
    FROM (SELECT DISTINCT id, login_date FROM Logins) AS one
) AS two
    LEFT JOIN Accounts three USING(id)
WHERE num >= 5

网友代码（巧用 DATE_SUB 函数）

代码

SELECT DISTINCT Logins.id, Accounts.name
FROM (
    SELECT id, reference_dt, COUNT(1) cnt 
    FROM (
        SELECT DISTINCT id, login_date, 
        	   DATE_SUB(login_date, INTERVAL DENSE_RANK() OVER ( PARTITION BY id ORDER BY login_date ASC ) DAY) reference_dt
        FROM Logins
    ) Logins
    GROUP BY id, reference_dt
) Logins
	INNER JOIN Accounts ON Logins.id = Accounts.id
WHERE cnt >= 5
ORDER BY id

代码分析

第一步：按 id 据 login_date 正序求 rank，这里用 DENSE_RANK()

SELECT DISTINCT id, login_date, 
       DENSE_RANK() OVER ( PARTITION BY id ORDER BY login_date ASC ) rk 
FROM Logins

结果如下，这一步看不懂没关系，可直接看下一步。

| id | login_date | rk |
| -- | ---------- | -- |
| 1  | 2020-05-30 | 1  |
| 1  | 2020-06-07 | 2  |
| 7  | 2020-05-30 | 1  |
| 7  | 2020-05-31 | 2  |
| 7  | 2020-06-01 | 3  |
| 7  | 2020-06-02 | 4  |
| 7  | 2020-06-03 | 5  |
| 7  | 2020-06-10 | 6  |

第二步：用 login_date - rank，求出 reference_dt，reference_dt 相同的 login_date 即为连续的 login_date

SELECT DISTINCT id, login_date, 
       DATE_SUB(login_date, INTERVAL DENSE_RANK() OVER ( PARTITION BY id ORDER BY login_date ASC ) DAY) reference_dt
FROM Logins

结果如下，很明显能看出不同 id 里面，只要 reference_dt 是相同的就一定代表 login_date 是连续的，之后我们只需要根据 id 和 reference_dt 进行分组，只要个数大于等于 5 就满足条件，查询出对应 id，再去连接 Accounts 查询其名字就可以

| id | login_date | reference_dt |
| -- | ---------- | ------------ |
| 1  | 2020-05-30 | 2020-05-29   |
| 1  | 2020-06-07 | 2020-06-05   |
| 7  | 2020-05-30 | 2020-05-29   |
| 7  | 2020-05-31 | 2020-05-29   |
| 7  | 2020-06-01 | 2020-05-29   |
| 7  | 2020-06-02 | 2020-05-29   |
| 7  | 2020-06-03 | 2020-05-29   |
| 7  | 2020-06-10 | 2020-06-04   |

DENSE_RANK 函数可参考：MYSQL 窗口函数（Rows & Range）—— 滑动窗口函数用法