面试题 — 连续登录问题的简单记录
标签搜索

面试题 — 连续登录问题的简单记录

海绵
2022-05-05 / 0 评论 / 57 阅读 / 正在检测是否收录...

面试题 — 连续登录问题的简单记录

连续问题,可谓是常见的面试题目了。对于连续登录天数这类问题,明白了开窗的作用,基本上是手到擒来。

求最大登陆天数====>直接做差值分组

思路:对每一行的数据进行编号排序,使用表中的时间列做一个 date_sub 的操作,倘若是连续的,则 date_sub 后的时间是一样的。然后做一个 group by 的操作,count 一下条数,然后取 max 即可获得最大值。

1001    2017-01-01    2016-12-31
1001    2017-01-02    2016-12-31
1001    2017-01-04    2017-01-01
1001    2017-01-05    2017-01-01
1001    2017-01-06    2017-01-01
1001    2017-01-07    2017-01-01


with a as (
SELECT *
    , DATE_SUB(login_date,INTERVAL ROW_NUMBER() over (partition by user_id order by login_date) DAY) as row_date 
FROM login_log
) , b as (
select user_id , row_date, count(row_date) as count_date
from a 
group by user_id , row_date
)
select user_id , max(count_date)
from b 
group by user_id 

求连续登录天数的进阶版====>取上一条判断

目前的需求变成了,只要时间差距在两天内,仍然算连续登录。

上面的方法已经不在使用了。做差的这个方法成立是需要时间是严格到每天的。但是中间出现间隔,这种情况已经不适用了。

对比一下上面的,这种情况减出来的已经不一样了。我们的题目的意思是:只要时间差距在两天内,仍然算连续登录。。。按道理这里的数据应该都是被是为连续的。

id        date        date_sub(date-rank_num)
1001    2017-01-01    2016-12-31
1001    2017-01-02    2016-12-31
1001    2017-01-04    2017-01-01
1001    2017-01-06    2017-01-02
1001    2017-01-08    2017-01-03
1001    2017-01-10    2017-01-04

SELECT *
    , lag(login_date , 1 , login_date) over (partition by user_id) as lag_date
    , DATEDIFF(login_date,lag(login_date , 1 , login_date) over (partition by user_id)) as diff_num
FROM login_log

-- 上面的sql是一个取上一条的一个开窗函数,以及一个 datediff 去判断两个时间的差值。
id        date        lag_date    date_sub(date,lag_date)
1001    2017-01-01    2017-01-01    0
1001    2017-01-02    2017-01-01    1
1001    2017-01-06    2017-01-02    4
1001    2017-01-08    2017-01-06    2
1001    2017-01-12    2017-01-08    4
1001    2017-01-13    2017-01-12    1
1001    2017-01-15    2017-01-13    2
1001    2017-01-16    2017-01-15    1

从date的时间列去看,符合题意是有三个连续的登录时间的。我们现在要做的就是 根据date_sub 这一列的数据去做分组。

如何分区:结合题意,只要时间差距在两天内,仍然算连续登录。

这里判断肯定是用到了 if 去做判断,如果用到了 if 做判断,我们可以思维惯性地使用 sum 去累加做分组。

这可以总结一下:什么时候会用到 sum去做分组?

第一点:用到了 if 做判断的。第二点:跟上下行关系大的。

image-20220426163922023

看上图,我们就完成了分组了。。慢慢体会吧。

求连续问题的另一种解法====>两次排序分组

b站上面有一个讲解的视频:点击可到达。SQL面试题:基于扫码记录查找密接人员_哔哩哔哩_bilibili

sql的建表语句:

-- 创建示例表和数据
CREATE TABLE trail(
  uid varchar(11) NOT NULL,
  area varchar(10) NOT NULL,
  scan_time timestamp);

INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 09:00:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 10:00:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 11:00:00');
INSERT INTO trail VALUES ('13011111111', 'A002', '2022-05-01 11:05:00');
INSERT INTO trail VALUES ('13011111111', 'A002', '2022-05-01 13:00:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 13:15:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 14:00:00');
INSERT INTO trail VALUES ('13022222222', 'A001', '2022-05-01 10:30:00');
INSERT INTO trail VALUES ('13022222222', 'A001', '2022-05-01 12:00:00');
INSERT INTO trail VALUES ('13033333333', 'A001', '2022-05-01 11:00:00');
INSERT INTO trail VALUES ('13033333333', 'A001', '2022-05-01 12:00:00');
INSERT INTO trail VALUES ('13033333333', 'A001', '2022-05-01 13:00:00');

- 问题一:如何找出用户在每个区域的停留开始时间和结束时间?

可以看我着两个案例,都可以实现分组的问题,读者可以尝试一下自己创建表,写一下这个案例。

image-20220504133417915

image-20220504133618107

2

打赏

海报

正在生成.....

评论 (0)

取消