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

2024-01-10 12:48:48

力扣题

1、题目地址

1454. 活跃用户

2、模拟表

表 Accounts:

Column NameType
idint
namevarchar
  • id 是该表主键(具有唯一值的列)
  • 该表包含账户 id 和账户的用户名.

表 Logins:

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

3、要求

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

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

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

结果表格式如下例所示。

示例 1:

输入:
Accounts 表:

idname
1Winston
7Jonathan

Logins 表:

idlogin_date
72020-05-30
12020-05-30
72020-05-31
72020-06-01
72020-06-02
72020-06-02
72020-06-03
12020-06-07
72020-06-10

输出:

idname
7Jonathan

解释:
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)—— 滑动窗口函数用法

文章来源:https://blog.csdn.net/weixin_50223520/article/details/135494316
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。