n

问题

nnnn

有如下访问网站的数据，包括用户id和访问时间两个字段。如果某个用户的连续的访问记录时间间隔小于60秒，则属于同一个会话，现在需要计算每个用户有多少个会话。

nnnn

比如A用户在第1秒，60秒，200秒，230秒有三次访问记录，则该用户有2个会话，其中第一个会话是第1秒和第60秒的记录，第二个会话是第200秒和230秒的记录。

nnnn

user_id     tsn1001    16920000000n1001    16920000050n1002    16920000065n1002    16920000080n1001    16920000150n1002    16920000160

nnnn

解答

nnnn

user_id	ts	判断与上一行差值是否小于60	开窗累加当做会话编号
A	1	0	0
A	60	0	0
A	200	1	1
A	230	0	1

nnnn

with tmp as (n    select 1001 as user_id,16920000000 as tsn    union alln    select 1001 as user_id,16920000050 as tsn    union alln    select 1002 as user_id,16920000065 as tsn    union alln    select 1002 as user_id,16920000080 as tsn    union alln    select 1001 as user_id,16920000150 as tsn    union alln    select 1002 as user_id,16920000160 as tsn)n nselectn	user_idn	,count(distinct user_group) as user_group_cntnfromn(n selectn    user_idn    ,tsn    -- 开窗做累加n    ,sum(flag) over(partition by user_id order by ts) as user_groupn    fromn    (nselectn        user_idn        ,tsn        -- 判断当前行的时间与上一行的差值n        ,if(ts-last_ts<60,0,1) as flagn        fromn        (nselectn user_idn ,tsn -- 取当前行的上一个时间，没有上一行就给自身的时间n ,lag(ts,1,ts) over(partition by user_id order by ts) as last_tsn from tmpn)t1n)t1n)t1ngroup by user_id;

n