求用户最大连续登陆天数mysql实现
with t_1 as( -- 使用timestampdiff(unit, begin, end) <= 30进行where的条件筛选,并使用distinct函数进行清洗去重得到新表t_1
select DISTINCT dimMemberID as user_id, date(dimDateID) as login_date
from fct_sales
where timestampdiff(day, date(dimDateID),(select max(dimDateID) from fct_sales)) <= 30
and dimMemberID <> 0
order by login_date
),t_2 as( -- 得到一个按user_id分组并有排序编号的新表t_2
select user_id
,login_date
,row_number()over(partition by user_id order by login_date asc ) as ranks
from t_1
),t_3 as ( -- 使用date_sub函数获得新的列gap和新表t_3
select user_id
,login_date
,ranks
,date_sub(login_date, interval ranks day) as gap
from t_2
), t_4 as ( -- 对t_3表按user_id、gap进行分组,并对组内的login_date进行count计数,得到表t_4
select user_id
,gap
,count(login_date) as num
from t_3
group by user_id
,gap
) ,t_5 as ( -- 按照user_id进行分组,使用max取出最大的num
select user_id
,max(num) as series_login -- 按照user_id分组,求得最大连续登陆天数
from t_4
group by user_id
)
select * ,count(1)over() as cnt from t_5;
- with t_1 as
- timestampdiff