hive以半小时为维度进行统计的需求

有一张点击表和曝光表 ad_click_b，ad_exposure_b，注意：这俩张表都是以小时为单位的增量的分区表，都拥有字段：rtime，req_id，data_date。（rtime 是精确到秒的时间戳类型；req_id 是这俩张表的主键，可以用于ad_click_b表与ad_exposure_b表的关联操作；data_date 是分区字段，并且分区单位是小时，格式为yyMMddHH）。以点击表为主表，求点击表在 data_date=2020042406中，在曝光表中往前延3个小时（2020042404，2020042405，2020042406）, 统计曝光事件与点击事件之间的时间间隔分布情况，按30分钟为粒度。本需求的目的是求：在 data_date=2020042406这一小时的点击量中， 有多少的比例是广告曝光后在30分钟内点击的；有多少的比例是广告曝光后在60分钟内点击的；有多少的比例是广告曝光后在90分钟内点击的…

结果展示示例：
时间间隔(单位分钟) 占比情况（%）
分钟 百分比
30 80
60 95
90 96
120 98
150 99
180 99.6
210 100

30 80 这一行表示：在2020042406这一个小时的点击量中，80%以上是30分钟内（点击时间-曝光时间）的点击的。
60 95 这一行表示：在2020042406这一个小时的点击量中，95%以上是60分钟内（点击时间-曝光时间）的点击的。

思路：
1.用req_id 关联点击事件left join 曝光事件，且曝光时间和点击事件都存在的情况下，click[20200422],exposure[20200420,20200421,20200422]
2.获取点击时间和曝光时间
3.利用2步骤中的 点击时间-曝光时间差 得到 时间差
4.计算各个时间差的占比

function etldata() {
    echo "start etldata"

    beeline -e "
		select 
		time
		,count
		,sum
		,sum(rate) over(rows between UNBOUNDED PRECEDING and CURRENT ROW)
		from(
			select
				time
				,count
				,sum
				,(count*100.00)/sum as rate
				from
				(
						select 
						time
						,count
						,sum(count) over() as sum
						from(
							select 
							(time+1)*30 as time
							,count(*) as count
							from 
							(
								select
								  click.req_id
								  ,click.rtime as ctime
								  ,expo.rtime as etime
								  ,floor((click.rtime-expo.rtime)/1000/60/30) as time
								  from (
										select rtime,req_id
										from tmp.ad_click_b
											where data_date=2020042406
								  ) click
								  left join 
									  (
										select rtime,req_id
										from tmp.ad_exposure_b
											where data_date>=2020042404 and data_date<=2020042406	
									  ) expo on click.req_id = expo.req_id
							)cli_epo  group by time 
						)t2

				)t1 
		)t order by time asc
	 "

   echo "end etldata"
}

function main() {

    etldata
}

main

with v_cli_epo as (  
     select click.req_id, 
            click.rtime as ctime, 
            expo.rtime as etime,
            floor((click.rtime - expo.rtime) / 1000 / 60 / 30) as time     from(
            select rtime, 
                   req_id 
            from tmp.ad_click_b where data_date = 2020042406
     )click 
     left join(
            select rtime, 
                   req_id 
            from tmp.ad_exposure_b where data_date >= 2020042404 and data_date <= 2020042406
     )expo on click.req_id = expo.req_id
)
, v_cli_epo_sum as (  
    select time, 
           count, 
           sum(count) over() as sum 
    from(
        select (time + 1) * 30 as time, 
               count(*) as count 
        from v_cli_epo 
        group by time
    )v_cli_epo_rn
)
, v_cli_epo_rate as (  
    select time, 
           count, 
           sum, 
          (count * 100.00) / sum as rate
    from v_cli_epo_sum
)
select
     time, 
     count, 
     sum, 
     sum(rate) over(rows between UNBOUNDED PRECEDING and CURRENT ROW)
from v_cli_epo_rate order by time asc