hivesql练习_间断连续登录用户问题
现有各用户的登录记录表（login_events）如下，表中每行数据表达的信息是一个用户何时登录了平台。
| user_id | login_datetime |
|---|---|
| 100 | 2021-12-01 19:00:00 |
| 100 | 2021-12-01 19:30:00 |
| 100 | 2021-12-02 21:01:00 |
现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续6天登录。期望结果如下：
| user_id <int> (用户id) | max_day_count <int> (最大连续天数) |
|---|---|
| 100 | 3 |
| 101 | 6 |
| 102 | 3 |
| 104 | 3 |
| 105 | 1 |
解答：
1 with tt as( 2 select user_id, login_date, lag(login_date, 1, '1970-01-01') over(partition by user_id order by login_date) last_login_date -- 上一次登录的日期，默认值设置为最小时间 3 from 4 ( 5 select user_id, date_format(login_datetime, 'yyyy-MM-dd') login_date -- 格式化时间格式 6 from login_events 7 group by user_id, date_format(login_datetime, 'yyyy-MM-dd') 8 ) t1 9 ), 10 tt1 as( 11 select user_id,login_date, 12 sum(if(datediff(login_date, last_login_date) > 2, 1, 0)) over(partition by user_id order by login_date) ts -- 满足连续登录天数条件。统计截至到当前时间共有几次满足连续登录天数条件 13 from tt 14 ), 15 tt2 as( 16 select user_id, datediff(max(login_date), min(login_date)) + 1 day_count -- 每次连续登录的天数 17 from tt1 18 group by user_id, ts 19 ) 20 select user_id, max(day_count) max_day_count -- 最大的连续登录天数 21 from 22 tt2 23 group by user_id
