一、简介
连续登录问题，不管是数据研发、还是数据分析，都是一个经典面试SQL题，本文从易到难，简单拓展，举一反三。
二、表结构
注：去过重的。
CREATE TABLE user_login(id int COMMENT '用户主键',dt string COMMENT '登录日期') |
三、表数据
| user_login.id user_login.dt1001 2021-12-121002 2021-12-121001 2021-12-131001 2021-12-141001 2021-12-161002 2021-12-161001 2021-12-191002 2021-12-171001 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：根据步骤四结果去重，获得最后结果 |
五、扩展总结
| 连续登录问题，其实可扩展为划分组别的问题。如果是时间不固定，比如求每个用户最大连续登录天数，那么方法一和三就失效了。如果仅仅是题目要求，求固定连续登录天数，那么个人更倾向于方法三。 |
文章来源于数据仓库与Python大数据
