select id,count(1) log_num
from(
SELECT id,
DATE_SUB(dt,INTERVAL (ROW_NUMBER() over (PARTITION by id ORDER BY dt)) day) rn
FROM(
select id,SUBSTR(date,1,10) dt
FROM `连续登录`
GROUP BY id,dt
)a
)b
GROUP BY id,rn
字段只有id,date,第一个解法如上
用row_number排序之后,再减去他们的排名,如果是一样的,那就是连续登录,不一样就是不连续(不记得就自己写着看看)
date_sub之后就是他们相同与否的日期,这个不能直接count,因为这个字段的结果不一样,后面算连续登录几天的时候还要借助这个进行分组count
这个解法会把只出现1天的也算出来,不过问题不大,题目要多少,回头写个条件筛选一下
聚合和group by,类似于数透
第二个解法,用lag&lead,貌似只能展现至少连续登录3天的,全展现出来的我目前还不会
SELECT id
from(
SELECT id,dt,
LAG(dt) over (PARTITION by id ORDER BY dt) pre_dt,
lead(dt) over (PARTITION by id ORDER BY dt) ne_dt
FROM(
select id,
SUBSTR(date,1,10) dt
FROM `连续登录`
GROUP BY id,dt
) a
)b
where DATEDIFF(dt,pre_dt) =1 and DATEDIFF(ne_dt,dt) =1
GROUP BY id
SELECT id,SUM(ds)
FROM(
SELECT id,stdt,
(case
when ldt is NULL then 0
when DATEDIFF(stdt,ldt) =1 then 1
else 0 end ) ds
FROM(
SELECT id, stdt,lag(stdt) over (PARTITION by id ORDER BY stdt) ldt
FROM(
select id, substr(date,1,10) stdt
from log_reverse
) t1
) t2
) t3
GROUP BY id
-- 这个只能算至少连续登录三天的
这个就很直观,不用想的那么复杂(函数会用的话),但就是不知道咋把他们每一天登陆的写出来