hive中路径转换(漏斗模型)

1 加载数据

load data local inpath '/root/hivedata/click-part-r-00000' overwrite into table ods_click_pageviews partition(datestr='20181103');

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

UNION All将多个SELECT语句的结果集合并为一个独立的结果集

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 all select 'step2' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103' and request like '/category%' union all select 'step3' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103' and request like '/order%' union all select 'step4' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103' and request like '/index%';

3 结果

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

4 查询每一步骤相对于路径起点人数的比例级联查询，自己跟自己join

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) tmp where rrstep ="step1"; select (rnnumbs/rrnumbs)*100 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 rrstep ="step1";

5 自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 | +---------+----------+---------+----------+--+

6 每一步的人数/第一步的人数==每一步相对起点人数比例

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';--tmp

7 结果

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

8 查询每一步骤相对于上一步骤的漏出率首先通过自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;

9 注意：cast为Hive内置函数 类型转换

select cast(1 as float); --1.0 select cast(‘2016-05-22’ as date); --2016-05-22

10 结果

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

11 然后就可以非常简单的计算出每一步相对上一步的漏出率

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;

12 汇总以上两种指标

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;