只有时间戳时求平均停留时长的思路

        在出数据时，有一个需求是求平均停留时长的，但现有的埋点只记录了用户访问访问和点击数据，没有登入登出记录。最开始的计算方法只是很粗暴的。就是把用户按天分组，计算一个用户一天内第一次访问记录和最后一次访问记录时间差，然后过滤掉不合理的数据，按天求平均值。这种方法是不合理的，只要用户登录两次以上并且中间间隔比较大，就会被过滤掉。

        sql如下

select
	sum(diff)/count(cookie)
from
(select day,timestamp,cookie,(max(timestamp) - min(timestamp))/1000 diff from log
where url like '%xxxxx%'
and day>='2021-03-29'
and day<='2021-04-04'
group by day,cookie
having diff >=1 and diff <= 1800) t

现在有一个新的思路，就是把时间戳排序后错开一行求差就是两次用户访问行为的时间间隔，如果这个时间间隔比如大于5分钟，可以认为这个用户已经退出了，小于这个数的就是这个用户一天的访问总时长。理论上大于5分钟的次数等于用户一天内的访问次数减一，但由于用户第一次的访问记录的差相当于减0也是大于5分钟的，所以大于5分钟的次数就等于用户一天内的访问次数。所以用计算出的总时长除以计算出的访问次数就是一个用户一天内的平均停留时长。

sql如下

select
day,type,
round(sum(disp)/count(case when disp>1 then 1 else 0 end),2) d
from
(select
day,type,cookie,
sum(case when diff<300 then diff else 0 end )/sum(case when diff>300 then 1 else 0 end ) disp
from
(select
nvl(t1.type,t2.type) type,
nvl(t1.day,t2.day) day,
nvl(t1.cookie,t2.cookie) cookie,
(nvl(t1.TIMESTAMP,0) - nvl(t2.TIMESTAMP,0))/1000 diff
from
(
select 
type,day,cookie,TIMESTAMP, 
dense_rank() over (partition by type,day,cookie order by TIMESTAMP) r 
from t_spe_log
)t1
full outer join 
(
select 
type,day,cookie,TIMESTAMP, 
dense_rank() over (partition by type,day,cookie order by TIMESTAMP) r 
from t_spe_log
) t2 on t1.type = t2.type and t1.day = t2.day and t1.cookie = t2.cookie and t1.r - 1 = t2.r
where (nvl(t1.TIMESTAMP,0) - nvl(t2.TIMESTAMP,0))/1000 > 0
order by day,type,cookie,diff)
group by day,type,cookie)
group by type,day
order by day,type