hive sql解决关联条件中不等值连接问题及累计值的计算

工作中写的sql，本次的sql可以拓展出一些新内容，留下笔记

with a as (
select  
		interface
		,case when action is NUll then 'null' else action end as action
		, request_time*1000 as request_time
	    ,count(request_time) as pv	
from 
	xx_xx
where ds >= %YYYYMMDD% || '00'
 and ds <= %YYYYMMDD% || '23'
group by  
	interface
	,action
	,request_time * 1000
),

b as (
 select 
		 a1.interface 
		,a1.action
		,a1.request_time
		,a1.pv 
		,sum(case when  a1.request_time>= a2.request_time then a2.pv end) as sum_cnt    --解决hive sql关联条件中不等值连接问题
 from 
		  a a1
 join   a a2
on 
	a1.interface = a2.interface
 and
   a1.action = a2.action   
group by 
		 a1.interface 
		,a1.action
		,a1.request_time
		,a1.pv 
),

c as (
select 
	  b.interface
	  ,b.action
	  ,case when b.sum_cnt / t.total_pv >=0.90 then b.request_time end as geo_time_90
	  ,case when b.sum_cnt / t.total_pv >=0.95 then b.request_time end as geo_time_95
	  ,case when b.sum_cnt / t.total_pv >=0.99 then b.request_time end as geo_time_99
from 
		b
join  
		(
			select interface
					,action
					,sum(pv) as total_pv
			from 
                	a 
            group by 
                		  interface
                          ,action
		)t 
 on 
	b.interface = t.interface
and b.action = t.action
)
select 
	c.interface
	,c.action
	,min(geo_time_90) as geo_time_90
	,min(geo_time_95) as geo_time_95
	,min(geo_time_99) as geo_time_99
from 
	c
group by 
		c.interface
		,c.action