set @cixu:=0;

set @lastpage:="";

select

pagename,

avg(rank) as avg_rn

from

(

select

uid,

pagename,

case when @lastpage != pagrname and @id =uid then @cixu := @cixu 1 

when  @lastpage = pagename and @id =uid then @cixu:=@cixu

else @cixu:=0 end as rank,

@lastpage := pagename

(select distinct uid,pagename

from ts

order by uid,date )a)b

group by pagename;