网站浏览总量 (一个月内每个时间点pv,uv的最大最小值)
(hive)
use analyst;
set mapred.reduce.tasks=20;
select
hours,max(pv) max_pv,min(pv) min_pv,max(uv) max_uv,min(uv) min_uv
from
(
select
days,from_unixtime(cast(executiontime/1000 as bigint),'HH') hours,count(1) pv,count(distinct cookie ) uv
from
tmp_log_no_cebuick_view
where
days >='20180622' and days<='20180628'
and xpath ='N/A' and disp ='NULL'
and url like '%/m.ebuick%'
group by days,from_unixtime(cast(executiontime/1000 as bigint),'HH')
) a
group by hours;
1.1buick_网站总pv.uv.平均停留时长(s).跳出率
(hive)
use analyst;
select
days,pv,uv,avg_time,(uv-nd)*100/uv dump_rate
from(
select a.days days ,count(1) pv,count(distinct cookie ) uv,max(avg_time) avg_time,max(no_disp) nd from tmp_log_ebuick_partition a
left join(
select
d.days,sum(cast (default.dec(disp,'NULL',0, default.dec(sign(cast(disp as int)-120000 ),1,0,CAST(disp as int)) ) as int ))/1000/count(distinct cookie) avg_time,max(no_disp) no_disp
from
tmp_log_ebuick_partition d
left join(
select
days,count(distinct cookie) no_disp from tmp_log_ebuick_partition
where
xpath<>'N/A'
group by days
)c on c.days=d.days
where d.days >='20180806' and disp is not null and disp>0 and disp<120000 group by d.days
)b
on a.days = b.days
where a.days >='20180806'
and xpath = 'N/A' and disp = 'NULL'
group by a.days
order by a.days
)a
order by days desc;