离线数仓（九）【DWS 层开发】（1）https://developer.aliyun.com/article/1532432
V3.0
其实，我们还可以进一步进行优化（把两个粒度统一为一个粒度：用户-商品粒度），因为我们 ADS 最终要求的粒度是 品牌和品类粒度，所以我们的数据从 DWD 到 DWS 层同样的数据需要聚合两次得到统计粒度分别为 用户-品牌和用户-品类 的这么两类数据，然后 ADS 层再对两张表分别进行过滤。
我们这里可以做一个优化：把用户-品牌和用户-品类这两个粒度统一为用户-商品粒度（粒度变得更细），等 ADS 层需要调用的时候再分别过滤，这样我们在 DWD 层到 DWS 层的过程就可以省去大量的计算开销。
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d ( `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT '商品id', `brand_name` STRING COMMENT '品牌名称', `order_count` BIGINT COMMENT '下单次数', `order_num` BIGINT COMMENT '下单件数', `order_amount` DECIMAL(16,2) COMMENT '下单金额' ) COMMENT '交易域用户商品粒度订单最近1日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_brand_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy'); CREATE EXTERNAL TABLE dws_trade_user_brand_order_nd ( `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT '商品id', `brand_name` STRING COMMENT '品牌名称', `order_count_7d` BIGINT COMMENT '下单次数', `order_num_7d` BIGINT COMMENT '下单件数', `order_amount_7d` DECIMAL(16,2) COMMENT '最近7天的下单金额', `order_count_30d` BIGINT COMMENT '下单次数', `order_num_30d` BIGINT COMMENT '下单件数', `order_amount_30d` DECIMAL(16,2) COMMENT '最近30天的下单金额' ) COMMENT '交易域品牌粒度订单最近7/30日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_brand_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');
装载语句这里就省略了，只不过就是 group by 的字段改成了 user_id和sku_id。
这样，我们最终的派生指标分类就可以分为两类了，最终根据统计周期只需要建 4 张表即可：
1.0.5、历史至今的汇总表
create external table dws_trade_user_order_td ( `user_id` STRING COMMENT '用户id', `order_date_first` STRING COMMENT '首次下单日期', `order_date_last` STRING COMMENT '最近一次下单日期', `order_count` BIGINT COMMENT '下单次数', `order_num` BIGINT COMMENT '下单件数', `order_total_amount` DECIMAL(16,2) COMMENT '历史下单总额' )comment '交易域用户粒度历史至今订单汇总事实表' partitioned by (`dt` STRING) stored as orc location '/warehouse/gmall/dws/dws_trade_user_order_td' tblproperties ('orc.compress' = 'snappy');
装载数据（区分首日和每日）
-- 数据装载 -- 2020-06-14 insert overwrite table dws_trade_user_order_td partition(dt='2020-06-14') select user_id, min(date_id), max(date_id), count(distinct (order_id)), sum(sku_num), sum(split_total_amount) from dwd_trade_order_detail_inc group by user_id; -- 2020-06-15 insert overwrite table dws_trade_user_order_td partition(dt='2020-06-15') select user_id, min(order_date_first), max(order_date_last), sum(order_count), sum(order_num), sum(order_total_amount) from ( select user_id, order_date_first, order_date_last, order_count, order_num, order_total_amount from dws_trade_user_order_td where dt=date_sub('2020-06-15',1) union all select user_id, '2020-06-15', '2020-06-15', count(distinct(order_id)) order_count_1d, sum(sku_num) order_num_1d, sum(split_total_amount) order_total_amount_1d from dwd_trade_order_detail_inc where dt='2020-06-15' group by user_id )t1 group by user_id;
1.0.6、DWS 层设计总结
（1）1d 表
表结构：行由派生指标的统计粒度决定，列由统计粒度id和派生指标决定。
分区：按天分区，每天存放的是当天明细的汇总结果。
数据装载：找到 DWD 层与之对应的明细表，从明细表拿一个分区的数据进行汇总，汇总之后放到汇总表的当天分区。
（2）nd 表
表结构：行和列和 1d 表基本一致，无非就是把派生指标分为 7d 和 30d。
分区：按天分区，每天存放的是截止当前最近n天的汇总结果。
数据装载：优先去 DWS 层的 1d 表中去取数据，直接拿30个分区的数据进行聚合（聚合时用sum（if）把7d的数据和30d的数据区分开来，聚合之后放到汇总表的当天分区。
（3）td 表
表结构：行和列还是分别根据粒度和派生指标决定。
分区：按天分区，每天存放的是历史截止当前的汇总结果。
数据装载：首日需要从 DWS 层的 1d 表中或者 DWD 中的明细表中获取数据。第二天之后从 td 表中拿到前一个分区的数据，然后再从 1d 表或者 DWD 中的明细表找到今天的汇总结果，然后把这两部分数据进行 union all 得到截止今天的汇总结果。
1.1、最近1日汇总表
1.1.1、 交易域用户商品粒度订单最近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 '/warehouse/gmall/dws/dws_trade_user_sku_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');
这张表可以大概分为两部分：维度字段和度量字段。其中度量字段中的统计粒度是必须的，退化字段是为了方便我们后期使用添加的（也可以不添加），而度量字段除了 ADS 层要求的，我们这里也“前瞻性”地添加了一些度量指标，防止后期该表麻烦，而且添加几个字对计算开销没什么影响，毕竟一张表里的聚合函数只会取一次数据。
分区我们按天分区，每天存储的内容都是当天的汇总结果。
数据装载（区分首日/每日装载）
因为我们在首日装载的时候有大量的历史数据（从属于不同的分区），所以我们要动态分区来装载。
首日装载：
我们在建表的时候退化了很多商品维度的字段，所以我们在装载数据的时候就应该把这部分数据通过 sku_id join 到一起，而我们 dwd 中存在大量历史的数据，所以用 14 号的商品信息去关联历史上的下单记录很可能对应不上，所以我们需要判断 null。
我们在搭建数仓的时候，历史的订单数据（事实）是有的，但是历史的商品信息（维度）是没有的，所以我们这里只能用首日的商品信息去关联历史的下单信息。
set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_user_sku_order_1d partition(dt) select user_id, id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, order_count_1d, order_num_1d, order_original_amount_1d, activity_reduce_amount_1d, coupon_reduce_amount_1d, order_total_amount_1d, dt from ( select dt, user_id, sku_id, count(*) order_count_1d, sum(sku_num) order_num_1d, sum(split_original_amount) order_original_amount_1d, sum(nvl(split_activity_amount,0.0)) activity_reduce_amount_1d, sum(nvl(split_coupon_amount,0.0)) coupon_reduce_amount_1d, sum(split_total_amount) order_total_amount_1d from dwd_trade_order_detail_inc group by dt,user_id,sku_id )od left join ( select id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name from dim_sku_full where dt='2020-06-14' )sku on od.sku_id=sku.id;
每日装载：
insert overwrite table dws_trade_user_sku_order_1d partition(dt='2020-06-15') select user_id, id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, order_count, order_num, order_original_amount, activity_reduce_amount, coupon_reduce_amount, order_total_amount from ( select user_id, sku_id, count(*) order_count, sum(sku_num) order_num, sum(split_original_amount) order_original_amount, sum(nvl(split_activity_amount,0)) activity_reduce_amount, sum(nvl(split_coupon_amount,0)) coupon_reduce_amount, sum(split_total_amount) order_total_amount from dwd_trade_order_detail_inc where dt='2020-06-15' group by user_id,sku_id )od left join ( select id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name from dim_sku_full where dt='2020-06-15' )sku on od.sku_id=sku.id;
离线数仓（九）【DWS 层开发】（3）https://developer.aliyun.com/article/1532437
