离线数仓（九）【DWS 层开发】

大家好，欢迎来到IT知识分享网。

前言

        上一个 DWD 层用了半个月时间，但是慢有慢的好处；今天开始 DWS 层的学习，目标是 4 月初把项目完成，完了赶紧从头回顾一遍项目。

        今天操场跑了 20 分钟，顺便在这里记录一下，现在每周只有没早八的时候能跑一下了，近一年没有好好跑步了，这个习惯应该找回来了。

1、DWS 层开发

        DWS 也叫数据汇总层，它的职责就是把一些计算耗时的公共中间结果提前保存起来（比如提前 join  两张表），用空间换时间！所以 DWS 层是依托于需求的（需求驱动），没有需求我们是不知道需要保存哪些中间结果的。

1.0、DWS 层设计

1.0.1、设计要点： 

（1）DWS层的设计参考指标体系（原子、派生、衍生指标等）。一般能通过一张明细事实表聚合得到的，那就是派生指标；得不到就是衍生指标。

（2）DWS层的数据存储格式为ORC列式存储 + snappy压缩。

（3）DWS层表名的命名规范为dws_数据域_统计粒度_业务过程_统计周期（1d/nd/td），这里的统计粒度可以是多个

注：1d表示最近1日，nd表示最近n日，td表示历史至今。

        我们接下来的汇总表的设计并不会把 1d、nd、td 这三个字段都放到一张表里，比如对于页面浏览这个业务过程，如果我们把三个字段都放到一张表里，那么当装载数据的时候，它需要从 DWD 的当天分区、前n天分区、历史分区中拿数据，这样的代价太大了，而如果我们把三个字段分开，一天的汇总结果放到一天的分区，当够了n天的时候，我们直接从 DWS 同层的 1d 中拿7天的就够了，而且 1d 的表已经帮我们聚合好了。尽管我们在学习 DWD 层的时候希望同层的表不要有依赖关系，但是在这里是可以的。

        而且我们并不会按照统计周期去创建n张表，而是只创建两张表：统计周期为1的和统计周期为n的。

1.0.2、构建指标体系

下面我们根据 ADS 层商品域的交易主题下的需求来进行指标分析：

        这里的度量值为 1 ，意思是我们并没有专门的维度供我们计算（度量值和维度的界限并不是那么清晰，并不是说度量值就非得是什么金额、下单件数等），我们的一行数据就代表一个度量值（这里指一个订单）。 

1.0.3、抽取派生指标

        我们需要列出所有需求的指标类型，然后从一堆需求中找到其中业务过程、统计粒度、统计周期相同的派生指标放到一张汇总表里。一般我们会把所有需求对应的派生指标都放到一张表里去分析：

        比如上面的表格是我们把所有交易域的需求对应的派生指标放到了一起，可以看到，我们需要创建4张汇总表，但是考虑到不同的统计周期，我们这里需要创建 8 张汇总表。

        首先至少得 3 个字段：品牌id，下单次数、下单人数。考虑到 ADS 层需要展示更详细的数据比如品牌名称，如果我们这里不加的话到时候还得用 DWS 层的表去和 DIM 层的维度表去 join 才能得到。所以我们这里尽量也做一个维度退化。毕竟我们分层的时候，DIM 层是同时跨越 DWD 和 DWS 层的：

关于分区，对于 1d 的汇总结果，它的当日分区放的就是当日的明细汇总结果。

 1.0.4、最近1d和nd的汇总表

        我们的下单次数和下单人数这两个派生指标是可以放到一张汇总表的：

CREATE EXTERNAL TABLE dws_trade_brand_order_1d ( `brand_id` STRING COMMENT '品牌id', `brand_name` STRING COMMENT '品牌名称', `order_count` STRING COMMENT '下单次数', `order_user_count` STRING COMMENT '下单人数' ) COMMENT '交易域品牌粒度订单最近1日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_brand_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

        这张表显然十分简单，但是考虑到我们数仓的需求并不是一成不变的，比如现在增加两个需求：统计最近1/7/30日各品牌的下单件数、下单金额。显然这两个指标也都是派生指标，而且它们的业务过程（下单）、统计粒度（品牌）和统计周期（1/7/30）和我们这张表都是一样的，所以我们就需要修改这张表结构，但是修改表就意味着我们需要处理这张表原本的历史数据，显然十分麻烦。所以我们在最初建表的时候就应该尽可能避免将来的改表操作，为此，我们提供了两种方案：

        1. 之前我们把所有业务过程、统计粒度和统计周期相同的派生指标会都放到一个汇总表里，现在我们可以为每个派生指标建一张汇总表，这样即使有新的派生指标加进来也不用担心对旧表的影响，但是这样我们的 DWS 层会出现大量的表，所以我们一般并不采用。

        2. 我们可以“前瞻性”地把这张事实表（比如下单的事实表就是 order_detail）的度量值去预测将来可能用到的派生指标：

CREATE EXTERNAL TABLE dws_trade_brand_order_1d ( `brand_id` STRING COMMENT '品牌id', `brand_name` STRING COMMENT '品牌名称', `order_count` BIGINT COMMENT '下单次数', `order_user_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');

这张表的数据装载也很简单：

insert overwrite table dws_trade_brand_order_1d partition (dt='2020-06-14') select tm_id, tm_name, count(*), count(distinct user_id), sum(sku_num), sum(split_total_amount) from ( select sku_id, user_id, sku_num, 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 tm_id,tm_name;

        关于下单的 1d 的汇总表已经设计完毕了，解下来就是 nd 汇总表的设计了。对于 nd ，它每天的分区里放的就是这一天最近 n 天的数据。

CREATE EXTERNAL TABLE dws_trade_brand_order_nd ( `brand_id` STRING COMMENT '品牌id', `brand_name` STRING COMMENT '品牌名称', `order_count_7d` BIGINT COMMENT '下单次数', `order_user_count_7d` BIGINT COMMENT '下单人数', `order_num_7d` BIGINT COMMENT '下单件数', `order_amount_7d` DECIMAL(16,2) COMMENT '最近7天的下单金额', `order_count_30d` BIGINT COMMENT '下单次数', `order_user_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');

        关于 nd 表的数据从哪取？我们一般都是直接从它的 1d 表去拿的，如果实在不行（比如我们的需求中并没有 1d 的需求）那我们只能去 DWD 去拿了。

数据装载：我们只需要分别查询出 7d 和 30d 的数据直接 join 即可：

insert overwrite table dws_trade_brand_order_nd partition (dt='2020-06-14') select xxxx from ( -- 7d select brand_id, brand_name, sum(order_count), sum(order_user_count), sum(order_num), sum(order_amount) from dws_trade_brand_order_1d where dt>=date_sub('2020-06-14',6) group by brand_id,brand_name ) d7 join ( --30d select brand_id, brand_name, sum(order_count), sum(order_user_count), sum(order_num), sum(order_amount) from dws_trade_brand_order_1d where dt>=date_sub('2020-06-14',29) group by brand_id,brand_name )d30 on d7.sku_id=d30.sku_id

这里，因为前 30 天的数据包含了前 7 天的数据，所以我们可以对这个 SQL 进行一个优化：

insert overwrite table dws_trade_brand_order_nd partition (dt='2020-06-14') select brand_id, brand_name, sum(`if`(dt>=date_sub('2020-06-14',6),order_count,0)), sum(`if`(dt>=date_sub('2020-06-14',6),order_user_count,0)), sum(`if`(dt>=date_sub('2020-06-14',6),order_num,0)), sum(`if`(dt>=date_sub('2020-06-14',6),order_amount,0)), sum(order_count), sum(order_user_count), sum(order_num), sum(order_amount) from dws_trade_brand_order_1d where dt>=date_sub('2020-06-14',29) group by brand_id,brand_name;

        这样我们就不用专门去查询近 7 天的数据，而且还少 join 了一次。但是这张表存在一个问题，我们之前在 1d 表中计算下单人数的时候对用户是去了重的，因为一个用户一天可能下单多次；按道理 7d 30d 同样需要对相同的用户进行去重，因为一个用户可能在多天中下单，所以我们这里需要解决这个同一用户重复计算的问题。

V2.0

我们可以通过改变这张表的粒度（改为用户品牌和用户品类粒度）来解决这个问题：

既然粒度已经是用户品牌粒度，那指标下单人数就不需要了，只需要在查询的时候直接去重即可。

CREATE EXTERNAL TABLE dws_trade_user_brand_order_1d ( `user_id` STRING COMMENT '用户id', `brand_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_user_brand_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

装载语句只需要稍微改改即可：

insert overwrite table dws_trade_user_brand_order_1d partition (dt='2020-06-14') select user_id, tm_id, tm_name, count(*), sum(sku_num), sum(split_total_amount) from ( select sku_id, user_id, sku_num, 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;

修改 nd 表的建表语句：

CREATE EXTERNAL TABLE dws_trade_user_brand_order_nd ( `user_id` STRING COMMENT '用户id', `brand_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');

修改 nd 表的装载语句： 

insert overwrite table dws_trade_user_brand_order_nd partition (dt='2020-06-14') select user_id, brand_id, brand_name, sum(`if`(dt>=date_sub('2020-06-14',6),order_count,0)), sum(`if`(dt>=date_sub('2020-06-14',6),order_num,0)), sum(`if`(dt>=date_sub('2020-06-14',6),order_amount,0)), sum(order_count), sum(order_num), sum(order_amount) from dws_trade_user_brand_order_nd where dt>=date_sub('2020-06-14',29) group by brand_id,brand_name;

现在，我们的指标（各品牌的下单件数和下单人数） 就变成了：

最终我们的派生指标根据统计粒度分为两类：

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; 

1.1.2、交易域用户商品粒度退单最近1日汇总表

建表语句

DROP TABLE IF EXISTS dws_trade_user_sku_order_refund_1d; CREATE EXTERNAL TABLE dws_trade_user_sku_order_refund_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_refund_count_1d` BIGINT COMMENT '最近1日退单次数', `order_refund_num_1d` BIGINT COMMENT '最近1日退单件数', `order_refund_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_refund_1d' TBLPROPERTIES ('orc.compress' = 'snappy'); 

数据装载（区分首日/每日） 

set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_user_sku_order_refund_1d partition(dt) select user_id, sku_id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, order_refund_count, order_refund_num, order_refund_amount, dt from ( select dt, user_id, sku_id, count(*) order_refund_count, sum(refund_num) order_refund_num, sum(refund_amount) order_refund_amount from dwd_trade_order_refund_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_refund_1d partition(dt='2020-06-15') select user_id, sku_id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, order_refund_count, order_refund_num, order_refund_amount from ( select user_id, sku_id, count(*) order_refund_count, sum(refund_num) order_refund_num, sum(refund_amount) order_refund_amount from dwd_trade_order_refund_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; 

1.1.3、交易域用户粒度订单最近1日汇总表

1）建表语句

行：最近一天用户的下单次数、件数和总金额。

列：维度只有用户id，并没有别的维度属性，所以之后 ADS 层如果有相关需要用到用户信息的时候再去 join。剩下的都是统计值。

DROP TABLE IF EXISTS dws_trade_user_order_1d; CREATE EXTERNAL TABLE dws_trade_user_order_1d ( `user_id` STRING COMMENT '用户id', `order_count_1d` BIGINT COMMENT '最近1日下单次数', `order_num_1d` BIGINT COMMENT '最近1日下单商品件数', `order_original_amount_1d` DECIMAL(16, 2) COMMENT '最近1日最近1日下单原始金额', `activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单活动优惠金额', `coupon_reduce_amount_1d` DECIMAL(16, 2) COMMENT '下单优惠券优惠金额', `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_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载（首日/每日）

        因为有历史数据，所以需根据 user_id 和 dt 进行分组 。

insert overwrite table dws_trade_user_order_1d partition(dt) select user_id, count(distinct(order_id)), sum(sku_num), sum(split_original_amount), sum(nvl(split_activity_amount,0)), sum(nvl(split_coupon_amount,0)), sum(split_total_amount), dt from dwd_trade_order_detail_inc group by user_id,dt;

这里的 order_id 需要进行去重，因为数据来源是 order_detail，所以一个订单有多个商品，下单时一个 order_id 会有多个下单记录。 

每日表只需要根据 user_id 进行分组，因为 dwd 层的该天分区没有历史数据。

insert overwrite table dws_trade_user_order_1d partition(dt='2020-06-15') select user_id, count(distinct(order_id)), sum(sku_num), sum(split_original_amount), sum(nvl(split_activity_amount,0)), sum(nvl(split_coupon_amount,0)), sum(split_total_amount) from dwd_trade_order_detail_inc where dt='2020-06-15' group by user_id;

1.1.4、交易域用户粒度加购最近1日汇总表

1）建表语句

行：用户最近 1 天加购的件数和次数

DROP TABLE IF EXISTS dws_trade_user_cart_add_1d; CREATE EXTERNAL TABLE dws_trade_user_cart_add_1d ( `user_id` STRING COMMENT '用户id', `cart_add_count_1d` BIGINT COMMENT '最近1日加购次数', `cart_add_num_1d` BIGINT COMMENT '最近1日加购商品件数' ) COMMENT '交易域用户粒度加购最近1日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_cart_add_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载（首日/每日）

装载很简单，首日只需要对 dt 进行额外分组，每日不需要。

insert overwrite table dws_trade_user_cart_add_1d partition(dt) select user_id, count(*), sum(sku_num), dt from dwd_trade_cart_add_inc group by user_id,dt;

insert overwrite table dws_trade_user_cart_add_1d partition(dt='2020-06-15') select user_id, count(*), sum(sku_num) from dwd_trade_cart_add_inc where dt='2020-06-15' group by user_id;

1.1.5、交易域用户粒度支付最近1日汇总表

1）建表语句

行：每个用户最近 1 日的支付次数、支付的商品件数、金额。

列：一个维度和三个统计指标。

DROP TABLE IF EXISTS dws_trade_user_payment_1d; CREATE EXTERNAL TABLE dws_trade_user_payment_1d ( `user_id` STRING COMMENT '用户id', `payment_count_1d` BIGINT COMMENT '最近1日支付次数', `payment_num_1d` BIGINT COMMENT '最近1日支付商品件数', `payment_amount_1d` DECIMAL(16, 2) COMMENT '最近1日支付金额' ) COMMENT '交易域用户粒度支付最近1日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_payment_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载（首日/每日）

insert overwrite table dws_trade_user_payment_1d partition(dt) select user_id, count(distinct(order_id)), sum(sku_num), sum(split_payment_amount), dt from dwd_trade_pay_detail_suc_inc group by user_id,dt;

insert overwrite table dws_trade_user_payment_1d partition(dt='2020-06-15') select user_id, count(distinct(order_id)), sum(sku_num), sum(split_payment_amount) from dwd_trade_pay_detail_suc_inc where dt='2020-06-15' group by user_id;

1.1.6、交易域省份粒度订单最近1日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_trade_province_order_1d; CREATE EXTERNAL TABLE dws_trade_province_order_1d ( `province_id` STRING COMMENT '用户id', `province_name` STRING COMMENT '省份名称', `area_code` STRING COMMENT '地区编码', `iso_code` STRING COMMENT '旧版ISO-3166-2编码', `iso_3166_2` STRING COMMENT '新版版ISO-3166-2编码', `order_count_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_province_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载（首日/全日）

因为有维度退化，所以需要进行 join 

set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_province_order_1d partition(dt) select province_id, province_name, area_code, iso_code, iso_3166_2, order_count_1d, order_original_amount_1d, activity_reduce_amount_1d, coupon_reduce_amount_1d, order_total_amount_1d, dt from ( select province_id, count(distinct(order_id)) order_count_1d, sum(split_original_amount) order_original_amount_1d, sum(nvl(split_activity_amount,0)) activity_reduce_amount_1d, sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d, sum(split_total_amount) order_total_amount_1d, dt from dwd_trade_order_detail_inc group by province_id,dt )o left join ( select id, province_name, area_code, iso_code, iso_3166_2 from dim_province_full where dt='2020-06-14' )p on o.province_id=p.id;

insert overwrite table dws_trade_province_order_1d partition(dt='2020-06-15') select province_id, province_name, area_code, iso_code, iso_3166_2, order_count_1d, order_original_amount_1d, activity_reduce_amount_1d, coupon_reduce_amount_1d, order_total_amount_1d from ( select province_id, count(distinct(order_id)) order_count_1d, sum(split_original_amount) order_original_amount_1d, sum(nvl(split_activity_amount,0)) activity_reduce_amount_1d, sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d, sum(split_total_amount) order_total_amount_1d from dwd_trade_order_detail_inc where dt='2020-06-15' group by province_id )o left join ( select id, province_name, area_code, iso_code, iso_3166_2 from dim_province_full where dt='2020-06-15' )p on o.province_id=p.id;

1.1.7、交易域用户粒度退单最近1日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_trade_user_order_refund_1d; CREATE EXTERNAL TABLE dws_trade_user_order_refund_1d ( `user_id` STRING COMMENT '用户id', `order_refund_count_1d` BIGINT COMMENT '最近1日退单次数', `order_refund_num_1d` BIGINT COMMENT '最近1日退单商品件数', `order_refund_amount_1d` DECIMAL(16, 2) COMMENT '最近1日退单金额' ) COMMENT '交易域用户粒度退单最近1日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_order_refund_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载（首日/每日）

set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_user_order_refund_1d partition(dt) select user_id, count(*) order_refund_count, sum(refund_num) order_refund_num, sum(refund_amount) order_refund_amount, dt from dwd_trade_order_refund_inc group by user_id,dt;

insert overwrite table dws_trade_user_order_refund_1d partition(dt='2020-06-15') select user_id, count(*), sum(refund_num), sum(refund_amount) from dwd_trade_order_refund_inc where dt='2020-06-15' group by user_id;

1.1.8、流量域会话粒度页面浏览最近1日汇总表

1）建表语句

同样这里除了会话 id 还做了一些维度退化：

DROP TABLE IF EXISTS dws_traffic_session_page_view_1d; CREATE EXTERNAL TABLE dws_traffic_session_page_view_1d ( `session_id` STRING COMMENT '会话id', `mid_id` string comment '设备id', `brand` string comment '手机品牌', `model` string comment '手机型号', `operate_system` string comment '操作系统', `version_code` string comment 'app版本号', `channel` string comment '渠道', `during_time_1d` BIGINT COMMENT '最近1日访问时长', `page_count_1d` BIGINT COMMENT '最近1日访问页面数' ) COMMENT '流量域会话粒度页面浏览最近1日汇总表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_traffic_session_page_view_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

没有首日每日的区分，因为流量域的数据都来自日志，而日志没有历史数据。

insert overwrite table dws_traffic_session_page_view_1d partition(dt='2020-06-14') select session_id, mid_id, brand, model, operate_system, version_code, channel, sum(during_time), count(*) from dwd_traffic_page_view_inc where dt='2020-06-14' group by session_id,mid_id,brand,model,operate_system,version_code,channel;

1.1.9、流量域访客页面粒度页面浏览最近1日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_traffic_page_visitor_page_view_1d; CREATE EXTERNAL TABLE dws_traffic_page_visitor_page_view_1d ( `mid_id` STRING COMMENT '访客id', `brand` string comment '手机品牌', `model` string comment '手机型号', `operate_system` string comment '操作系统', `page_id` STRING COMMENT '页面id', `during_time_1d` BIGINT COMMENT '最近1日浏览时长', `view_count_1d` BIGINT COMMENT '最近1日访问次数' ) COMMENT '流量域访客页面粒度页面浏览最近1日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_traffic_page_visitor_page_view_1d' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dws_traffic_page_visitor_page_view_1d partition(dt='2020-06-14') select mid_id, brand, model, operate_system, page_id, sum(during_time), count(*) from dwd_traffic_page_view_inc where dt='2020-06-14' group by mid_id,brand,model,operate_system,page_id;

2.1、 最近n日汇总表

2.1.1、交易域用户商品粒度订单最近n日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_trade_user_sku_order_nd; CREATE EXTERNAL TABLE dws_trade_user_sku_order_nd ( `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_7d` STRING COMMENT '最近7日下单次数', `order_num_7d` BIGINT COMMENT '最近7日下单件数', `order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额', `activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日活动优惠金额', `coupon_reduce_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日下单原始金额', `activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日活动优惠金额', `coupon_reduce_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 '/warehouse/gmall/dws/dws_trade_user_sku_order_nd' TBLPROPERTIES ('orc.compress' = 'snappy'); 

2）数据装载

        这里并不需要区分首日和每日。

insert overwrite table dws_trade_user_sku_order_nd partition(dt='2020-06-14') select user_id, sku_id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, sum(if(dt>=date_add('2020-06-14',-6),order_count_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_num_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_original_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),activity_reduce_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),coupon_reduce_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_total_amount_1d,0)), sum(order_count_1d), sum(order_num_1d), sum(order_original_amount_1d), sum(activity_reduce_amount_1d), sum(coupon_reduce_amount_1d), sum(order_total_amount_1d) from dws_trade_user_sku_order_1d where dt>=date_add('2020-06-14',-29) group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name; 

        这里我们 group by 的字段有很多，但其实并不影响。因为 group by 影响的是粒度，但是这里 group by  的字段都是商品的一些维度，所以不管 group by user_id,sku_id 还是 group by user_id,sku_id 和其它商品维度，最终的粒度依然是用户商品粒度。

2.1.2、交易域用户商品粒度退单最近n日汇总表

1）建表语句

每行代表：每个人每个最近7/30天某个商品退单的次数、件数。

列由维度和统计值组成。

DROP TABLE IF EXISTS dws_trade_user_sku_order_refund_nd; CREATE EXTERNAL TABLE dws_trade_user_sku_order_refund_nd ( `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_refund_count_7d` BIGINT COMMENT '最近7日退单次数', `order_refund_num_7d` BIGINT COMMENT '最近7日退单件数', `order_refund_amount_7d` DECIMAL(16, 2) COMMENT '最近7日退单金额', `order_refund_count_30d` BIGINT COMMENT '最近30日退单次数', `order_refund_num_30d` BIGINT COMMENT '最近30日退单件数', `order_refund_amount_30d` DECIMAL(16, 2) COMMENT '最近30日退单金额' ) COMMENT '交易域用户商品粒度退单最近n日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_refund_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

找到与之相对的 1d 表，拿 30 哥分区过滤一下，按照用户和商品（所有商品属性，因为这些商品属性只对应一个商品，不用担心 group by 会破坏粒度）分组。

insert overwrite table dws_trade_user_sku_order_refund_nd partition(dt='2020-06-14') select user_id, sku_id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, sum(if(dt>=date_add('2020-06-14',-6),order_refund_count_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_refund_num_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_refund_amount_1d,0)), sum(order_refund_count_1d), sum(order_refund_num_1d), sum(order_refund_amount_1d) from dws_trade_user_sku_order_refund_1d where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name;

2.1.3、交易域用户粒度订单最近n日汇总表

1）建表语句

行：每个用户最近7/30天的下单信息（次数、件数和总额等）

DROP TABLE IF EXISTS dws_trade_user_order_nd; CREATE EXTERNAL TABLE dws_trade_user_order_nd ( `user_id` STRING COMMENT '用户id', `order_count_7d` BIGINT COMMENT '最近7日下单次数', `order_num_7d` BIGINT COMMENT '最近7日下单商品件数', `order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额', `activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单活动优惠金额', `coupon_reduce_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日下单原始金额', `activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单活动优惠金额', `coupon_reduce_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 '/warehouse/gmall/dws/dws_trade_user_order_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

上面的 1d 表我们对 order_id 进行了去重，因为一个订单多个商品，会在 dwd_order_detail 重罚产生多条记录。但是这里的 nd 表并不需要去重，因为一个订单不可能多次下单。

insert overwrite table dws_trade_user_order_nd partition(dt='2020-06-14') select user_id, sum(if(dt>=date_add('2020-06-14',-6),order_count_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_num_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_original_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),activity_reduce_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),coupon_reduce_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_total_amount_1d,0)), sum(order_count_1d), sum(order_num_1d), sum(order_original_amount_1d), sum(activity_reduce_amount_1d), sum(coupon_reduce_amount_1d), sum(order_total_amount_1d) from dws_trade_user_order_1d where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' group by user_id;

 2.1.4、交易域用户粒度加购最近n日汇总表

        其实这张表是没有必要建的，因为它只有一个派生指标，没有和它有相同的业务过程、统计周期和统计粒度的其它派生指标。

        但是如果 ADS 层指标足够多的话，这张表的复用性还是比较高的。

1）建表语句

DROP TABLE IF EXISTS dws_trade_user_cart_add_nd; CREATE EXTERNAL TABLE dws_trade_user_cart_add_nd ( `user_id` STRING COMMENT '用户id', `cart_add_count_7d` BIGINT COMMENT '最近7日加购次数', `cart_add_num_7d` BIGINT COMMENT '最近7日加购商品件数', `cart_add_count_30d` BIGINT COMMENT '最近30日加购次数', `cart_add_num_30d` BIGINT COMMENT '最近30日加购商品件数' ) COMMENT '交易域用户粒度加购最近n日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_cart_add_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_trade_user_cart_add_nd partition(dt='2020-06-14') select user_id, sum(if(dt>=date_add('2020-06-14',-6),cart_add_count_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),cart_add_num_1d,0)), sum(cart_add_count_1d), sum(cart_add_num_1d) from dws_trade_user_cart_add_1d where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' group by user_id;

2.1.5、交易域用户粒度支付最近n日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_trade_user_payment_nd; CREATE EXTERNAL TABLE dws_trade_user_payment_nd ( `user_id` STRING COMMENT '用户id', `payment_count_7d` BIGINT COMMENT '最近7日支付次数', `payment_num_7d` BIGINT COMMENT '最近7日支付商品件数', `payment_amount_7d` DECIMAL(16, 2) COMMENT '最近7日支付金额', `payment_count_30d` BIGINT COMMENT '最近30日支付次数', `payment_num_30d` BIGINT COMMENT '最近30日支付商品件数', `payment_amount_30d` DECIMAL(16, 2) COMMENT '最近30日支付金额' ) COMMENT '交易域用户粒度支付最近n日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_payment_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dws_trade_user_payment_nd partition (dt = '2020-06-14') select user_id, sum(if(dt >= date_add('2020-06-14', -6), payment_count_1d, 0)), sum(if(dt >= date_add('2020-06-14', -6), payment_num_1d, 0)), sum(if(dt >= date_add('2020-06-14', -6), payment_amount_1d, 0)), sum(payment_count_1d), sum(payment_num_1d), sum(payment_amount_1d) from dws_trade_user_payment_1d where dt >= date_add('2020-06-14', -29) and dt <= '2020-06-14' group by user_id;

2.1.6、交易域省份粒度订单最近n日汇总表

1）建表语句

这里对省份信息做了一个维度退化：

DROP TABLE IF EXISTS dws_trade_province_order_nd; CREATE EXTERNAL TABLE dws_trade_province_order_nd ( `province_id` STRING COMMENT '用户id', `province_name` STRING COMMENT '省份名称', `area_code` STRING COMMENT '地区编码', `iso_code` STRING COMMENT '旧版ISO-3166-2编码', `iso_3166_2` STRING COMMENT '新版版ISO-3166-2编码', `order_count_7d` BIGINT COMMENT '最近7日下单次数', `order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额', `activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单活动优惠金额', `coupon_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单优惠券优惠金额', `order_total_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单最终金额', `order_count_30d` BIGINT COMMENT '最近30日下单次数', `order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单原始金额', `activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单活动优惠金额', `coupon_reduce_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 '/warehouse/gmall/dws/dws_trade_province_order_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dws_trade_province_order_nd partition(dt='2020-06-14') select province_id, province_name, area_code, iso_code, iso_3166_2, sum(if(dt>=date_add('2020-06-14',-6),order_count_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_original_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),activity_reduce_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),coupon_reduce_amount_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_total_amount_1d,0)), sum(order_count_1d), sum(order_original_amount_1d), sum(activity_reduce_amount_1d), sum(coupon_reduce_amount_1d), sum(order_total_amount_1d) from dws_trade_province_order_1d where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' group by province_id,province_name,area_code,iso_code,iso_3166_2;

2.1.7、交易域优惠券粒度订单最近n日汇总表

        交易域优惠券粒度订单最近n日汇总表和交易域活动粒度订单最近 n 日汇总表，这两张表都没有 1d 表，因为 ADS 层没有对应最近 1 日的需求。

        因为它俩没有 1d 表，所以我们的数据需要取自 dwd 层。

1）建表语句

DROP TABLE IF EXISTS dws_trade_coupon_order_nd; CREATE EXTERNAL TABLE dws_trade_coupon_order_nd ( `coupon_id` STRING COMMENT '优惠券id', `coupon_name` STRING COMMENT '优惠券名称', `coupon_type_code` STRING COMMENT '优惠券类型id', `coupon_type_name` STRING COMMENT '优惠券类型名称', `coupon_rule` STRING COMMENT '优惠券规则', `start_date` STRING COMMENT '发布日期', `original_amount_30d` DECIMAL(16, 2) COMMENT '使用下单原始金额', `coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '使用下单优惠金额' ) COMMENT '交易域优惠券粒度订单最近n日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_coupon_order_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dws_trade_coupon_order_nd partition(dt='2020-06-14') select id, coupon_name, coupon_type_code, coupon_type_name, benefit_rule, start_date, sum(split_original_amount), sum(split_coupon_amount) from ( select id, coupon_name, coupon_type_code, coupon_type_name, benefit_rule, date_format(start_time,'yyyy-MM-dd') start_date from dim_coupon_full where dt='2020-06-14' and date_format(start_time,'yyyy-MM-dd')>=date_add('2020-06-14',-29) )cou left join ( select coupon_id, order_id, split_original_amount, split_coupon_amount from dwd_trade_order_detail_inc where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' and coupon_id is not null )od on cou.id=od.coupon_id group by id,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date;

2.1.8、交易域活动粒度订单最近n日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_trade_activity_order_nd; CREATE EXTERNAL TABLE dws_trade_activity_order_nd ( `activity_id` STRING COMMENT '活动id', `activity_name` STRING COMMENT '活动名称', `activity_type_code` STRING COMMENT '活动类型编码', `activity_type_name` STRING COMMENT '活动类型名称', `start_date` STRING COMMENT '发布日期', `original_amount_30d` DECIMAL(16, 2) COMMENT '参与活动订单原始金额', `activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '参与活动订单优惠金额' ) COMMENT '交易域活动粒度订单最近n日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_activity_order_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dws_trade_activity_order_nd partition(dt='2020-06-14') select act.activity_id, activity_name, activity_type_code, activity_type_name, date_format(start_time,'yyyy-MM-dd'), sum(split_original_amount), sum(split_activity_amount) from ( select activity_id, activity_name, activity_type_code, activity_type_name, start_time from dim_activity_full where dt='2020-06-14' and date_format(start_time,'yyyy-MM-dd')>=date_add('2020-06-14',-29) group by activity_id, activity_name, activity_type_code, activity_type_name,start_time )act left join ( select activity_id, order_id, split_original_amount, split_activity_amount from dwd_trade_order_detail_inc where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' and activity_id is not null )od on act.activity_id=od.activity_id group by act.activity_id,activity_name,activity_type_code,activity_type_name,start_time;

2.1.9、交易域用户粒度退单最近n日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_trade_user_order_refund_nd; CREATE EXTERNAL TABLE dws_trade_user_order_refund_nd ( `user_id` STRING COMMENT '用户id', `order_refund_count_7d` BIGINT COMMENT '最近7日退单次数', `order_refund_num_7d` BIGINT COMMENT '最近7日退单商品件数', `order_refund_amount_7d` DECIMAL(16, 2) COMMENT '最近7日退单金额', `order_refund_count_30d` BIGINT COMMENT '最近30日退单次数', `order_refund_num_30d` BIGINT COMMENT '最近30日退单商品件数', `order_refund_amount_30d` DECIMAL(16, 2) COMMENT '最近30日退单金额' ) COMMENT '交易域用户粒度退单最近n日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_order_refund_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dws_trade_user_order_refund_nd partition(dt='2020-06-14') select user_id, sum(if(dt>=date_add('2020-06-14',-6),order_refund_count_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_refund_num_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),order_refund_amount_1d,0)), sum(order_refund_count_1d), sum(order_refund_num_1d), sum(order_refund_amount_1d) from dws_trade_user_order_refund_1d where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' group by user_id;

2.1.10、流量域访客页面粒度页面浏览最近n日汇总表

1）建表语句

DROP TABLE IF EXISTS dws_traffic_page_visitor_page_view_nd; CREATE EXTERNAL TABLE dws_traffic_page_visitor_page_view_nd ( `mid_id` STRING COMMENT '访客id', `brand` string comment '手机品牌', `model` string comment '手机型号', `operate_system` string comment '操作系统', `page_id` STRING COMMENT '页面id', `during_time_7d` BIGINT COMMENT '最近7日浏览时长', `view_count_7d` BIGINT COMMENT '最近7日访问次数', `during_time_30d` BIGINT COMMENT '最近30日浏览时长', `view_count_30d` BIGINT COMMENT '最近30日访问次数' ) COMMENT '流量域访客页面粒度页面浏览最近n日汇总事实表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_traffic_page_visitor_page_view_nd' TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dws_traffic_page_visitor_page_view_nd partition(dt='2020-06-14') select mid_id, brand, model, operate_system, page_id, sum(if(dt>=date_add('2020-06-14',-6),during_time_1d,0)), sum(if(dt>=date_add('2020-06-14',-6),view_count_1d,0)), sum(during_time_1d), sum(view_count_1d) from dws_traffic_page_visitor_page_view_1d where dt>=date_add('2020-06-14',-29) and dt<='2020-06-14' group by mid_id,brand,model,operate_system,page_id;

总结 

       至此，DWS 层搭建完毕，DWS 和 ADS 层都是需求驱动的，这一点现在深有体会。接下来就是 ADS 层的开发了。