需求四：漏斗转化分析

实现思路

其实每一层用户的行为都可以找到对应的表。

• 活跃：对应于表dws_app_open_history。

• 商品详情页：对应于表dwd_goods_item。

• 下单：对应于表dwd_order。

• 支付：对应于表dwd_order。

可以对每个表计算每一层的转化率，然后将结果保存到APP层的app_conver_rate表中。

APP层开发

初始化APP层的app_conver_rate表。

> cd /home/work/warehouse_goods_order
> vi app_shopmall_requirement04_init.sh

#!/bin/bash
# 需求四：漏斗分析
# app层数据库和表初始化脚本，只需要执行一次即可

hive -e "
create database if not exists app_shopmall;

create external table if not exists app_shopmall.app_conver_rate (
    active_num            int,
    item_num              int,
    order_num             int,
    pay_num               int,
    active_to_item_ratio  decimal(10, 2),
    item_to_order_ratio   decimal(10, 2),
    order_to_pay_ratio    decimal(10, 2)
) partitioned by(dt string) 
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/app/conver_rate/';
"

再按照天来分区，并在每天的凌晨时执行一次。

> cd /home/work/warehouse_goods_order
> vi app_shopmall_requirement04_add_partition.sh

#!/bin/bash
# 需求四：漏斗分析
# 每天凌晨执行一次
if [ "d$1" = "d" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

hive -e "
insert overwrite table app_shopmall.app_conver_rate partition(dt = '${dt}') select 
daoh.active_num,
dgi.item_num,
do.order_num,
do.pay_num,
dgi.item_num / daoh.active_num as active_to_item_ratio,
do.order_num / dgi.item_num as item_to_order_ratio,
do.pay_num / do.order_num as order_to_pay_ratio
from
(
    select 
	count(*) as active_num
	from dws_shopmall.dws_app_open_history
	where dt = '${dt}'
) as daoh
join
(
    select 
	count(distinct goodsid) as item_num
	from dwd_shopmall.dwd_goods_item
    where dt = '${dt}'
) as dgi
on 1 = 1
join
(
    select
	count(*) as order_num,
	sum(case when status != 0 then 1 else 0 end) as pay_num
	from dwd_shopmall.dwd_order
	where dt = '${dt}'
) as do
on 1 = 1;
"

可以通过定时任务来执行它。

> crontab -e

# 每天凌晨0点1分执行
1 0 * * * /home/work/warehouse_goods_order/app_shopmall_requirement04_add_partition.sh

---

感谢支持

更多内容，请移步《超级个体》。