HiveSQL用户行为漏斗分析

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

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

 1.表结构

 2.数据来源

 实现1：

1）分别查询各个指标的一天数据

select dt,
       sum(`if`(page_id = 'home', 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail', 1, 0)) good_detail_count
from dws_traffic_page_visitor_page_view_1d
where dt = '2020-06-14'
  and page_id in ('home', 'good_detail')
group by dt
 
select dt,
       count(distinct (user_id)) cart_count
from dws_trade_user_cart_add_1d
where dt = '2020-06-14'
group by dt
 
select dt,
       count(distinct (user_id)) order_count
from dws_trade_user_sku_order_1d
where dt = '2020-06-14'
group by dt
 
select dt,
        count(distinct (user_id)) payment_count
from dws_trade_user_payment_1d
where dt = '2020-06-14'
group by dt

2）查询7、30天的首页浏览、详情页浏览

--7d
select dt,
       sum(`if`(page_id = 'home' and view_count_7d > 0, 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail' and view_count_7d > 0, 1, 0)) good_detail_count
from dws_traffic_page_visitor_page_view_nd
where dt = '2020-06-14'
  and page_id in ('home', 'good_detail')
group by dt
--30d
select dt,
       sum(`if`(page_id = 'home' and view_count_30d > 0, 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail' and view_count_30d > 0, 1, 0)) good_detail_count
from dws_traffic_page_visitor_page_view_nd
where dt = '2020-06-14'
  and page_id in ('home', 'good_detail')
group by dt

3）7天和30天的数据来源，行相同，列不同、将7天和30天的查询合并

（1）使用case when 对列进行分支

select recent_days,
       sum(`if`(page_id = 'home' and view_count > 0, 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail' and view_count > 0, 1, 0)) good_detail_count
from (select page_id,
             recent_days,
             case recent_days
                 when 7 then view_count_7d
                 when 30 then view_count_30d
                 end view_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 in ('home', 'good_detail')) t1
group by recent_days

 4）将1与7、30查询进行union

select 1 recent_days,
       sum(`if`(page_id = 'home' and view_count_30d > 0, 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail' and view_count_30d > 0, 1, 0)) good_detail_count
from dws_traffic_page_visitor_page_view_nd
where dt = '2020-06-14'
  and page_id in ('home', 'good_detail')
union
select recent_days,
       sum(`if`(page_id = 'home' and view_count > 0, 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail' and view_count > 0, 1, 0)) good_detail_count
from (select page_id,
             recent_days,
             case recent_days
                 when 7 then view_count_7d
                 when 30 then view_count_30d
                 end view_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 in ('home', 'good_detail')) t1
group by recent_days

 5)加购人数7，30日查询

（1）分别查询7、30

select
       sum(`if`(cart_add_count_7d>0,1,0)) cart_count
from dws_trade_user_cart_add_nd
where dt = '2020-06-14'
 
select
       sum(`if`(cart_add_count_30d>0,1,0)) cart_count
from dws_trade_user_cart_add_nd
where dt = '2020-06-14'

（2）7日、30日查询合并

select
       sum(`if`(`if`(recent_days=7,cart_add_count_7d,cart_add_count_30d) > 0, 1, 0)) cart_count
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

（3）后续字段相同合并

select
       sum(`if`(`if`(recent_days=7,cart_add_count_7d,cart_add_count_30d) > 0, 1, 0)) cart_count
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

select
       sum(`if`(`if`(recent_days=7,coupon_reduce_amount_7d,coupon_reduce_amount_30d)>0,1,0)) order_count
from dws_trade_user_sku_order_nd lateral view explode(array(7, 30)) tmp as recent_days
where dt = '2020-06-14'
group by recent_days

select
       sum(`if`(`if`(recent_days=7,payment_count_7d,payment_amount_30d)>0,1,0)) order_count
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

（4）将字段补齐 1日与7、30日进行union

select 1                                                               recent_days,
       sum(`if`(page_id = 'home' and view_count_30d > 0, 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail' and view_count_30d > 0, 1, 0)) good_detail_count
from dws_traffic_page_visitor_page_view_nd
where dt = '2020-06-14'
  and page_id in ('home', 'good_detail')
union
select recent_days,
       sum(`if`(page_id = 'home' and view_count > 0, 1, 0))        home_count,
       sum(`if`(page_id = 'good_detail' and view_count > 0, 1, 0)) good_detail_count
from (select page_id,
             recent_days,
             case recent_days
                 when 7 then view_count_7d
                 when 30 then view_count_30d
                 end view_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 in ('home', 'good_detail')) t1
group by recent_days
 
select 1,
       count(distinct (user_id)) cart_count
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
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
 
 
select 1,
       count(distinct (user_id)) order_count
from dws_trade_user_sku_order_1d
where dt = '2020-06-14'
union all
select recent_days,
       sum(`if`(`if`(recent_days=7,coupon_reduce_amount_7d,coupon_reduce_amount_30d)>0,1,0)) order_count
from dws_trade_user_sku_order_nd lateral view explode(array(7, 30)) tmp as recent_days
where dt = '2020-06-14'
group by recent_days
 
 
select 1,
       count(distinct (user_id)) payment_count
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_amount_30d)>0,1,0)) order_count
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

（5）进行join

 
select
       '2020-06-14' dt,
       t1.recent_days,
       home_count,
       good_detail_count,
       cart_count,
       order_count,
       payment_count
from (select t1.recent_days,
             home_count,
             good_detail_count,
             cart_count,
             order_count
      from (select t1.recent_days,
                   home_count,
                   good_detail_count,
                   cart_count
            from (select 1                                                               recent_days,
                         sum(`if`(page_id = 'home' and view_count_30d > 0, 1, 0))        home_count,
                         sum(`if`(page_id = 'good_detail' and view_count_30d > 0, 1, 0)) good_detail_count
                  from dws_traffic_page_visitor_page_view_nd
                  where dt = '2020-06-14'
                    and page_id in ('home', 'good_detail')
                  union
                  select recent_days,
                         sum(`if`(page_id = 'home' and view_count > 0, 1, 0))        home_count,
                         sum(`if`(page_id = 'good_detail' and view_count > 0, 1, 0)) good_detail_count
                  from (select page_id,
                               recent_days,
                               case recent_days
                                   when 7 then view_count_7d
                                   when 30 then view_count_30d
                                   end view_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 in ('home', 'good_detail')) t1
                  group by recent_days) t1
                     left join
                 (
                     select 1                         recent_days,
                            count(distinct (user_id)) cart_count
                     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
                     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) t2
                 on t1.recent_days = t2.recent_days) t1
               left join
           (select 1                         recent_days,
                   count(distinct (user_id)) order_count
            from dws_trade_user_sku_order_1d
            where dt = '2020-06-14'
            union all
            select recent_days,
                   sum(`if`(`if`(recent_days = 7, coupon_reduce_amount_7d, coupon_reduce_amount_30d) > 0, 1,
                            0)) order_count
            from dws_trade_user_sku_order_nd lateral view explode(array(7, 30)) tmp as recent_days
            where dt = '2020-06-14'
            group by recent_days
           ) t2
           on t1.recent_days = t2.recent_days) t1
         left join
     (select 1 recent_days,
             count(distinct (user_id)) payment_count
      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_amount_30d) > 0, 1, 0)) order_count
      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
     ) t2
     on t1.recent_days = t2.recent_days