⽹站流量⽇志分析（模块开发----统计分析实战）
⼀、模块开发----统计分析
数据仓库建设好以后，⽤户就可以编写Hive SQL语句对其进⾏访问并对其中数据进⾏分析。
在实际⽣产中，究竟需要哪些统计指标通常由数据需求相关部门⼈员提出，⽽且会不断有新的统计需求产⽣，以下为⽹站流量分析中的⼀些典型指标⽰例。
1．流量分析
1.1．基础指标多维统计分析
基础指标统计
对于指标业务含义的解读是关键。
PageView 浏览次数（pv）
select count(*) from ods_weblog_detail where datestr ="20181101" and valid = "true"; 排除静态资源
Unique Visitor 独⽴访客（UV）:
select count(distinct remote_addr) as uvs from ods_weblog_detail where datestr ="20181101";
访问次数（VV）：
select count(distinct session) as vvs from ods_click_stream_visit where datestr ="20181101";
IP:
select count(distinct remote_addr) as ips from ods_weblog_detail where datestr ="20181101";
create table dw_webflow_basic_info(month string,day string,
pv bigint,uv bigint ,ip bigint, vv bigint) partitioned by(datestr string);
insert into table dw_webflow_basic_info partition(datestr="20181101")
select '201811','01',a.,b. from
(select count(*) as pv,count(distinct remote_addr) as uv,count(distinct remote_addr) as ips
from ods_weblog_detail
where datestr ='20181101') a join
(select count(distinct session) as vvs from ods_click_stream_visit where datestr ="20181101") b;
多维统计分析
按时间维度
⽅式⼀：直接在ods_weblog_detail单表上进⾏查询
--计算该处理批次（⼀天）中的各⼩时pvs
drop table dw_pvs_everyhour_oneday;
create table 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')
h as month,a.day as day,a.hour as hour,count(*) as pvs from ods_weblog_detail a
where a.datestr='20130918' group h,a.day,a.hour;
--计算每天的pvs
drop table dw_pvs_everyday;
create table dw_pvs_everyday(pvs bigint,month string,day string);
insert into table dw_pvs_everyday
select count(*) as h as month,a.day as day from ods_weblog_detail a
group h,a.day;
⽅式⼆：与时间维表关联查询
--维度：⽇
drop table dw_pvs_everyday;
create table dw_pvs_everyday(pvs bigint,month string,day string);
insert into table dw_pvs_everyday
select count(*) as h as month,a.day as day from (select distinct month, day from t_dim_time) a
join ods_weblog_detail b
h and a.day=b.day
group h,a.day;
--维度：⽉
drop table dw_pvs_everymonth;
create table dw_pvs_everymonth (pvs bigint,month string);
insert into table dw_pvs_everymonth
select count(*) as h from (select distinct month from t_dim_time) a
join ods_weblog_detail b h group h;
--另外，也可以直接利⽤之前的计算结果。⽐如从之前算好的⼩时结果中统计每⼀天的
Insert into table dw_pvs_everyday
Select sum(pvs) as pvs,month,day from dw_pvs_everyhour_oneday group by month,day having day='18';
按referer、时间维度
--统计每⼩时各来访url产⽣的pv量
drop table dw_pvs_referer_everyhour;
create table 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='20181101')
select http_referer,ref_host,month,day,hour,count(*) as pv_referer_cnt
from ods_weblog_detail
group by http_referer,ref_host,month,day,hour
having ref_host is not null
order 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='20181101')
select ref_host,month,day,hour,count(*) as ref_host_cnts
from ods_weblog_detail
group by ref_host,month,day,hour
having ref_host is not null
order by hour asc,day asc,month asc,ref_host_cnts desc;
按终端维度
数据中能够反映出⽤户终端信息的字段是http_user_agent。
User Agent也简称UA。它是⼀个特殊字符串头，是⼀种向访问⽹站提供所使⽤的浏览器类型及版本、操作系统及版本、浏览器内核、等信息的标识。例如：
User-Agent,Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.276 Safari/537.36
上述UA信息就可以提取出以下的信息：
chrome 58.0、浏览器 chrome、浏览器版本 58.0、系统平台 windows
浏览器内核 webkit
感兴趣的可以查看参考资料如何⾃定义UDF解析UA。
按栏⽬维度
⽹站栏⽬可以理解为⽹站中内容相关的主题集中。体现在域名上来看就是不同的栏⽬会有不同的⼆级
⽬录。⽐如某⽹站⽹址为，旗下栏⽬可以通过如下⽅式访问：
栏⽬维度：../job
栏⽬维度：../news
栏⽬维度：../sports
栏⽬维度：../technology
那么根据⽤户请求url就可以解析出访问栏⽬，然后按照栏⽬进⾏统计分析。
1.2．复合指标统计分析
⼈均浏览页数（平均访问深度）
今⽇所有来访者平均请求浏览的页⾯数。该指标可以说明⽹站对⽤户的粘性。
计算⽅式：总页⾯请求数pv/独⽴访客数uv
remote_addr表⽰不同的⽤户。可以先统计出不同remote_addr的pv量然后累加（sum）所有pv作为总的页⾯请求数，再count所有
remote_addr作为总的去重总⼈数。
drop table dw_avgpv_user_everyday;
create table dw_avgpv_user_everyday(
day string,
avgpv string);
insert into table dw_avgpv_user_everyday
select '20130918',sum(b.pvs)/_addr) from
(select remote_addr,count(1) as pvs from ods_weblog_detail where datestr='20130918' group by remote_addr) b;
平均访问频度
平均每个独⽴访客⼀天内访问⽹站的次数（产⽣的session个数）。
计算⽅式：访问次数vv/独⽴访客数uv
select '20181101',vv/uv from dw_webflow_basic_info; --注意vv的计算采⽤的是点击流模型表数据已经去除⽆效数据
select count(session)/ count(distinct remote_addr) from ods_click_stream_visit where datestr ="20181101"; --符合逻辑
平均访问时长
平均每次访问（会话）在⽹站上的停留时间。体现⽹站对访客的吸引程度。平均访问时长=访问总时长/访问次数。
跳出率:
跳出率是指⽤户到达你的⽹站上并在你的⽹站上仅浏览了⼀个页⾯就离开的访问次数与所有访问次数的百分⽐。是评价⽹站性能的重要指标。 
1.3．分组TOP问题（分组函数）
需求描述：统计每⼩时各来访host的产⽣的pvs数最多的前N个（topN）。
row_number()函数
语法：row_number() over (partition by xxx order by xxx) rank，rank为分组的别名，相当于新增⼀个字段为rank。
partition by⽤于分组，⽐⽅说依照sex字段分组
order by⽤于分组内排序，⽐⽅说依照sex分组后，组内按照age排序
排好序之后，为每个分组内每⼀条分组记录从1开始返回⼀个数字
取组内某个数据，可以使⽤where 表名.rank>x之类的语法去取
以下语句对每个⼩时内的来访host次数倒序排序标号:
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;
效果如下：
根据上述row_number的功能，可编写hql取各⼩时的ref_host访问次数topn
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='20181101')
select t.hour,t.f_f_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;
结果如下：
2．受访分析（从页⾯的⾓度分析）
2.1．各页⾯访问统计
主要是针对数据中的request进⾏统计分析，⽐如各页⾯PV ，各页⾯UV 等。
以上指标⽆⾮就是根据页⾯的字段group by。例如：
--统计各页⾯pv
select request as request,count(request) as request_counts from
ods_weblog_detail group by request having request is not null order by request_counts desc limit 20;
2.2．热门页⾯统计
--统计每⽇最热门的页⾯top10
drop table dw_hotpages_everyday;
create table dw_hotpages_everyday(day string,url string,pvs string);
insert into table dw_hotpages_everyday
select '20130918',a.quest_counts from
(select request as request,count(request) as request_counts from ods_weblog_detail where datestr='20181101’ group by request having request is not null) a
order quest_counts desc limit 10;
3．访客分析
3.1．独⽴访客
需求描述：按照时间维度⽐如⼩时来统计独⽴访客及其产⽣的pv。
对于独⽴访客的识别，如果在原始⽇志中有⽤户标识，则根据⽤户标识即很好实现；此处，由于原始⽇志中并没有⽤户标识，以访客IP来模拟，技术上是⼀样的，只是精确度相对较低。
--时间维度：时
drop table dw_user_dstc_ip_h;
create table dw_user_dstc_ip_h(
remote_addr string,
pvs bigint,
hour string);
insert into table dw_user_dstc_ip_h
select remote_addr,count(1) as pvs,concat(month,day,hour) as hour
from ods_weblog_detail
Where datestr='20181101'
group by concat(month,day,hour),remote_addr;
在此结果表之上，可以进⼀步统计，如每⼩时独⽴访客总数：
select count(1) as dstc_ip_cnts,hour from dw_user_dstc_ip_h group by hour;
--时间维度：⽇
select remote_addr,count(1) as counts,concat(month,day) as day
from ods_weblog_detail
Where datestr='20181101'
group by concat(month,day),remote_addr;
--时间维度：⽉
select remote_addr,count(1) as counts,month
from ods_weblog_detail
group by month,remote_addr;
3.2．每⽇新访客
需求：将每天的新访客统计出来。
实现思路：创建⼀个去重访客累积表，然后将每⽇访客对⽐累积表。
网站流量统计分析工具--历⽇去重访客累积表
drop table dw_user_dsct_history;
create table dw_user_dsct_history(
day string,
ip string
)
partitioned by(datestr string);
--每⽇新访客表
drop table dw_user_new_d;
create table dw_user_new_d (
day string,
ip string
)
partitioned by(datestr string);
--每⽇新⽤户插⼊新访客表
insert into table dw_user_new_d partition(datestr='20181101')
select tmp.day as day_addr as new_ip from
(
select today.day as _addr as today_addr,old.ip as old_addr
from
(select distinct remote_addr as remote_addr," 20181101" as day from ods_weblog_detail where datestr="20181101") today
left outer join
dw_user_dsct_history old
_addr=old.ip
) tmp
where tmp.old_addr is null;
--每⽇新⽤户追加到累计表
insert into table dw_user_dsct_history partition(datestr='20181101')
select day,ip from dw_user_new_d where datestr='20181101';
验证查看：
select count(distinct remote_addr) from ods_weblog_detail;
select count(1) from dw_user_dsct_history where datestr='20181101';
select count(1) from dw_user_new_d where datestr='20181101';
3.3．地域分析
IP⼀般包含的信息：国家、区域（省/州）、城市、街道、经纬度、ISP提供商等信息。因为IP数据库随着时间经常变化（不过⼀段时间内变化很⼩），所以需要有⼈经常维护和更新。这个数据也不可能完全准确、也不可能覆盖全。
⽬前，国内⽤的⽐较有名的是“纯真IP数据库”，国外常⽤的是 maxmind、ip2location。IP数据库是否收费：收费、免费都有。⼀般有⼈维护的数据往往都是收费的，准确率和覆盖率会稍微⾼⼀些。
查询形式：
本地，将IP数据库下载到本地使⽤，查询效率⾼、性能好。常⽤在统计分析⽅⾯。具体形式⼜分为：
内存查询：将全部数据直接加载到内存中，便于⾼性能查询。或者⼆进制的数据⽂件本⾝就是经过优化的索引⽂件，可以直接对⽂件做查询。
数据库查询：将数据导⼊到数据库，再⽤数据库查询。效率没有内存查询快。
远程（web service或ajax），调⽤远程第三⽅服务。查询效率⾃然⽐较低，⼀般⽤在⽹页应⽤中。查询的本质：输⼊⼀个IP，到其所在的IP段，⼀般都是采⽤⼆分搜索实现的。
⽰例：Hive UDF和GeoIP库为Hive加⼊IP识别功能
Hive所需添加的IP地址信息识别UDF函数如下：
4．访客Visit分析（点击流模型）
4.1．回头/单次访客统计
需求：查询今⽇所有回头访客及其访问次数。
实现思路：上表中出现次数>1的访客，即回头访客；反之，则为单次访客。
drop table dw_user_returning;
create table dw_user_returning(
day string,
remote_addr string,
acc_cnt string)
partitioned by (datestr string);
insert overwrite table dw_user_returning partition(datestr='20181101')
select tmp._addr,tmp.acc_cnt
from
(select '20181101' as day,remote_addr,count(session) as acc_cnt from ods_click_stream_visit group by remote_addr) tmp
where tmp.acc_cnt>1;
4.2．⼈均访问频次
需求：统计出每天所有⽤户访问⽹站的平均次数（visit）
总visit数/去重总⽤户数
select count(session)/count(distinct remote_addr) from ods_click_stream_visit where datestr='20181101';
5．关键路径转化率分析（漏⽃模型）
5.1．需求分析
转化：在⼀条指定的业务流程中，各个步骤的完成⼈数及相对上⼀个步骤的百分⽐。
5.2．模型设计
定义好业务流程中的页⾯标识，下例中的步骤为：
Step1、 /item
Step2、 /category
Step3、 /index
Step4、 /order
5.3．开发实现
查询每⼀个步骤的总访问⼈数
--查询每⼀步⼈数存⼊dw_oute_numbs
create table dw_oute_numbs as
select 'step1' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103' and request like '/item%' union
select 'step2' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103' and request like
'/category%'
union
select 'step3' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103' and request like '/order%' union
select 'step4' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103' and request like
'/index%';
注：UNION ALL将多个SELECT语句的结果集合并为⼀个独⽴的结果集。
查询每⼀步骤相对于路径起点⼈数的⽐例
思路：级联查询，利⽤⾃join
--dw_oute_numbs跟⾃⼰join
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join
dw_oute_numbs rr;
--每⼀步的⼈数/第⼀步的⼈数==每⼀步相对起点⼈数⽐例
umbs as ratio
from
(
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join
dw_oute_numbs rr) tmp
step='step1';
查询每⼀步骤相对于上⼀步骤的漏出率