现有各用户的登录记录表（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, login_date) 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, 12 if(datediff(login_date, last_login_date) < 3, 13 max(login_date) over(partition by user_id order by login_date), 14 'null') max_login_date, -- 截至到当前时间满足连续登录的最大的登录日期 15 if(datediff(login_date, last_login_date) < 3, 16 min(login_date) over(partition by user_id order by login_date), 17 'null') min_login_date -- 截至到当前时间满足连续登录的最小的登录日期 18 from tt 19 ) 20 select user_id, max(datediff(max_login_date, min_login_date) + 1) max_day_count 21 from tt1 22 group by user_id