关键路径转换分析---漏斗模型

目录

• 需求
• 方案
• 总结

1.需求

         在一条指定的业务流程中，各个步骤的完成人数及相对上一个步骤的百分比。

2.方案： 使用漏斗模型

•          查询每一个步骤的总访问人数

create table dw_route_numbs as  select 'step1' as step,count(distinct remote_addr)  as numbs from tmp_page_views where request like '/item%' union select 'step2' as step,count(distinct remote_addr) as numbs from tmp_page_views where request like '/category%' union select 'step3' as step,count(distinct remote_addr) as numbs from tmp_page_views where request like '/order%' union select 'step4' as step,count(distinct remote_addr)  as numbs from tmp_page_views where request like '/index%';

      结果如下：

      

•          查询每一步骤相对于路径起点人数的比例

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_route_numbs rn inner join dw_route_numbs rr) tmp where tmp.rrstep='step1';

      结果如下：

      

 

•          查询每一步骤相对于上一步骤的漏出率

select tmp.rrstep as rrstep,tmp.rrnumbs/tmp.rnnumbs as ration from ( select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs  from dw_route_numbs rn inner join dw_route_numbs rr) tmp where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1;

      结果如下：

      

•          汇总以上两种指标

select abs.step,abs.numbs,abs.ratio as abs_ratio,rel.ratio as rel_ratio from ( select tmp.rnstep as step,tmp.rnnumbs as numbs,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_route_numbs rn inner join dw_route_numbs rr) tmp where tmp.rrstep='step1' ) abs left outer join ( select tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as ratio from ( select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs  from dw_route_numbs rn inner join dw_route_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;

      结果如下：

      

3. 总结

•     使用 自join 方法
•     cast方法 字符串转为整型