以下数据为用户访问网页的时间,将数据按照连续的访问记录如果时间间隔小于 60 秒进行分组 ,字段分别为id,ts(秒)
u1001,1655334234
u1001,1655334256
u1002,1655334278
u1001,1655334334
u1002,1655334434
u1001,1655334534
u1001,1655334544
u1002,1655334634
u1001,1655334638
u1001,1655334654
1.将数据下移一行,得到间隔时间
select
id,ts,ts-lag_ts as diff_ts
from
(select
id,ts,
lag(ts,1,0) over(partition by id order by ts ) as lag_ts
from tmp.table_test2) t1;
2.开窗函数累加
select
id,ts,
sum(if(diff_ts>60,1,0)) over(partition by id order by ts)
from
(select
id,ts,ts-lag_ts as diff_ts
from
(select
id,ts,
lag(ts,1,0) over(partition by id order by ts ) as lag_ts
from tmp.table_test2
) t1
)t2;