数仓面试——连续登录问题

一、简介

连续登录问题,是一个经典sql,本文从易到难,简单拓展,有更好方法的同学,欢迎私下交流

二、表结构(去过重的)

CREATE TABLE `user_login`(

`id` int COMMENT '用户主键',

`dt` string COMMENT '登录日期')

三、表数据

user_login.id        user_login.dt

1001        2021-12-12

1002        2021-12-12

1001        2021-12-13

1001        2021-12-14

1001        2021-12-16

1002        2021-12-16

1001        2021-12-19

1002        2021-12-17

1001        2021-12-20

四、需求: 求出连续3天登录的用户id

方法一:自关联

SELECT
tmp2.id
FROM
(
SELECT
tmp.id,
tmp.dt
FROM
(
SELECT
ul1.id,
ul1.dt
FROM
user_login ul1
INNER JOIN
user_login ul2
ON ul1.id = ul2.id
WHERE
ul2.dt BETWEEN date_sub(ul1.dt, 2) AND ul1.dt
) tmp
GROUP BY
tmp.id,
tmp.dt
HAVING
count(1) = 3
) tmp2
group by
tmp2.id

详解:

1:因为是固定的3天,所以可以采用主键自关联方法,过滤条件为副表ul2的登录时间在主表登录时间和主表登录时间减去两天的区间内

2:然后根据主表的id,和登录时间分组,分组后数量正好是3天的,说明连续三天时间都有登录

3:根据步骤二的结果去重,获取结果

方法二:巧用窗口函数row_number

SELECT
tmp2.id
FROM
(
SELECT
id,
date_sub(dt, rn) dt_line
FROM
(
SELECT
id,
dt,
row_number () over (PARTITION BY id ORDER BY dt) rn
FROM
user_login
) tmp
GROUP BY
id,
date_sub(dt, rn)
HAVING
count(1) >= 3
) tmp2
group by
tmp2.id

详解:

1:利用row_number 提供一个自增序列

2:如果前后连续,那么登录日期减去步骤一提供的序列号结果相等

3:根据步骤二提供的日期基准,和用户id分组,过滤出大于等于3的结果

4:根据步骤三的结果去重,获取结果

方法三:采用窗口函数 lag(lead)

SELECT
tmp2.id
FROM
(
SELECT
id,
IF (datediff(dt, lag_dt) = 2, 1, 0) flag
FROM
(
SELECT
id,
dt,
lag (dt, 2, '0000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
FROM
user_login
) tmp
) tmp2
WHERE
tmp2.flag = 1
GROUP BY
tmp2.id

详解:

1:因为是固定的3天,所以可以采用lag(lead)方法,求出前面第二个的日期与当前的日期差

2:根据步骤一的结果,判断如果日期差等于2,则是连续登录

3:根据步骤二的结果过滤分组,获取结果

方法四: 比对相邻数据日期,可扩展为组别划分

SELECT
id
FROM
(
SELECT
id,
max_dt_line
FROM
(
SELECT
id,
dt,
lag_dt,
max(dt_line) over (PARTITION BY id ORDER BY dt) max_dt_line
FROM
(
SELECT
id,
dt,
lag_dt,
IF (datediff(dt, lag_dt) = 1,"0",dt) dt_line
FROM
(
SELECT
id,
dt,
lag (dt, 1, '000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
FROM
user_login
) tmp
) tmp1
) tmp2
GROUP BY
id,
max_dt_line
HAVING
count(1) >= 3
) tmp4
GROUP BY
id;

详解:

1:利用lag(lead) 比较当前日期与上一次登录日期的差

2:如果日期差为1,则连续登录,记为0,否则记录当前登录日期

3:利用窗口函数max,获取用户登录基准日期

4:根据基准日期分组,过滤出大于等于3天的数据

5:根据步骤四结果去重,获得最后结果

五、拓展

连续登录问题,其实可扩展为划分组别的问题

如果是时间不固定,比如求每个用户最大连续登录天数,那么方法一和三就失效了

如果仅仅是题目要求,求固定连续登录天数,那么个人更倾向于方法三

原文链接:数仓面试——连续登录问题