连续登录天数分析

基础思路先对数据按照用户和日期进行去重每一天只保存一次数据---然后使用date_subdt,row_number() over(partition by id order by asc)统一化数据实例分析user_id(用户id)ip_address(ip地址)login_ts(登录时间)logout_ts(登出时间)101180.149.130.1612021-09-21 08:00:002021-09-27 08:30:00102120.245.11.22021-09-22 09:00:002021-09-27 09:30:0010327.184.97.32021-09-23 10:00:002021-09-27 10:30:00先进行去重然后获取标准化数据select user_id, login_ts, date_sub(login_ts,row_number() over (partition by user_id order by login_ts asc))as dt_s from (SELECT user_id, login_ts FROM user_login_detail GROUP BY user_id, login_ts )t1然后再对dts进行user_id和dt_s进行分组窗口就是这一段数据我们脑子里最好保存着这么一个窗口便于后面使用聚合函数使用count统计连续天数用min(create_date)统计初始日期max统计结束日期select user_id, count(1) as num, min(login_ts) as min_dt, max(login_ts) as max_dt from (select user_id, login_ts, date_sub(login_ts, row_number() over (partition by user_id order by login_ts asc)) as dt_s from (SELECT user_id, login_ts FROM user_login_detail GROUP BY user_id, login_ts) t1) t2 group by user_id, t2.dt_s