select * from ods_weblog_detail limit 2;+--------------------------+--------------------------------+--------------------------------+-------------------------------+---------------------------+----------------------------+--------------------------+------------------------+-------------------------+-----------------------------------------------+---------------------------+------------------------------------+---------------------------------+-----------------------------+-----------------------------+------------------------------+---------------------------------+------------------------------------+----------------------------+--+| ods_weblog_detail.valid | ods_weblog_detail.remote_addr | ods_weblog_detail.remote_user | ods_weblog_detail.time_local | ods_weblog_detail.daystr | ods_weblog_detail.timestr | ods_weblog_detail.month | ods_weblog_detail.day | ods_weblog_detail.hour | ods_weblog_detail.request | ods_weblog_detail.status | ods_weblog_detail.body_bytes_sent | ods_weblog_detail.http_referer | ods_weblog_detail.ref_host | ods_weblog_detail.ref_path | ods_weblog_detail.ref_query | ods_weblog_detail.ref_query_id | ods_weblog_detail.http_user_agent | ods_weblog_detail.datestr |+--------------------------+--------------------------------+--------------------------------+-------------------------------+---------------------------+----------------------------+--------------------------+------------------------+-------------------------+-----------------------------------------------+---------------------------+------------------------------------+---------------------------------+-----------------------------+-----------------------------+------------------------------+---------------------------------+------------------------------------+----------------------------+--+| false | 194.237.142.21 | - | 2013-09-18 06:49:18 | 2013-09-18 | 06:49:18 | 09 | 18 | 06 | /wp-content/uploads/2013/07/rstudio-git3.png | 304 | 0 | "-" | NULL | NULL | NULL | NULL | "Mozilla/4.0(compatible;)" | 20130918 || false | 163.177.71.12 | - | 2013-09-18 06:49:33 | 2013-09-18 | 06:49:33 | 09 | 18 | 06 | / | 200 | 20 | "-" | NULL | NULL | NULL | NULL | "DNSPod-Monitor/1.0" | 20130918 |+--------------------------+--------------------------------+--------------------------------+-------------------------------+---------------------------+----------------------------+--------------------------+------------------------+-------------------------+-----------------------------------------------+---------------------------+------------------------------------+---------------------------------+-----------------------------+-----------------------------+------------------------------+---------------------------------+------------------------------------+----------------------------+--+
1. 流量分析----------------------------------------------------------------------------------------------计算每小时pvs，注意gruop by语句的语法select count(*) as pvs,month,day,hour from ods_weblog_detail group by month,day,hour;--------------------------------------------------------------------------------------------1.1． 多维度统计PV总量--第一种方式：直接在ods_weblog_detail单表上进行查询1.1.1 计算该处理批次（一天）中的各小时pvsdrop table if exists dw_pvs_everyhour_oneday;create table if not exists dw_pvs_everyhour_oneday(month string,day string,hour string,pvs bigint) partitioned by(datestr string);
insert into table dw_pvs_everyhour_oneday partition(datestr='20130918')select a.month as month,a.day as day,a.hour as hour,count(*) as pvs from ods_weblog_detail awhere a.datestr='20130918' group by a.month,a.day,a.hour;
--计算每天的pvsdrop table if exists dw_pvs_everyday;create table if not exists dw_pvs_everyday(pvs bigint,month string,day string);
insert into table dw_pvs_everydayselect count(*) as pvs,a.month as month,a.day as day from ods_weblog_detail agroup by a.month,a.day;
+----------------------+------------------------+----------------------+--+| dw_pvs_everyday.pvs | dw_pvs_everyday.month | dw_pvs_everyday.day |+----------------------+------------------------+----------------------+--+| 10777 | 09 | 18 || 2993 | 09 | 19 |+----------------------+------------------------+----------------------+--+
1.1.2 第二种方式：与时间维表关联查询
--维度：日drop table dw_pvs_everyday;create table dw_pvs_everyday(pvs bigint,month string,day string);
insert into table dw_pvs_everydayselect count(*) as pvs,a.month as month,a.day as day from (select distinct month, day from t_dim_time) ajoin ods_weblog_detail b on a.month=b.month and a.day=b.daygroup by a.month,a.day;
--维度：月drop table dw_pvs_everymonth;create table dw_pvs_everymonth (pvs bigint,month string);
insert into table dw_pvs_everymonthselect count(*) as pvs,a.month from (select distinct month from t_dim_time) ajoin ods_weblog_detail b on a.month=b.month group by a.month;
--另外，也可以直接利用之前的计算结果。比如从之前算好的小时结果中统计每一天的Insert into table dw_pvs_everydaySelect sum(pvs) as pvs,month,day from dw_pvs_everyhour_oneday group by month,day having day='18';+--------+--------+------+--+| pvs | month | day |+--------+--------+------+--+| 10777 | 09 | 18 || 2993 | 09 | 19 |+--------+--------+------+--+
--------------------------------------------------------------------------------------------1.2 按照来访维度统计pv
--统计每小时各来访url产生的pv量，查询结果存入：( "dw_pvs_referer_everyhour" )
drop table if exists dw_pvs_referer_everyhour;create table if not exists dw_pvs_referer_everyhour(referer_url string,referer_host string,month string,day string,hour string,pv_referer_cnt bigint) partitioned by(datestr string);
insert into table dw_pvs_referer_everyhour partition(datestr='20130918')select http_referer,ref_host,month,day,hour,count(1) as pv_referer_cntfrom ods_weblog_detail group by http_referer,ref_host,month,day,hour having ref_host is not nullorder by hour asc,day asc,month asc,pv_referer_cnt desc;
--统计每小时各来访host的产生的pv数并排序
drop table dw_pvs_refererhost_everyhour;create table dw_pvs_refererhost_everyhour(ref_host string,month string,day string,hour string,ref_host_cnts bigint) partitioned by(datestr string);
insert into table dw_pvs_refererhost_everyhour partition(datestr='20130918')select ref_host,month,day,hour,count(1) as ref_host_cntsfrom ods_weblog_detail group by ref_host,month,day,hour having ref_host is not nullorder by hour asc,day asc,month asc,ref_host_cnts desc;
---------------------------------------------------------------------------1.3 统计pv总量最大的来源TOPN--需求：按照时间维度，统计一天内各小时产生最多pvs的来源topN分组求topN，先分组，再求每组内的topN
--row_number函数select ref_host,ref_host_cnts,concat(month,day,hour),row_number() over (partition by concat(month,day,hour) order by ref_host_cnts desc) as od from dw_pvs_refererhost_everyhour;
--综上可以得出drop table dw_pvs_refhost_topn_everyhour;create table dw_pvs_refhost_topn_everyhour(hour string,toporder string,ref_host string,ref_host_cnts string)partitioned by(datestr string);
insert into table dw_pvs_refhost_topn_everyhour partition(datestr='20130918')select t.hour,t.od,t.ref_host,t.ref_host_cnts from (select ref_host,ref_host_cnts,concat(month,day,hour) as hour,row_number() over (partition by concat(month,day,hour) order by ref_host_cnts desc) as od from dw_pvs_refererhost_everyhour) t where od<=3;
---------------------------------------------------------------------------------------------1.4 人均浏览页数--需求描述：统计今日所有来访者平均请求的页面数。--总页面请求数/去重总人数
select '20130918',count(1) / count(distinct remote_addr) from ods_weblog_detail where datestr='20130918'; --13.4 (20.744 seconds)select count(request) / count(distinct remote_addr) from ods_weblog_detail where datestr='20130918'; -- 13.4 (20.888 seconds)select count(request) / (select count(1) from (select remote_addr from ods_weblog_detail group by remote_addr) t) from ods_weblog_detail;报错：Error: Error while compiling statement: FAILED: ParseException line 1:25 cannot recognize input near 'select' 'count' '(' in expression specification (state=42000,code=40000)
select count(distinct remote_addr) from ods_weblog_detail; --1027(19.466 seconds)select count(1) from (select remote_addr from ods_weblog_detail group by remote_addr) t; --1027(36.809 seconds)
drop table dw_avgpv_user_everyday;create table dw_avgpv_user_everyday(day string,avgpv string);
insert into table dw_avgpv_user_everydayselect '20130918',count(1) / count(distinct remote_addr) from ods_weblog_detail where datestr='20130918'; --select '20130918',sum(b.pvs)/count(b.remote_addr) --这个方法实际上效率还要低一些from(select remote_addr,count(1) as pvs from ods_weblog_detail where datestr='20130918' group by remote_addr) b;
转载于:https://www.cnblogs.com/mediocreWorld/p/11107222.html
相关资源：JAVA上百实例源码以及开源项目