漏斗分析是一个数据分析模型，它能够科学反映一个业务过程从起点到终点各阶段用户转化情况。由于其能将各阶段环节都展示出来，故哪个阶段存在问题，就能一目了然。

漏斗模型

该需求要求统计一个完整的购物流程各个阶段的人数，具体说明如下：

建表sql

CREATE EXTERNAL TABLE ads_user_action
(
    `dt`                STRING COMMENT '统计日期',
    `recent_days`       BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `home_count`        BIGINT COMMENT '浏览首页人数',
    `good_detail_count` BIGINT COMMENT '浏览商品详情页人数',
    `cart_count`        BIGINT COMMENT '加入购物车人数',
    `order_count`       BIGINT COMMENT '下单人数',
    `payment_count`     BIGINT COMMENT '支付人数'
) COMMENT '漏斗分析'

前提

今天为2020-06-14

完整sql

with
    page_view_info as (

        select
               1 recent_days,
               sum(`if`(page_id ='good_detail',1,0)) good_detail_count,
               sum(`if`(page_id ='home',1,0)) home_count
        -- 1天中一个设备浏览的一种页面是一行
        from dws_traffic_page_visitor_page_view_1d
        where dt='2020-06-14'
        -- 只查询浏览首页和详情页的页面浏览情况
        and (page_id = 'good_detail' or page_id = 'home')
        union all
                select
                         recent_days,
                        sum(if(if( recent_days = 7, view_count_7d,view_count_30d  ) > 0 and page_id ='good_detail',1,0)) good_detail_count,
                         sum(if(if( recent_days = 7, view_count_7d,view_count_30d  ) > 0 and page_id ='home',1,0)) home_count

                from dws_traffic_page_visitor_page_view_nd
                lateral view explode(`array`(7, 30)) tmp as recent_days
                where dt='2020-06-14'
                 and (page_id = 'good_detail' or page_id = 'home')
                group by recent_days

    ),
    cart_add_info as (

        select
               1 recent_days,
                count(*) cart_count
        -- 1天中一个user是一行
        from dws_trade_user_cart_add_1d
        where dt='2020-06-14'
         union all
                select
                         recent_days,
                         sum(if(if( recent_days = 7, cart_add_count_7d,cart_add_count_30d  ) > 0,1,0)) cart_count
                -- 统计的是用户截止到6-14日，最近7日的指标和最近30日的指标
                -- 存在一个用户可能 最近30天支付 payment_count_30d > 0 ,但是可能最近7天没支付， payment_count_7d =0
                from dws_trade_user_cart_add_nd
                lateral view explode(`array`(7, 30)) tmp as recent_days
                where dt='2020-06-14'
                group by recent_days

    ),
     order_info as (

        select
               1 recent_days,
                count(*) order_count
        -- 1天中一个user是一行
        from dws_trade_user_order_1d
        where dt='2020-06-14'
          union all
                select
                         recent_days,
                         sum(if(if( recent_days = 7, order_count_7d,order_count_30d  ) > 0,1,0)) order_count
                -- 统计的是用户截止到6-14日，最近7日的指标和最近30日的指标
                -- 存在一个用户可能 最近30天支付 payment_count_30d > 0 ,但是可能最近7天没支付， payment_count_7d =0
                from dws_trade_user_order_nd
                lateral view explode(`array`(7, 30)) tmp as recent_days
                where dt='2020-06-14'
                group by recent_days

    ),payment_info as (

                select
                       1 recent_days,
                        count(*) payment_count
                -- 1天中一个user是一行
                from dws_trade_user_payment_1d
                where dt='2020-06-14'
                union all
                select
                         recent_days,
                         sum(if(if( recent_days = 7, payment_count_7d,payment_count_30d  ) > 0,1,0)) payment_count
                -- 统计的是用户截止到6-14日，最近7日的指标和最近30日的指标
                -- 存在一个用户可能 最近30天支付 payment_count_30d > 0 ,但是可能最近7天没支付， payment_count_7d =0
                from dws_trade_user_payment_nd
                lateral view explode(`array`(7, 30)) tmp as recent_days
                where dt='2020-06-14'
                group by recent_days
    )
insert overwrite table ads_user_action
select * from ads_user_action
union 
select
       '2020-06-14' dt,
       page_view_info.recent_days,
       home_count,
       good_detail_count,
       cart_count,
       order_count,
       payment_count
from
     page_view_info
  join   order_info on page_view_info.recent_days = order_info.recent_days
  join   cart_add_info on page_view_info.recent_days = cart_add_info.recent_days
  join   payment_info on page_view_info.recent_days = payment_info.recent_days;

步骤分析

1. 先求 第一天的数据

首页浏览人数
详情页浏览人数

 select
               1 recent_days,
               sum(`if`(page_id ='good_detail',1,0)) good_detail_count,
               sum(`if`(page_id ='home',1,0)) home_count
        -- 1天中一个设备浏览的一种页面是一行
        from dws_traffic_page_visitor_page_view_1d
        where dt='2020-06-14'
        -- 只查询浏览首页和详情页的页面浏览情况
        and (page_id = 'good_detail' or page_id = 'home')

加购人数

        select
               1 recent_days,
                count(*) cart_count
        -- 1天中一个user是一行
        from dws_trade_user_cart_add_1d
        where dt='2020-06-14'

下单人数

        select
               1 recent_days,
                count(*) order_count
        -- 1天中一个user是一行
        from dws_trade_user_order_1d
        where dt='2020-06-14'

支付人数

                select
                       1 recent_days,
                        count(*) payment_count
                -- 1天中一个user是一行
                from dws_trade_user_payment_1d
                where dt='2020-06-14'

2. 求7 30 天

（1） dws_traffic_page_visitor_page_view_nd :流量域访客页面粒度页面浏览最近n日汇总事实表

访客id	页面id	最近7日访问次数 view_count_7d	最近30日访问次数 view_count_30d
1001	home	8	11
1002	home	0	30
1003	home	3	21

计算 首页 浏览人数的时候
7日：view_count_7d>0 计1人
30日：view_count_30d >0 计1人

第一步：先将上表复制一遍 扩展为 7 30

访客id	页面id	最近7日访问次数 view_count_7d	最近30日访问次数 view_count_30d	recent_days
1001	home	8	11	7
1002	home	0	30	7
1003	home	3	21	7
1001	home	8	11	30
1002	home	0	30	30
1003	home	3	21	30

选择 符合条件列

获取到 对应时间 的浏览次数
A  =>   if( recent_days = 7, view_count_7d,view_count_30d  )  

第二步：浏览次数>0 记为1人，这步是为了防止，有的记录 7天内没有访问 但 30天有访问，例如1002 那条记录。

将每条记录 转换为 是否有效 0 1

B  =>  if( A > 0 and page_id ='home',1,0)

第三步：计算总数

sum( B )