hive 利用差值有序实现第二次局部分组

案例一：

select
uid,count((p_day-order) as data )  p
from(select 
uid, p_day,row_number() over(partition by uid order by p_day asc ) as order 
from(select uid,datediff(p_day,'2000-01-01 00:00:00') as p_day from table 
where p_day>=start and p_day<=end
)t1
group by uid,p_day)t2
group by uid
having p<=16

案例二：用户在每个页面上访问的次数和当前页面访问的首次时间

 

select * from
(select f2.mtype,f2.uid,f2.jiange,f2.entry_page,(row_number() over (partition by f2.uid order by f2.num_4 asc)) as num_5 from
(select min(f1.num_2) as num_4,f1.mtype,f1.uid,f1.entry_page,(f1.num_3-f1.num_2) as jiange from
(select *,(row_number() over (partition by f0.uid,f0.entry_page  order by f0.num_2 asc)) as num_3 from table）t1)t2)t3

还有窗口函数lead和lag也可以实现类似解法（定值）

 

 

题目：

 

select 
uid,entry_page,rn_3,count(diff) as cnt 
from(
select 
uid,entry_page,diff,row_number() over(partition by uid,entry_page,diff order by rn_1 asc) as rn_3
from(
select 
uid,entry_page,(rn_1-rn_2) as diff,rn_1,rn_2
from(
select 
uid,entry_page,row_number() over(partition by uid,entry_page order by rn_1 asc) as rn_2,rn_1
from(
 select uid,entry_page,row_number() over(partition by uid order by time asc) as rn_1 from table
)t1
)t2)t3)t4
group by uid,entry_page,rn_3
order by rn_3 asc limit 10
-- 其中rn_1 代表用户访问页面的所有顺序
-- 其中rn_2 代表用户访问各个页面上的顺序
-- 其中rn_3 代表用户访问页面的顺序

-- 结果是 用户依次访问的页面及在本次该页面上的操作数