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呢,而是记为当前登录日期
方法三为什么要把最后一条数据拿出来特殊处理呢
聪明的你,知道其中的原因吗?欢迎私下交流。。。。