6.2 离线数仓—ADS层商品主题需求的实现

---

前言

前面完成了ADS层用户主题相关需求的设计和开发，下面进行ADS层商品主题的设计和开发。

一、最近7/30日各品牌复购率

1.需求说明和分析

1）需求说明

统计周期	统计粒度	指标	说明
最近7、30日	品牌	复购率	重复购买人数占购买人数比例

2）字段分析
根据需求，可以得知要包含以下字段：统计日期、最近天数、品牌ID、品牌名称、复购率

2.建表语句

DROP TABLE IF EXISTS ads_repeat_purchase_by_tm;
CREATE EXTERNAL TABLE ads_repeat_purchase_by_tm
(
    `dt`                STRING COMMENT '统计日期',
    `recent_days`       BIGINT COMMENT '最近天数,7:最近7天,30:最近30天',
    `tm_id`             STRING COMMENT '品牌ID',
    `tm_name`           STRING COMMENT '品牌名称',
    `order_repeat_rate` DECIMAL(16, 2) COMMENT '复购率'
) COMMENT '各品牌复购率统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_repeat_purchase_by_tm/';

3.数据装载

1）我的思路
先算7天的，直接从交易域用户商品粒度订单最近n日汇总表获取数据，按用户+品牌分组，对于每个用户的每个品牌，若最近7日下单次数为1，则代表了购买该品牌的人数，若最近7日下单次数大于1，则代表了重复购买该品牌的人数。要注意的是，这个表的粒度是用户——商品粒度，可能出现一个用户多次购买一个品牌的情况，所以要按用户+品牌进行分组语句如下：

select
  tm_id,
  tm_name,
  sum(if(order_count>1),1,0)/sum(if(order_count>=1,1,0))
from
(
  select
    user_id,
    tm_id,
    tm_name,
    sum(order_count_7d) order_count
  from dws_trade_user_sku_order_nd
  where dt='2020-06-14'
  group by user_id,tm_id,tm_name
)t1
group by tm_id,tm_name

30天的复购率方法类似，语句如下：

select
  tm_id,
  tm_name,
  sum(if(order_count>1),1,0)/sum(if(order_count>=1,1,0))
from
(
  select
    user_id,
    tm_id,
    tm_name,
    sum(order_count_30d) order_count
  from dws_trade_user_sku_order_nd
  where dt='2020-06-14'
  group by user_id,tm_id,tm_name
)t1
group by tm_id,tm_name

相同的数据，聚合逻辑不同，采用炸裂的方式炸裂两份。

select
  recent_days,
  tm_id,
  tm_name,
  sum(if(order_count>1),1,0)/sum(if(order_count>=1,1,0))
from
(
  select
    recent_days,
    user_id,
    tm_id,
    tm_name,
    if(recent_days=7,sum(order_count_7d),sum(order_count_30d)) 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 user_id,tm_id,tm_name,recent_days
)t1
group by tm_id,tm_name,recent_days

其中，子查询语句可以这样写：

sum(if(recent_days=7,order_count_7d,order_count_30d) order_count

2）给的思路

insert overwrite table ads_repeat_purchase_by_tm
select * from ads_repeat_purchase_by_tm
union
select
    '2020-06-14' dt,
    recent_days,
    tm_id,
    tm_name,
    cast(sum(if(order_count>=2,1,0))/sum(if(order_count>=1,1,0)) as decimal(16,2))
from
(
    select
        '2020-06-14' dt,
        recent_days,
        user_id,
        tm_id,
        tm_name,
        sum(order_count) order_count
    from
    (
        select
            recent_days,
            user_id,
            tm_id,
            tm_name,
            case recent_days
                when 7 then order_count_7d
                when 30 then order_count_30d
            end order_count
        from dws_trade_user_sku_order_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='2020-06-14'
    )t1
    group by recent_days,user_id,tm_id,tm_name
)t2
group by recent_days,tm_id,tm_name;

二、各品牌商品交易统计

1.需求说明和分析

1）需求说明

统计周期	统计粒度	指标	说明
最近1、7、30日	品牌	订单数	略
最近1、7、30日	品牌	订单人数	略
最近1、7、30日	品牌	退单数	略
最近1、7、30日	品牌	退单人数	略

2）字段分析
根据需求说明，可以得知，要包含以下字段：统计日期、最近天数、订单数、订单人数、退单数、退单人数、品牌ID、品牌名称

2.建表语句

DROP TABLE IF EXISTS ads_trade_stats_by_tm;
CREATE EXTERNAL TABLE ads_trade_stats_by_tm
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count`             BIGINT COMMENT '订单数',
    `order_user_count`        BIGINT COMMENT '订单人数',
    `order_refund_count`      BIGINT COMMENT '退单数',
    `order_refund_user_count` BIGINT COMMENT '退单人数'
) COMMENT '各品牌商品交易统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_trade_stats_by_tm/';

3.数据装载

1）我的思路
先算1天内订单数：

--最近1天内订单数：
--1个用户对一个品牌的下单次数（一个品牌可能对应多个用户）
--1个品牌的下单次数和下单人数
select
  tm_id,
  tm_name,
  count(*)
  sum(order_count)
from
(
  select
    user_id,
    tm_id,
    tm_name,
    sum(order_count_1d) order_count
  from dws_trade_user_sku_order_1d
  where dt='2020-06-14'
  group by user_id,tm_id,tm_name
)t1
group by tm_id,tm_name

算7天内订单数和订单人数：