SQL之断点连续问题

1001    2022-04-02
1001    2022-04-04
1001    2022-04-05
1001    2022-04-07
1001    2022-04-11
1001    2022-04-12
1001    2022-04-14
1002    2022-04-03
1002    2022-04-05
1002    2022-04-07
1002    2022-04-09
1002    2022-04-11
1002    2022-04-15
1002    2022-04-16
上述数据表示用户登录的日期(已经去重)
计算每个用户连续登录的最大天数,注意:断一天也算连续

select
    id,
    dt,
    lag(dt,1,'1970-01-01')over(partition by id order by dt) lag_dt
from new_test t1

+-------+-------------+-------------+
|  id   |     dt      |   lag_dt    |
+-------+-------------+-------------+
| 1001  | 2022-04-02  | 1970-01-01  |
| 1001  | 2022-04-04  | 2022-04-02  |
| 1001  | 2022-04-05  | 2022-04-04  |
| 1001  | 2022-04-07  | 2022-04-05  |
| 1001  | 2022-04-11  | 2022-04-07  |
| 1001  | 2022-04-12  | 2022-04-11  |
| 1001  | 2022-04-14  | 2022-04-12  |
| 1002  | 2022-04-03  | 1970-01-01  |
| 1002  | 2022-04-05  | 2022-04-03  |
| 1002  | 2022-04-07  | 2022-04-05  |
| 1002  | 2022-04-09  | 2022-04-07  |
| 1002  | 2022-04-11  | 2022-04-09  |
| 1002  | 2022-04-15  | 2022-04-11  |
| 1002  | 2022-04-16  | 2022-04-15  |
+-------+-------------+-------------+

select
    id,
    dt,
    lag_dt,
    datediff(dt,lag_dt) d_diff
from (select
    id,
    dt,
    lag(dt,1,'1970-01-01')over(partition by id order by dt) lag_dt
from new_test)t1  t2
+-------+-------------+-------------+---------+
|  id   |     dt      |   lag_dt    | d_diff  |
+-------+-------------+-------------+---------+
| 1001  | 2022-04-02  | 1970-01-01  | 19084   |
| 1001  | 2022-04-04  | 2022-04-02  | 2       |
| 1001  | 2022-04-05  | 2022-04-04  | 1       |
| 1001  | 2022-04-07  | 2022-04-05  | 2       |
| 1001  | 2022-04-11  | 2022-04-07  | 4       |
| 1001  | 2022-04-12  | 2022-04-11  | 1       |
| 1001  | 2022-04-14  | 2022-04-12  | 2       |
| 1002  | 2022-04-03  | 1970-01-01  | 19085   |
| 1002  | 2022-04-05  | 2022-04-03  | 2       |
| 1002  | 2022-04-07  | 2022-04-05  | 2       |
| 1002  | 2022-04-09  | 2022-04-07  | 2       |
| 1002  | 2022-04-11  | 2022-04-09  | 2       |
| 1002  | 2022-04-15  | 2022-04-11  | 4       |
| 1002  | 2022-04-16  | 2022-04-15  | 1       |
+-------+-------------+-------------+---------+

select
    id,
    dt,
    sum(if(d_diff>2,1,0))over(partition by id order by dt) flag
from (select
    id,
    dt,
    lag_dt,
    datediff(dt,lag_dt) d_diff
from (select
    id,
    dt,
    lag(dt,1,'1970-01-01')over(partition by id order by dt) lag_dt
from new_test)t1 )t2  t3

+-------+-------------+-------+
|  id   |     dt      | flag  |
+-------+-------------+-------+
| 1001  | 2022-04-02  | 1     |
| 1001  | 2022-04-04  | 1     |
| 1001  | 2022-04-05  | 1     |
| 1001  | 2022-04-07  | 1     |
| 1001  | 2022-04-11  | 2     |
| 1001  | 2022-04-12  | 2     |
| 1001  | 2022-04-14  | 2     |
| 1002  | 2022-04-03  | 1     |
| 1002  | 2022-04-05  | 1     |
| 1002  | 2022-04-07  | 1     |
| 1002  | 2022-04-09  | 1     |
| 1002  | 2022-04-11  | 1     |
| 1002  | 2022-04-15  | 2     |
| 1002  | 2022-04-16  | 2     |
+-------+-------------+-------+


select
    id,
    flag,
    (datediff(max(dt),min(dt)) + 1) days
from
    (select
    id,
    dt,
    sum(if(d_diff>2,1,0))over(partition by id order by dt) flag
from (select
    id,
    dt,
    lag_dt,
    datediff(dt,lag_dt) d_diff
from (select
    id,
    dt,
    lag(dt,1,'1970-01-01')over(partition by id order by dt) lag_dt
from new_test)t1 )t2)t3
group by id,flag;
+-------+-------+-------+
|  id   | flag  | days  |
+-------+-------+-------+
| 1001  | 1     | 6     |
| 1001  | 2     | 4     |
| 1002  | 1     | 9     |
| 1002  | 2     | 2     |
+-------+-------+-------+