数仓DWS层设计思想---开始搭建之前的回顾

DWS层存在的意义：
通过指标体系，分析并整合出通用的、重复使用的派生指标，减少从（DWD、DIM）==>（ADS）重复计算
构建指标体系：
原子指标 = 业务过程+度量值+聚合逻辑
派生指标 = 原子指标+统计周期+业务限定+统计粒度
衍生指标 = 多个派生指标的整合（比例、比率等）
使用Excel汇总每一个派生指标的具体构成（基本上就是指标体系），包括上述等式中的每一部分，例如下图。从已汇总的表格中提炼公共点

部分派生指标的统计
公共三要素：业务过程、统计周期、统计粒度：
1、业务过程：对应sql中 FROM，对应DWD中不同的事实表
2、统计周期：对应sql中 WHERE 的时间筛选部分，对应DWS中不同的表（以命名规范区分）
3、统计粒度：对应sql中 GROUP BY
确认上述三要素相同的派生指标将放置于同一张DWS表中，具体的，会根据不同的度量值和聚合逻辑设计表中的select字段。
DWS层表格命名规范：
dws--数据域--统计粒度--业务过程--统计周期（1d/nd/td）

设计（指标分析）举例

1、指标需求（ADS层）

品牌相关需求

品类相关需求

2、指标定义

拿到需求后，按照派生指标公式（派生指标 = 原子指标+统计周期+业务限定+统计粒度），对需求进行拆分、定义，举例如下：

派生指标定义举例

衍生指标定义举例

3、汇总派生指标

指标定义完毕后，建立Excel汇总指标体系，将三要素相同的指标用相同底色标注。
相同底色的指标将在同一个DWS表中体现。

指标体系

4、建表 & 装载（以品牌指标为例）

最近1天下单——建表
1、将Excel表中的字段写入
2、根据业务过程（下单），参考对应的DWD层事实表（dwd_order_detail_inc），添加与主键（粒度）相关的其他字段，用以丰富表字段内容
3、粒度细化
由于“最近n天”的数据来源于“最近1天”，在统计部分累加型指标时（最近n天下单人数），是按照每天进行读取，这样就会出现因时间范围划分不准确而产生的重复数据问题（应按照n天进行划分并去重，最后累加。但是实际上是将n个独立去重的结果相累加）。这种问题是因为粒度无法满足统计需求而产生的，故要解决此问题，我们需要对粒度做出进一步的细化，从单一的（tm_id），变成（user_id, tm_id）。粒度越细，表通用性越强

CREATE EXTERNAL TABLE dws_trade_user_tm_order_1d
(
    `user_id`            STRING COMMENT '用户id',
    `tm_id`            STRING COMMENT '品牌id',
    `tm_name`          STRING COMMENT '品牌名称',
    `order_count`      BIGINT COMMENT '最近1日订单数',
--   `order_user_count` BIGINT COMMENT '最近1日订单人数',
    `order_num`               BIGINT COMMENT '最近1日商品下单件数',
    `order_original_amount` DECIMAL(16, 2) COMMENT '最近1日下单原始价格',
    `order_activity_amount` DECIMAL(16, 2) COMMENT '最近1日下单活动优惠',
    `order_coupon_amount`   DECIMAL(16, 2) COMMENT '最近1日下单优惠券优惠',
    `order_total_amount`    DECIMAL(16, 2) COMMENT '最近1日下单最终价格'
) COMMENT '交易域用户品牌粒度订单最近1日汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/xxx'
    TBLPROPERTIES ('orc.compress' = 'snappy');

最近1天下单——装载
从相关表格中拉取数据，按照分区规划、数据流动情况进行装载

insert overwrite table dws_trade_user_tm_order_1d partition(dt='2020-06-14')
select
    user_id,
    tm_id,
    tm_name,
    count(*) order_count,
    sum(sku_num) order_num,
    sum(split_original_amount) order_original_amount,
    sum(nvl(split_activity_amount,0.0)) order_activity_amount,
    sum(nvl(split_coupon_amount,0.0)) order_coupon_amount,
    sum(split_total_amount) order_total_amount
from
(
    select
        sku_id,
        user_id,
        sku_num,
        split_original_amount,
        split_activity_amount,
        split_coupon_amount,
        split_total_amount
    from dwd_trade_order_detail_inc
    where dt = '2020-06-14'
)od
left join
(
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt='2020-06-14'
)sku
on od.sku_id=sku.id
group by user_id,tm_id,tm_name;

最近n天下单——建表

DROP TABLE IF EXISTS dws_trade_user_tm_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_tm_order_nd
(
    `user_id`            STRING COMMENT '用户id',
    `tm_id`            STRING COMMENT '品牌id',
    `tm_name`          STRING COMMENT '品牌名称',
    `order_count_7d`      BIGINT COMMENT '最近7日订单数',
    `order_num_7d`               BIGINT COMMENT '最近7日商品下单件数',
    `order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始价格',
    `order_activity_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单活动优惠',
    `order_coupon_amount_7d`   DECIMAL(16, 2) COMMENT '最近7日下单优惠券优惠',
    `order_total_amount_7d`    DECIMAL(16, 2) COMMENT '最近7日下单最终价格',
    `order_count_30d`      BIGINT COMMENT '最近30日订单数',
    `order_num_30d`               BIGINT COMMENT '最近30日商品下单件数',
    `order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单原始价格',
    `order_activity_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单活动优惠',
    `order_coupon_amount_30d`   DECIMAL(16, 2) COMMENT '最近30日下单优惠券优惠',
    `order_total_amount_30d`    DECIMAL(16, 2) COMMENT '最近30日下单最终价格'
) COMMENT '交易域用户品牌粒度订单最近n日汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/xxx'
    TBLPROPERTIES ('orc.compress' = 'snappy');

最近n天下单——装载
1、最近1天表格已搭建完毕，最近n天直接使用最近1天进行装载
2、最近n天实际上是“最近7天”与“最近30天”的合并，那么，在最外层的筛选中，必须按照“最近30天”的范围进行筛选，以保证数据准确性。

select
    user_id,
    tm_id,
    tm_name,
    sum(if(dt>=date_add('2020-06-14',-6),order_count,0)) order_count_7d,
    sum(if(dt>=date_add('2020-06-14',-6),order_num,0)) order_num_7d,
    sum(if(dt>=date_add('2020-06-14',-6),order_original_amount,0)) order_original_amount_7d,
    sum(if(dt>=date_add('2020-06-14',-6),order_activity_amount,0)) order_activity_amount_7d,
    sum(if(dt>=date_add('2020-06-14',-6),order_coupon_amount,0)) order_coupon_amount_7d,
    sum(if(dt>=date_add('2020-06-14',-6),order_total_amount,0)) order_total_amount_7d,
    sum(order_count) order_count_30d,
    sum(order_num) order_num_30d,
    sum(order_original_amount) order_original_amount_30d,
    sum(order_activity_amount) order_activity_amount_30d,
    sum(order_coupon_amount) order_coupon_amount_30d,
    sum(order_total_amount) order_total_amount_30d
from dws_trade_user_tm_order_1d
where dt>=date_add('2020-06-14',-29)
group by user_id,tm_id,tm_name;

最近1日、最近n日设计思想总结

品牌表、品类表两者数据的来源均为DWD层的dwd_trade_order_detail_inc，他们之间的主要区别就在于sql语句中的GROUP BY聚合字段。
即便是当前品牌表粒度(user_id, tm_id)、品类表粒度(user_id, cate_id)已经做了细化，两者的聚合过程仍不可避免的存在冗余。（冗余并不准确，但实在是找不到别的词来形容了。实际上这一整段都描述的都不准确，将就看吧）
为了解决这样的问题，提高整个DWS、ADS层统计性能，我们仍需要对粒度做出更加进一步的细化。
最终细化结果为（user_id, sku_id）。至此，将品牌、品类指标汇总为一张DWS表，在需要时进行二次聚合。以下为“用户商品粒度订单最近1日”的最终建表语句。

DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(
    `user_id`                   STRING COMMENT '用户id',
    `sku_id`                    STRING COMMENT 'sku_id',
    `sku_name`                  STRING COMMENT 'sku名称',
    `category1_id`              STRING COMMENT '一级分类id',
    `category1_name`            STRING COMMENT '一级分类名称',
    `category2_id`              STRING COMMENT '一级分类id',
    `category2_name`            STRING COMMENT '一级分类名称',
    `category3_id`              STRING COMMENT '一级分类id',
    `category3_name`            STRING COMMENT '一级分类名称',
    `tm_id`                     STRING COMMENT '品牌id',
    `tm_name`                   STRING COMMENT '品牌名称',
    `order_count_1d`            BIGINT COMMENT '最近1日下单次数',
    `order_num_1d`              BIGINT COMMENT '最近1日下单件数',
    `order_original_amount_1d`  DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',
    `coupon_reduce_amount_1d`   DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',
    `order_total_amount_1d`     DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近1日汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/xxx'
    TBLPROPERTIES ('orc.compress' = 'snappy');