创建数据
drop table if exists tmp.luoxiaoxin1;
CREATE table if not exists tmp.luoxiaoxin1 as select 10001 as id,'2021-07-01' as `date`
union all
select 10001 as id,'2021-07-02' as `date`union allselect 10001 as id,'2021-07-02' as `date`
union all
select 10001 as id,'2021-07-04' as `date`union allselect 10002 as id,'2021-07-01' as `date`
union all
select 10002 as id,'2021-07-02' as `date`union allselect 10002 as id,'2021-07-03' as `date`;
这里解释一下为啥要用dense_rank() 因为碰到一个面试官给的登陆数据中有重复数据。\
select id
,`date`
,rn
,date_sub(`date`, rn)
--,count(*) a
from(select distinct id
,`date`
,dense_rank() over (partition by id order by `date`) as rn
from tmp.luoxiaoxin1
) tmp
select id
--,`date`
--,rn
,date_sub(`date`, rn) ,count(*) a
from(select distinct id
,`date`
,dense_rank() over (partition by id order by `date`) as rn
from tmp.luoxiaoxin1
) tmp
group by id
,date_sub(`date`, rn)having count(*)>=3
