题目描述
(通过次数8,894 | 提交次数22,565,通过率39.42%)
表 Accounts: +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ id 是该表主键. 该表包含账户 id 和账户的用户名. 表 Logins: +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | login_date | date | +---------------+---------+ 该表无主键, 可能包含重复项. 该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次. 写一个 SQL 查询, 找到活跃用户的 id 和 name. 活跃用户是指那些至少连续5 天登录账户的用户. 返回的结果表按照 id 排序. 结果表格式如下例所示: 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 | +----+------------+ Result 表: +----+----------+ | id | name | +----+----------+ | 7 | Jonathan | +----+----------+ id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户. id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户. 进阶问题: 如果活跃用户是那些至少连续n天登录账户的用户,你能否写出通用的解决方案 来源:力扣(LeetCode) 链接:https://leetcode.cn/problems/active-users 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
--测试数据 Create table If Not Exists Accounts (id int, name varchar(10)); Create table If Not Exists Logins (id int, login_date date); insert into Accounts (id, name) values ('1', 'Winston'); insert into Accounts (id, name) values ('7', 'Jonathan'); insert into Logins (id, login_date) values ('7', '2020-05-30'); insert into Logins (id, login_date) values ('1', '2020-05-30'); insert into Logins (id, login_date) values ('7', '2020-05-31'); insert into Logins (id, login_date) values ('7', '2020-06-01'); insert into Logins (id, login_date) values ('7', '2020-06-02'); insert into Logins (id, login_date) values ('7', '2020-06-02'); insert into Logins (id, login_date) values ('7', '2020-06-03'); insert into Logins (id, login_date) values ('1', '2020-06-07'); insert into Logins (id, login_date) values ('7', '2020-06-10');
解题思路
这又是一道典型的求连续区间的问题。
对于求连续区间的问题,一般的解决方法是,先计算出字段值在全表中的序号,再计算出从某个起点出发到字段值中间经过的值的数量,最后再以序号-数量的结果进行分组统计。
“字段值在全表中的序号”比较好计算,使用开窗函数row_number可以很轻松的实现。
“从某个起点出发到字段值中间经过的值的数量”,对于时间来说,可以将一个比较小的日期定为起始日期(比如1900-01-01),然后计算当前日期距离起始日期经过的天数。使用datediff日期函数,可以实现。
不过,这道题有两点需要注意。
第一,源表Logins中,数据可能是重复的。也就是说,同一个id,同一天,可能有多条记录。这就需要在计算开始前,先对Logins进行去重操作。否则会影响结果的准确性。
第二,因为题目要求,计算出连续5天及以上登录的id。那么,同一个id,可能会存在多段时间都满足要求的情况。所以,对于计算结果,还需要再做一次去重操作。
参考SQL
未特别说明的情况下,参考SQL为基于MySQL8.0实现。
with tmp as ( select distinct id,login_date from Logins ) select c.id, d.name from ( select distinct b.id from ( select a.id, a.login_date, datediff(a.login_date,'1900-01-01') - row_number() over(order by a.id,a.login_date) rn_diff from tmp a ) b group by b.id,b.rn_diff having count(1)>=5 )c left join Accounts d on c.id = d.id order by c.id;
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。