数仓面试——连续登录问题进阶版
Hi, 我是小萝卜算子
一、引入
紧接上一篇文章：数仓面试——连续登录问题
表结构和数据一致
需求有所升级： 中间间隔一天，也算连续登录，求出连续4天登录的用户id（因为样例中1001用户连续3天登录，但是升级后，则可视为连续4天登录）
二、思考
由于间隔一天也算连续登录
数仓面试——连续登录问题中方法一/二/三的方式将不再有效，因为过滤条件变成不固定的筛选日期了
方法四依然有效，只需要将flag条件略加修改即可
三、需求： 求出连续4天登录的用户id，间隔一天登录也算连续
方法一：相对不灵活的方式
SELECT
id
FROM
(
SELECT
id,
base_dt
FROM
(
SELECT
id,
dt,
date_sub(base_dt1, dr) base_dt
FROM
(
SELECT
id,
dt,
date_sub(dt, rn) base_dt1,
dense_rank () over (
PARTITION BY id
ORDER BY
date_sub(dt, rn)
) dr
FROM
(
SELECT
id,
dt,
row_number () over (PARTITION BY id ORDER BY dt) rn
FROM
user_login
) tmp
) tmp1
) tmp2
GROUP BY
id,
base_dt
HAVING
count(1) >= 4
) tmp3
GROUP BY
id;详解：
1：利用rown_number，按用户分组，登录时间升序
2：本次登录日期减去步骤一生成自增序列，形成第一道日期基准
3：利用dense_rank，按用户分组，步骤二形成的日期基准升序
4：步骤二的日期基准减去步骤三的自增序列，形成最终的日期基准
5：按步骤四形成的用户和最终日期基准分组，过滤出次数大于等于4的数据
6：按照用户分组去重，获得最终结果
方法二：采用超过两天的登录间隔为分界线分组
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) <= 2,"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) >= 4
) tmp4
GROUP BY
id;详解：
1：利用lag（lead），按用户分组，登录时间升序，比较当前日期与上一个登录日期的差
2：如果日期差小于等于2，则连续登录，记为0，否则记录当前登录日期
3：利用窗口函数max，获取用户登录基准日期（连续登录为0，所以max为前标线）
4：根据基准日期分组，过滤出大于等于4天的数据
5：根据步骤四结果去重，获得最后结果
方法三：采用过滤掉连续登录的数据
SELECT
id
FROM
(
SELECT
id,
dt,
lag_dt,
rn,
cnt,
CASE
WHEN cnt = rn
AND dt_flag = 0 THEN
rn - lag (rn, 1, 0) over (PARTITION BY id ORDER BY dt) + 1
ELSE
rn - lag (rn, 1, 0) over (PARTITION BY id ORDER BY dt)
END lag_rn
FROM
(
SELECT
id,
dt,
lag_dt,
IF (datediff(dt, lag_dt) <= 2, 0, 1) dt_flag,
row_number () over (PARTITION BY id ORDER BY dt) rn,
count(1) over (PARTITION BY id) cnt
FROM
(
SELECT
id,
dt,
lag (dt, 1, '000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
FROM
user_login
) tmp
) tmp1
WHERE
dt_flag = 1
OR cnt = rn
) tmp2
WHERE
lag_rn >= 4
GROUP BY
id;详解：
1：利用lag（lead） 比较当前日期与上一次登录日期的差
2：如果日期差小于等于2，则连续登录，记为0，否则记为1，记为日期基准
3：添加一个row_number和 count 窗口函数
4：取出日期基准为1的数据和最后一条数据
5：比较当前行和前一行rn的差，为连续登录的天数（最后一行特殊处理）
6：根据用户去重，获得结果
方法四：采用sum分组
SELECT
id
FROM
(
SELECT
id,
base
FROM
(
SELECT
id,
dt,
lag_dt,
sum(
IF (datediff(dt, lag_dt) <= 2, 0, 1)
) over (PARTITION BY id ORDER BY dt) base
FROM
(
SELECT
id,
dt,
lag (dt, 1, '000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
FROM
user_login
) tmp
) tmp1
GROUP BY
id,
base
HAVING
count(1) >= 4
) tmp2
GROUP BY
id;详解：
1：利用lag（lead） 比较当前日期与上一次登录日期的差
2：如果日期差小于等于2，则连续登录，记为0，否则记为1，为日期基准
3：利用窗口函数sum，获取用户到当前行的和
4：连续登录用户步骤三求和结果相同（+0）
5：根据用户和步骤三求得的分组基准分组，并过滤出连续登录超过4天的用户
6：根据用户分组去重，获得结果
五、拓展
1：连续登录其实是分组问题的一个特例，只要把合适的数据划分到同一个组内，结果肯定是水到渠成的事
2：本文给的几个例子都是先求最基本的基准
方法一使用自增序列，获取一个临时基准，然后又用dense_rank，让同一基准内的数据划分到一起，最终获得分组的一个base_dt，但是此方法不灵活，需求修改为多天的话，需要大量修改代码，所以此方式不好
方法二设定日期基准线，如果是连续登录为0，不是则为当前登录日期，然后利用max窗口函数，按登录时间升序，获得分组的日期基准
方法三设定连续登录基准线，连续登录为0，不是则为1，然后添加一个自增序列，那么就可以把1的和最后一条提取出来，然后计算前后的序列差，但是要注意最后一条要特殊处理
方法四巧妙的利用sum窗口，基准为0的数据sum后还是数据本身，然后就能生成分组的基准
3：个人对类似分组操作，更倾向于方法二和方法四，简单有效，而且不用考虑特殊情况，同学们更喜欢哪种方式呢
六、想一想
方法一为什么第二步要用dense_rank呢
方法二为什么不能像方法三和四一样，不连续记为1呢，而是记为当前登录日期
方法三为什么要把最后一条数据拿出来特殊处理呢
聪明的你，知道其中的原因吗？欢迎私下交流。。。。