网站搭建需要的公司,营销渠道策划方案,网页设计专业公司,养老网站备案必须做前置审批吗力扣题
1、题目地址
1454. 活跃用户
2、模拟表
表 Accounts#xff1a;
Column NameTypeidintnamevarchar
id 是该表主键#xff08;具有唯一值的列#xff09;该表包含账户 id 和账户的用户名.
表 Logins#xff1a;
Column NameTypeidintlogin_datedate
该表可能…力扣题
1、题目地址
1454. 活跃用户
2、模拟表
表 Accounts
Column NameTypeidintnamevarchar
id 是该表主键具有唯一值的列该表包含账户 id 和账户的用户名.
表 Logins
Column NameTypeidintlogin_datedate
该表可能包含重复项.该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.
3、要求
活跃用户 是指那些至少连续 5 天登录账户的用户。
编写解决方案, 找到 活跃用户 的 id 和 name。
返回的结果表按照 id 排序 。
结果表格式如下例所示。
示例 1
输入 Accounts 表
idname1Winston7Jonathan
Logins 表
idlogin_date72020-05-3012020-05-3072020-05-3172020-06-0172020-06-0272020-06-0272020-06-0312020-06-0772020-06-10
输出
idname7Jonathan
解释 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 numFROM (SELECT DISTINCT id, login_date FROM Logins) AS one
) AS twoLEFT 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_dtFROM Logins) LoginsGROUP BY id, reference_dt
) LoginsINNER 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_dtreference_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—— 滑动窗口函数用法
