6.4 离线数仓—ADS层活动及优惠券主题需求的实现

---

前言

前面完成了ADS层交易主题相关需求的设计和开发，下面进行ADS层活动及优惠券主题的设计和开发。

一、最近30天发布的活动的补贴率

1.需求说明和分析

1）需求说明

统计粒度	指标	说明
活动	补贴率	参与促销活动的订单明细活动减免金额总和/原始金额总和

2）字段分析
根据需求可知，该表要包含以下字段：统计日期、活动ID、与活动相关的维度（至少有活动名称，活动开始日期）、补贴率

2.建表语句

DROP TABLE IF EXISTS ads_activity_stats;
CREATE EXTERNAL TABLE ads_activity_stats
(
    `dt`            STRING COMMENT '统计日期',
    `activity_id`   STRING COMMENT '活动ID',
    `activity_name` STRING COMMENT '活动名称',
    `start_date`    STRING COMMENT '活动开始日期',
    `reduce_rate`   DECIMAL(16, 2) COMMENT '补贴率'
) COMMENT '活动统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_activity_stats/';

3.数据装载

1）我的思路
统计30天内发布的活动，那参与活动的订单肯定也在30天内。根据这个思路统计，语句如下：

select
  '2020-06-14' dt,
  activity_id,
  activity_name,
  start_date,
  activity_reduce_amount_30d/original_amount_30d
from dws_trade_activity_order_nd
where dt='2020-06-14'

2）给的思路
给的思路跟我的思路一样，一遍就写出来了！！！
不过要注意类似XX率的情况要注意转换数据类型，防止去重不成功。

insert overwrite table ads_activity_stats
select * from ads_activity_stats
union
select
    '2020-06-14' dt,
    activity_id,
    activity_name,
    start_date,
    cast(activity_reduce_amount_30d/original_amount_30d as decimal(16,2))
from dws_trade_activity_order_nd
where dt='2020-06-14';

二、最近30天发布的优惠券的补贴率

1.需求说明和分析

1）需求说明

统计粒度	指标	说明
优惠券	补贴率	用券的订单明细优惠券减免金额总和/原始金额总和

2）字段分析
根据需求可知，该表要包含以下字段：统计日期、优惠券ID、与活动相关的维度（至少有优惠券名称，优惠券发布日期）、补贴率，还有一个优惠规则？？？

2.建表语句

DROP TABLE IF EXISTS ads_coupon_stats;
CREATE EXTERNAL TABLE ads_coupon_stats
(
    `dt`          STRING COMMENT '统计日期',
    `coupon_id`   STRING COMMENT '优惠券ID',
    `coupon_name` STRING COMMENT '优惠券名称',
    `start_date`  STRING COMMENT '发布日期',
    `rule_name`   STRING COMMENT '优惠规则，例如满100元减10元',
    `reduce_rate` DECIMAL(16, 2) COMMENT '补贴率'
) COMMENT '优惠券统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_coupon_stats/';

3.数据装载

1）我的思路
跟上面活动的思路一样的，语句如下：

select
  '2020-06-14' dt,
  coupon_id,
  coupon_name,
  start_date,
  coupon_rule,
  cast(coupon_reduce_amount_30d/original_amount_30d as DECIMAL(16,2))
from dws_trade_coupon_order_nd
where dt='2020-06-14'

2）给的思路
给的思路跟我的思路一样，一遍就写出来了！！！

insert overwrite table ads_coupon_stats
select * from ads_coupon_stats
union
select
    '2020-06-14' dt,
    coupon_id,
    coupon_name,
    start_date,
    coupon_rule,
    cast(coupon_reduce_amount_30d/original_amount_30d as decimal(16,2))
from dws_trade_coupon_order_nd
where dt='2020-06-14';