数据分析---网站日志流分析

数据分析平台介绍

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-prVBOiWK-1603156618262)(B596936947C14377910EC56BFF7CC915)]

非结构化数据: 视频、音频、图片
半结构化数据:如网站流量日志

要做数据预处理 处理成结构化数据

网站流量分析项目的一些问题

  • 网站流量分析项目中,数据采集环节使用的技术是?

flume

  • 网站流量分析项目中,数据预处理环节使用的技术是?

MapReduce

  • 网站流量分析项目中,建设数仓使用的技术是?

hive

  • 网站流量分析项目中,数据导出环节使用的技术是?

sqoop

  • 建设数仓,首先要确定业务指标。网站流量分析项目中,说说你知道复合级指标.
+ 人均浏览的页数:人均浏览页数=浏览次数/独立访客,体现网站对访客的吸引程度
+ 跳出率:指某一范围内单页访问次数与总访问次数的百分比
+ 退出率:退出的访问者与综合访问量的百分比
  • 网站流量分析中,基础分析有哪些?
+ 趋势分析:通过流量趋势的变化形态,为分析网站访客访问规律、网站发展状况提供参考
+ 对比分析:根据选定的两个对比时段,提供流量在时间上的纵向对比报表,进而发现网站发展状况、发展规律、流量变化率等
+ 当前在线:提供当前时刻站点上的访客量,以及15分钟内的流量、来源、受访等变化情况,方便用户及时了解当前网站流量状况
+ 访问明细:提供最近7天的访客访问记录,可安每个PV或访问行为显示
  • 网站流量分析中,来源分析有哪些?
来源分类、搜索引擎、搜索词、最近7日的访客搜索记录、来路域名、来路页面、来源升降榜等
  • 网站流量分析中,受访分析有哪些?
受访域名、受访页面、受访升降榜、热点图、用户视点、访问轨迹等
  • 网站日志流量分析中,访客分析有哪些?
地区运营商、终端详情、新老访客、忠诚度、活跃度
  • 网站流量分析项目中,整个过程的流程调度使用的技术是?

azkaban

  • 什么是事实表?
可以理解为发生在现实生活中的操作型事件,比如一次购买行为就是一个事实,下订单就是一个事实。由若干维度和度量组成的

事实:可以理解为一个动词, 主题,比如下订单

事实表里面包含维度的主键 需要内容关联查即可

事实表中的每行数据代表一个业务事件(下单、支付、退款、评价等)。
“事实”这个术语表示的是业务事件的度量值(可统计次数、个数、金额等),例如,订单事件中的下单金额。
每个事实表的行包括:具有可加性的数值型的度量值、与维度表相连接的外键、通常具有两个以上的外键、外键之间表示维度表之间多对多的关系。

事实表的特点:
1、行数多
2、列少
  • 什么是维度表?
维度:一个名词,group by后面通常可跟维度

比如从地区角度看哪个地区的销售额最多,地区就是一个维度,维度信息存储的表就是维度表,存储的一般是对事实描述的信息

维度 数据量小 比较固定(如地域维度,再比如用户 相对于下订单的事实)

骨灰级指标、基础指标、复合级指标都是维度

维度表的特征:
1、范围很宽(列比较多);
2、跟事实表相比,行数比较少;
3、内容相对固定。

在这里插入图片描述

  • 维度建模的三种模式
+ 星型模式
    星型模式是以事实表为中心,所有的维度表直接连接在事实表上,像星星一样.
    维表只和事实表关联,维表之间没有关联.
    每个维表主键为单列,且该主键放置在事实表中,作为两边连接的外键.
+ 雪花模式
    在星型模式的基础上,维度表可以拥有其他维度表
    这种模型不太容易理解,维护成本比较高
    性能方面需要关联多层维表,性能也比星型模型要低。所以一般不是很常用
+ 星座模式
    星型模式是基于一张事实表的,而星座模式是基于多张事实表的,而且共享维度信息。
    
  • 维度建模有什么好处?为什么有时候不严格遵守维度建模的原则组织事实表和维度表?
+ 面向业务主题,使得数据模型非常好理解
+ 数据冗余小(因为很多具体的信息都存在相应的维度表中了)
+ 结构清晰(表结构一目了然)

增加使用成本,比如查询时要关联多张表

对于一些高频的字段,频繁的做查询会非常拖累性能,所以我们经常把这些高频字段放到事实表

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

1.数据处理流程

1.数据采集

数据采集概念,目前行业会有两种解释:一是数据从无到有的过程(web服务器打印的日志、自定义采集的日志等)叫做数据采集;另一方面也有把通过使用Flume等工具把数据采集到指定位置的这个过程叫做数据采集。
关于具体含义要结合语境具体分析,明白语境中具体含义即可。

2.数据预处理

通过mapreduce程序对采集到的原始日志数据进行预处理,比如清洗、滤除脏数据,结构化,格式整理(标准化),等,并且梳理成点击流模型数据。

3.数据入库

将预处理之后的数据导入到HIVE仓库中相应的库和表中。

4.数据分析(ETL)

项目的核心内容,即根据需求开发ETL分析语句,得出各种统计结果。

5.数据展现

将分析所得数据进行数据可视化,一般通过图表进行展示。

市面上有许多开源的数据可视化软件、工具。比如Echarts.

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2.mr程序进行数据预处理理论方面

我们采集的日志是半结构化数据,所以需要做预处理,处理成结构化数据

在这里插入图片描述

将采集的日志处理成如下图表(原始访问日志表)

在这里插入图片描述

思路:通过io把日志流拿到,然后读每一行,以空格分隔开,再进行清洗

3.流量分析常见分类

骨灰级指标

IP

1天之内,访问网站的不重复IP数。一天内相同IP地址多次访问网站只被计算1次。曾经IP指标可以用来表示用户访问身份,目前则更多的用来获取访问者的地理位置信息。

PV

PageView浏览量: 即通常说的PV值,用户每打开1个网站页面,记录1个PV。用户多次打开同一页面PV累计多次。通俗解释就是页面被加载的总次数

UV

Unique PageView: 1天之内,访问网站的不重复用户数(以浏览器cookie为依据),一天内同一访客多次访问网站只被计算1次, 不同用户的点击次数访问次数

基础级指标

  • 访问次数:访客从进入网站到离开网站的一系列活动记为一次访问,也称会话(session),1次访问(会话)可能包含多个PV。
  • 网站停留时间:访问者在网站上花费的时间。
  • 页面停留时间:访问者在某个特定页面或某组网页上所花费的时间。

复合级指标

  • 人均浏览页数:平均每个独立访客产生的PV。人均浏览页数=浏览次数/独立访客。体现网站对访客的吸引程度。
  • 跳出率:指某一范围内单页访问次数或访问者与总访问次数的百分比。其中跳出指单页访问或访问者的次数,即在一次访问中访问者进入网站后只访问了一个页面就离开的数量。
  • 退出率:指某一范围内退出的访问者与综合访问量的百分比。其中退出指访问者离开网站的次数,通常是基于某个范围的。

基础分析(PV,IP,UV)

趋势分析:根据选定的时段,提供网站流量数据,通过流量趋势变化形态,为您分析网站访客的访问规律、网站发展状况提供参考。

对比分析:根据选定的两个对比时段,提供网站流量在时间上的纵向对比报表,帮您发现网站发展状况、发展规律、流量变化率等。

当前在线:提供当前时刻站点上的访客量,以及最近15分钟流量、来源、受访、访客变化情况等,方便用户及时了解当前网站流量状况。

访问明细:提供最近7日的访客访问记录,可按每个PV或每次访问行为(访客的每次会话)显示,并可按照来源、搜索词等条件进行筛选。 通过访问明细,用户可以详细了解网站流量的累计过程,从而为用户快速找出流量变动原因提供最原始、最准确的依据。

4.统计分析

建表

事实表设计

在这里插入图片描述
在这里插入图片描述

维度表设计

在这里插入图片描述

-- 创建原始日志数据表
create table ods_weblog_origin(
valid string,
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_sent string,
http_referer string,
http_user_agent string)
partitioned by (datestr string)
row format delimited
fields terminated by '\001';

-- 创建点击流模型pageviews表
create table ods_click_pageviews(
session string,
remote_addr string,
remote_user string,
time_local string,
request string,
visit_step string,
page_staylong string,
http_referer string,
http_user_agent string,
body_bytes_sent string,
status string)
partitioned by (datestr string)
row format delimited
fields terminated by '\001';


-- 创建点击流visit模型表
create table ods_click_stream_visit(
session     string,
remote_addr string,
inTime      string,
outTime     string,
inPage      string,
outPage     string,
referal     string,
pageVisits  int)
partitioned by (datestr string)
row format delimited
fields terminated by '\001';

load data local inpath '/export/servers/webLog/ods_weblog_origin' overwrite into table
ods_weblog_origin partition(datestr='2020-10-17');

load data local inpath '/export/servers/webLog/ods_click_pageviews' overwrite into table
ods_click_pageviews partition(datestr='2020-10-17');

load data local inpath '/export/servers/webLog/ods_click_stream_visit' overwrite into table
ods_click_stream_visit partition(datestr='2020-10-17');

访问日志明细宽表

建明细表ods_weblog_detail

create table ods_weblog_detail(
valid           string, --有效标识
remote_addr     string, --来源IP
remote_user     string, --用户标识
time_local      string, --访问完整时间
daystr          string, --访问日期
timestr         string, --访问时间
month           string, --访问月
day             string, --访问日
hour            string, --访问时
request         string, --请求的url
status          string, --响应码
body_bytes_sent string, --传输字节数
http_referer    string, --来源url
ref_host        string, --来源的host
ref_path        string, --来源的路径
ref_query       string, --来源参数query
ref_query_id    string, --来源参数query的值
http_user_agent string --客户终端标识
)
partitioned by(datestr string);



通过查询插入数据到明细宽表  ods_weblog_detail中


分步:
--抽取refer_url到中间表  t_ods_tmp_referurl 
--也就是将来访url分离出host  path  query  query id

drop table if exists t_ods_tmp_referurl;

create table t_ods_tmp_referurl as
SELECT a.*,b.*
FROM ods_weblog_origin a 
LATERAL VIEW parse_url_tuple(regexp_replace (http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') 
b as host, path, query, query_id; 

--抽取转换time_local字段到中间表明细表 t_ods_tmp_detail

create table tt11tt as
SELECT a.*,b.*
FROM ods_weblog_origin a 
LATERAL VIEW parse_url_tuple(regexp_replace (http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') 
b as host, path, query, query_id; 


drop table if exists t_ods_tmp_detail;
create table t_ods_tmp_detail as 
select b.*,substring(time_local,0,10) as daystr,
substring(time_local,12) as tmstr,
substring(time_local,6,2) as month,
substring(time_local,9,2) as day,
substring(time_local,12,2) as hour
From t_ods_tmp_referurl b;

以上语句可以改写成:
insert into table ods_weblog_detail partition(datestr='2020-10-17')
select c.valid,c.remote_addr,c.remote_user,c.time_local,
substring(c.time_local,0,10) as daystr,
substring(c.time_local,12) as tmstr,
substring(c.time_local,6,2) as month,
substring(c.time_local,9,2) as day,
substring(c.time_local,11,3) as hour,
c.request,c.status,c.body_bytes_sent,c.http_referer,c.ref_host,c.ref_path,c.ref_query,c.ref_query_id,c.http_user_agent
from
(SELECT 
a.valid,a.remote_addr,a.remote_user,a.time_local,
a.request,a.status,a.body_bytes_sent,a.http_referer,a.http_user_agent,b.ref_host,b.ref_path,b.ref_query,b.ref_query_id 
FROM ods_weblog_origin a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as
 ref_host, ref_path, ref_query, ref_query_id) c;


show partitions ods_weblog_detail;

以下ETL需求小结

# 1.流量分析
	## 1.1多维度统计PV总量
		按时间维度:group by 年月日时,count统计
		按终端维度:like筛选终端即可
		
	## 1.2按refer维度,按照来访维度统计
		1.每小时各来访url的PV量:group by 月日时 url,count即可,一般host不为空
		2.每小时各来访host的PV量:group by 时间、host,也是host不为空
		
	## 1.3统计PV总量最大的来源topN
		开窗函数
		根据上述统计的各url来访PV量,partition by 时间,对PV量排序 取 topN即可
	## 1.4人均浏览页数
		人均浏览页数 = 总页面请求数 / 去重后的总人数
	
	
# 2. 受访分析
	## 2.1 各页面访问统计(各页面的PV)
	group by url串 count统计 
	
	## 2.2 热门页面统计
		统计某个分区里受访页面的top10: where 分区,group by url串,排序分页取10
		统计每日最热门页面的top10: group 到日、url串 对count() 排序分页取10 

		
# 3.访客分析
	## 按时间维度统计独立访客及其产生的PV量: group by 时间,ip
	## 统计每天新访客: 新旧两表左连接,旧表ip是空的,即为新访客
	
	
# 4.访客visit分析(点击流模型)
	## 回头/单次访客统计: group ip 次数大于1
	## 查询今日所有回头访客及其访问次数:
	

# 5.关键路径转化率分析(漏斗模型)
	## 查询每一个步骤的总访问人数
		union all 联合查询 分别查每一步的总人数,group步骤
	## 查询每一步骤相对于路径起点人数的比例
		表自连接,条件第一步
		每一步相对起点人数比例 = 每一步的人数/第一步的人数
	## 查询每一步骤相对于上一步骤的漏出率

1.流量分析

1.1多维度统计PV总量

按时间维度
 -- --计算每小时pvs(页面被加载的总次数),注意gruop by语法
select count(*) as pvs, month, day, hour from ods_weblog_detail group by month, day, hour;
 

 -- 每小时的pvs
 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='20201015')
select a.month as month, a.day as day, a.hour as hour, count(*) as pvs from ods_weblog_detail a
where a.datestr='20201015' group by a.month,a.day,a.hour;
 
 -- 每天的pvs
 create table if not exists dw_pvs_everyday(pvs bigint,month string,day string);
 insert into table dw_pvs_everyday
select count(*) as pvs, a.month as month, a.day as day from ods_weblog_detail a
group by a.month, a.day;
按终端维度
 select distinct(http_user_agent) from ods_weblog_detail where http_user_agent like '%Chrome%' limit 200;

1.2按referer维度(按照来访维度统计pv)

 -- 统计每小时各来访url产生的pv量
 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='20201015') select http_referer, ref_host, month, day, hour, count(1) 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;
	
drop table dw_pvs_referer_everyhour;

-- --统计每小时各来访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='20201015')
select ref_host,month,day,hour,count(1) 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;

1.3统计pv总量最大的来源TOPN (分组TOP)

--需求:按照时间维度,统计一天内各小时产生最多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='20201015')
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人均浏览页数

--需求描述:统计今日所有来访者平均请求的页面数。
--总页面请求数/去重总人数
drop table dw_avgpv_user_everyday;

create table dw_avgpv_user_everyday(day string, avgpv string);

insert into table dw_avgpv_user_everyday
select '20201015',sum(b.pvs)/count(b.remote_addr) 
from
(
select remote_addr, count(1) as pvs from ods_weblog_detail where datestr='20201015' 
group by remote_addr
) b;

2.受访分析

2.1 各页面访问统计(各页面PV)

select request as request,count(1) as request_counts from
ods_weblog_detail group by request having request is not null order by request_counts desc limit 20;

2.2热门页面统计

-- 统计20201015这个分区里面的受访页面的top10
drop table dw_hotpages_everyday;

create table dw_hotpages_everyday(day string,url string,pvs string);

insert into table dw_hotpages_everyday
select '20201015',a.request,a.request_counts from
(
select request as request,count(request) as request_counts 
from ods_weblog_detail 
where datestr='20201015' 
group by request 
having request is not null
) a
order by a.request_counts desc limit 10;


-- 统计每日最热门页面的top10
select a.month,a.day,a.request ,concat(a.month,a.day),a.total_request
from (
select month,day, request,count(1) as total_request
from ods_weblog_detail
where datestr = '20130918'
group by  request ,month ,day
having request is not null
order by total_request desc limit 10
) a;

3.访客分析

--需求:按照时间维度来统计独立访客及其产生的pv量

按照时间维度比如小时来统计独立访客及其产生的 pv 。

时间维度:时
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='20201015'
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='20201015'
group by concat(month,day),remote_addr;


时间维度: 月
select remote_addr,count(1) as counts,month 
from ods_weblog_detail
group by month,remote_addr;


-- 每日新访客
-- 需求:将每天的新访客统计出来

--历日去重访客累积表
drop table dw_user_dsct_history;

create table dw_user_dsct_history(
day string, ip string) partitioned by(datestr string);


select a.remote_addr ,a.day
from (
select  remote_addr,'20201015' as day 
from ods_weblog_detail newIp
where datestr ='20201015'
group by remote_addr
) a 
left join dw_user_dsct_history hist
on a.remote_addr = hist.ip
where hist.ip is null;


--每日新访客表
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='20201015')
select tmp.day as day,tmp.today_addr as new_ip 
from
(
select today.day as day,today.remote_addr as today_addr,old.ip as old_addr 
from 
(
select distinct remote_addr as remote_addr,"20201015" as day 
from ods_weblog_detail where datestr="20201015"
) today
left join 
dw_user_dsct_history old
on today.remote_addr=old.ip
) tmp
where tmp.old_addr is null;



--每日新用户追加到历史累计表
insert into table dw_user_dsct_history partition(datestr='20201015')
select day,ip from dw_user_new_d where datestr='20201015';


验证:
select count(distinct remote_addr) from ods_weblog_detail;

select count(1) from dw_user_dsct_history where datestr='20201015';

select count(1) from dw_user_new_d where datestr='20201015';

4.访客Visit分析(点击流模型)

--  回头/单次访客统计
select remote_addr ,count(remote_addr) ipcount
from  ods_click_stream_visit
group by remote_addr
having ipcount > 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='20201015')
select tmp.day,tmp.remote_addr,tmp.acc_cnt
from
(select '20201015' as day,remote_addr,count(session) as acc_cnt from ods_click_stream_visit 
group by remote_addr) tmp
where tmp.acc_cnt>1;


-- 人均访问频次,使用所有的独立访问的人,即独立的session个数除以所有的去重IP即可
-- 人均访问的频次,频次表示我们来了多少个session
--  次数都是使用session来进行区分,一个session就是表示一次
select count(session)/count(distinct remote_addr) from ods_click_stream_visit where datestr='20201015';


select count(1) 
from ods_click_stream_visit
where datestr ='20201015'


-- 人均页面浏览量,所有的页面点击次数累加除以所有的独立去重IP总和即可
select sum(pagevisits)/count(distinct remote_addr) from ods_click_stream_visit where datestr='20201015';

5.关键路径转化率分析(漏斗模型)

求两个指标:
	第一个指标:每一步相对于第一步的转化率
	第二个指标:每一步相对于上一步的转化率


# 使用模型生成的数据,可以满足我们的转化率的求取
load data inpath '/weblog/clickstream/pageviews/click-part-r-00000' overwrite into table ods_click_pageviews partition(datestr='20130920');

----------------------------------------------------------
---1、查询每一个步骤的总访问人数

Step1、  /item                     1000          相对上一步        相对第一步   1000
Step2、  /category                 800             0.8              0.8         1800
Step3、  /index                    500             0.625            0.5          2300
Step4、  /order                    100             0.2              0.1          2400




create table dw_oute_numbs as 
select 'step1' as step,count(distinct remote_addr)  as numbs from ods_click_pageviews 
where datestr='20130920' 
and request like '/item%'
union all
select 'step2' as step,count(distinct remote_addr)  as numbs from ods_click_pageviews 
where datestr='20130920' 
and request like '/category%'
union all
select 'step3' as step,count(distinct remote_addr)  as numbs from ods_click_pageviews where datestr='20130920' 
and request like '/order%'
union all
select 'step4' as step,count(distinct remote_addr)  as numbs from ods_click_pageviews where datestr='20130920' 
and request like '/index%';


+---------------------+----------------------+--+
| dw_oute_numbs.step  | dw_oute_numbs.numbs  |
+---------------------+----------------------+--+
| step1               | 1029                 |
| step2               | 1029                 |
| step3               | 1028                 |
| step4               | 1018                 |
+---------------------+----------------------+--+



----------------------------------------------------------------------------
--2、查询每一步骤相对于路径起点人数的比例
--级联查询,自己跟自己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;

自join后结果如下图所示:
每一步相对于第一步的转化率


+---------+----------+---------+----------+--+
| rnstep  | rnnumbs  | rrstep  | rrnumbs  |
+---------+----------+---------+----------+--+
| step1   | 1029     | step1   | 1029     |
| step2   | 1029     | step1   | 1029     |
| step3   | 1028     | step1   | 1029     |
| step4   | 1018     | step1   | 1029     |
| step1   | 1029     | step2   | 1029     |
| step2   | 1029     | step2   | 1029     |
| step3   | 1028     | step2   | 1029     |
| step4   | 1018     | step2   | 1029     |
| step1   | 1029     | step3   | 1028     |
| step2   | 1029     | step3   | 1028     |
| step3   | 1028     | step3   | 1028     |
| step4   | 1018     | step3   | 1028     |
| step1   | 1029     | step4   | 1018     |
| step2   | 1029     | step4   | 1018     |
| step3   | 1028     | step4   | 1018     |
| step4   | 1018     | step4   | 1018     |
+---------+----------+---------+----------+--+

过滤只取step1的所有的数据
select tempTab.rnnumbs/tempTab.rrnumbs 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 where rr.step = 'step1'
) tempTab;




--每一步的人数/第一步的人数==每一步相对起点人数比例
select tmp.rnstep,tmp.rnnumbs/tmp.rrnumbs 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
where tmp.rrstep='step1';

--------------------------------------------------------------------------------
--3、查询每一步骤相对于上一步骤的漏出率
--首先通过自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
where cast(substr(rn.step,5,1) as int)=cast(substr(rr.step,5,1) as int)-1;



select newTable.rnnumbs/newTable.rrnumbs from (
select * 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 
) tmpTable
where  cast(substr(tmpTable.rrStep,5,1) as int ) =  cast(substr(tmpTable.rnstep,5,1) as int )-1
) newTable 



where temTable.rrstep.截串  >= temTable.rnstep.截串

注意:cast为hive的内置函数,主要用于类型的转换
用例:
select  cast(1 as  float);
select  cast('2018-06-22' as date);


+---------+----------+---------+----------+--+
| rnstep  | rnnumbs  | rrstep  | rrnumbs  |
+---------+----------+---------+----------+--+
| step1   | 1029     | step2   | 1029     |
| step2   | 1029     | step3   | 1028     |
| step3   | 1028     | step4   | 1018     |
+---------+----------+---------+----------+--+

--然后就可以非常简单的计算出每一步相对上一步的漏出率
select tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as leakage_rate
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
where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1;

-----------------------------------------------------------------------------------
--4、汇总以上两种指标
select abs.step,abs.numbs,abs.rate as abs_ratio,rel.rate as leakage_rate
from 
(
select tmp.rnstep as step,tmp.rnnumbs as numbs,tmp.rnnumbs/tmp.rrnumbs as rate
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
where tmp.rrstep='step1'
) abs
left outer join
(
select tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as rate
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
where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1
) rel
on abs.step=rel.step;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

WGS.

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值