写在前面
连续登陆问题可以扩展到很多其他方面:
1.我们每天使用的某团外卖,就可以统计用户连续点外卖的天数。如果发现你连续5天都没有点外卖,可能就会给你发优惠券,刺激消费
2.比如银行贷款你连续预期好多好多天,一直没有处理。银行就会按照自己的流程对你采集一定的措施。
反正就是确实在数仓中还是用的比较多的,比较实际的需求。
需求:编写hql,统计连续3天登陆的用户
分析:如何判断连续,这个是本题的关键
可以对user_id进行分组排序后,得到rn,然后用登陆日期减去rn ,就会得到一个日期差,如果是连续的话,这个日期差值应该也是连续一致的。
SQL实现
select
user_id,
--date_sub(login_date, t1.rn) as date_flag,
count(1) as cnt
from
(
select user_id,
login_date,
row_number() over(partition by user_id order by login_date asc) rn
from user_login) t1
group by user_id, date_sub(login_date, t1.rn)
having cnt >=3;
执行,得到正确结果
这里还提供另外一种写法:
select distinct user_id
from
(
select user_id,
login_date,
lead(login_date, 2, -1) over(partition by user_id order by login_date desc) as date_flag
from user_login
group by user_id,login_date) t1
where date_sub(cast(t1.login_date as date), 2) = cast(t1.date_flag as date);