1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
select
    distinct month, user_id 
from (
    select
        user_id,
        date_format(login_time, "yyyy-MM") month,
        row_number over(partition by user_id, new_login_time order by new_login_time asc) as rn_1
    from (
        select
            user_id,
            login_time,
            date_sub(login_time, rn) new_login_time
        from (
            select
                user_id,
                login_time,
                row_number over(partition by user_id order by login_time asc) as rn
            from (
                select 
                    distinct user_id, 
                    date_format(login_time, "yyyy-MM-dd") login_time 
                from t
            ) t1
        ) t2
    ) t3
) t4
where rn_1 >= 5

1
2
3
//rn为行号标记
row_number over(partition by user_id order by login_time asc) as rn

1
2
3
//rn_1为行号标记
row_number over(partition by user_id, new_login_time order by new_login_time asc) as rn_1