数据仓库项目（第六节）电商业务数仓指标分析实例、拉链表的创建和使用

电商业务数仓分析实例

GMV

什么是GMV

1、建表语句

create table ads_gmv_sum_day( 
    `dt` string COMMENT '统计日期',
    `gmv_count`  bigint COMMENT '当日gmv订单个数',
    `gmv_amount`  decimal(16,2) COMMENT '当日gmv订单总金额',
    `gmv_payment`  decimal(16,2) COMMENT '当日支付金额' 
) COMMENT '每日活跃用户数量'
row format delimited  fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_gmv_sum_day/'
;

2、数据导入

insert into table ads_gmv_sum_day 
select 
'2019-02-10' dt ,
    sum(order_count)  gmv_count ,
    sum(order_amount) gmv_amount ,
    sum(payment_amount) payment_amount 
from dws_user_action 
where dt ='2019-02-10'
group by dt
;

转化率

什么是转化率

ADS层之新增用户占日活跃用户比率

1、建表语句

drop table if exists ads_user_convert_day;
create   table ads_user_convert_day( 
    `dt` string COMMENT '统计日期',
    `uv_m_count`  bigint COMMENT '当日活跃设备',
    `new_m_count`  bigint COMMENT '当日新增设备',
    `new_m_ratio`   decimal(10,2) COMMENT '当日新增占日活的比率'
) COMMENT '每日活跃用户数量'
row format delimited  fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_user_convert_day/'
;

2、 数据导入

insert into table ads_user_convert_day 
select 
    '2019-02-10',
    sum( uc.dc) sum_dc,
    sum( uc.nmc) sum_nmc,
    cast(sum( uc.nmc)/sum( uc.dc)*100 as decimal(10,2))  new_m_ratio
from 
(
    select 
        day_count dc,
        0 nmc
    from ads_uv_count
where  dt='2019-02-10' 

    union all
    select  
        0 dc,
        new_mid_count nmc
    from ads_new_mid_count  
    where create_date='2019-02-10' 
)uc;

ADS层之用户行为漏斗分析

1、建表语句

create   table ads_user_action_convert_day( 
    `dt` string COMMENT '统计日期',
    `total_visitor_m_count`  bigint COMMENT '总访问人数',
    `order_u_count` bigint     COMMENT '下单人数', 
    `visitor2order_convert_ratio`  decimal(10,2) COMMENT '访问到下单转化率', 
    `payment_u_count` bigint     COMMENT '支付人数',
    `order2payment_convert_ratio` decimal(10,2) COMMENT '下单到支付的转化率' 
 ) COMMENT '每日用户行为转化率统计'
row format delimited  fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_user_convert_day/'
;

2、数据导入

insert into table ads_user_action_convert_day
select 
    '2019-02-10',
    uv.day_count,
    ua.order_count,
    cast(ua.order_count/uv.day_count*100 as  decimal(