数据仓库设计
数据仓库分层规划
优秀可靠的数仓体系，需要良好的数据分层结构。合理的分层，能够使数据体系更加清晰，使复杂问题得以简化。以下是该项目的分层规划。
数据仓库构建流程
数据调研
数据调研重点要做两项工作，分别是业务调研和需求分析。这两项工作做的是否充分，直接影响着数据仓库的质量。
1）业务调研
业务调研的主要目标是熟悉业务流程、熟悉业务数据。
熟悉业务流程要求做到，明确每个业务的具体流程，需要将该业务所包含的每个业务过程一一列举出来。
熟悉业务数据要求做到，将数据（包括埋点日志和业务数据表）与业务过程对应起来，明确每个业务过程会对哪些表的数据产生影响，以及产生什么影响。产生的影响，需要具体到，是新增一条数据，还是修改一条数据，并且需要明确新增的内容或者是修改的逻辑。
下面业务电商中的交易为例进行演示，交易业务涉及到的业务过程有买家下单、买家支付、卖家发货，买家收货，具体流程如下图。
明确数据域
数据仓库模型设计除横向的分层外，通常也需要根据业务情况进行纵向划分数据域。
划分数据域的意义是便于数据的管理和应用。
通常可以根据业务过程或者部门进行划分，本项目根据业务过程进行划分，需要注意的是一个业务过程只能属于一个数据域。
下面是本数仓项目所需的所有业务过程及数据域划分详情。
数据域 |
业务过程 |
交易域 |
加购、下单、取消订单、支付成功、退单、退款成功 |
流量域 |
页面浏览、启动应用、动作、曝光、错误 |
用户域 |
注册、登录 |
互动域 |
收藏、评价 |
工具域 |
优惠券领取、优惠券使用（下单）、优惠券使用（支付） |
构建业务总线矩阵
业务总线矩阵中包含维度模型所需的所有事实（业务过程）以及维度，以及各业务过程与各维度的关系。矩阵的行是一个个业务过程，矩阵的列是一个个的维度，行列的交点表示业务过程与维度的关系。
一个业务过程对应维度模型中一张事务型事实表，一个维度则对应维度模型中的一张维度表。所以构建业务总线矩阵的过程就是设计维度模型的过程。但是需要注意的是，总线矩阵中通常只包含事务型事实表，另外两种类型的事实表需单独设计。
按照事务型事实表的设计流程，选择业务过程à声明粒度à确认维度à确认事实，得到的最终的业务总线矩阵见以下表格。
明确统计指标
明确统计指标具体的工作是，深入分析需求，构建指标体系。构建指标体系的主要意义就是指标定义标准化。所有指标的定义，都必须遵循同一套标准，这样能有效的避免指标定义存在歧义，指标定义重复等问题。
1）指标体系相关概念
（1）原子指标
原子指标基于某一业务过程的度量值，是业务定义中不可再拆解的指标，原子指标的核心功能就是对指标的聚合逻辑进行了定义。我们可以得出结论，原子指标包含三要素，分别是业务过程、度量值和聚合逻辑。
例如订单总额就是一个典型的原子指标，其中的业务过程为用户下单、度量值为订单金额，聚合逻辑为sum()求和。需要注意的是原子指标只是用来辅助定义指标一个概念，通常不会对应有实际统计需求与之对应。
（2）派生指标
派生指标基于原子指标，其与原子指标的关系如下图所示。
与原子指标不同，派生指标通常会对应实际的统计需求。请从图中的例子中，体会指标定义标准化的含义。
（3）衍生指标
衍生指标是在一个或多个派生指标的基础上，通过各种逻辑运算复合而成的。例如比率、比例等类型的指标。衍生指标也会对应实际的统计需求。
维度模型设计
维度模型的设计参照上述得到的业务总线矩阵即可。事实表存储在DWD层，维度表存储在DIM层。
汇总模型设计
汇总模型的设计参考上述整理出的指标体系（主要是派生指标）即可。汇总表与派生指标的对应关系是，一张汇总表通常包含业务过程相同、统计周期相同、统计粒度相同的多个派生指标。请思考：汇总表与事实表的对应关系是？
报表数据导出
为方便报表应用使用数据，需将ads各指标的统计结果导出到MySQL数据库中。
MySQL建库建表
创建数据库
CREATE DATABASE IF NOT EXISTS gmall_report DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
创建表
1）各渠道流量统计
DROP TABLE IF EXISTS `ads_traffic_stats_by_channel`; CREATE TABLE `ads_traffic_stats_by_channel` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `channel` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '渠道', `uv_count` bigint(20) NULL DEFAULT NULL COMMENT '访客人数', `avg_duration_sec` bigint(20) NULL DEFAULT NULL COMMENT '会话平均停留时长，单位为秒', `avg_page_count` bigint(20) NULL DEFAULT NULL COMMENT '会话平均浏览页面数', `sv_count` bigint(20) NULL DEFAULT NULL COMMENT '会话数', `bounce_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '跳出率', PRIMARY KEY (`dt`, `recent_days`, `channel`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各渠道流量统计' ROW_FORMAT = DYNAMIC;
2）路径分析
DROP TABLE IF EXISTS `ads_page_path`; CREATE TABLE `ads_page_path` ( `dt` date NOT NULL COMMENT '统计日期', `source` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '跳转起始页面ID', `target` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '跳转终到页面ID', `path_count` bigint(20) NULL DEFAULT NULL COMMENT '跳转次数', PRIMARY KEY (`dt`, `source`, `target`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '页面浏览路径分析' ROW_FORMAT = DYNAMIC;
3）用户变动统计
DROP TABLE IF EXISTS `ads_user_change`; CREATE TABLE `ads_user_change` ( `dt` date NOT NULL COMMENT '统计日期', `user_churn_count` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '流失用户数', `user_back_count` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '回流用户数', PRIMARY KEY (`dt`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户变动统计' ROW_FORMAT = DYNAMIC;
4）用户留存率
DROP TABLE IF EXISTS `ads_user_retention`; CREATE TABLE `ads_user_retention` ( `dt` date NOT NULL COMMENT '统计日期', `create_date` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户新增日期', `retention_day` int(20) NOT NULL COMMENT '截至当前日期留存天数', `retention_count` bigint(20) NULL DEFAULT NULL COMMENT '留存用户数量', `new_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增用户数量', `retention_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '留存率', PRIMARY KEY (`dt`, `create_date`, `retention_day`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户留存率' ROW_FORMAT = DYNAMIC;
5）用户新增活跃统计
DROP TABLE IF EXISTS `ads_user_stats`; CREATE TABLE `ads_user_stats` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近n日,1:最近1日,7:最近7日,30:最近30日', `new_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增用户数', `active_user_count` bigint(20) NULL DEFAULT NULL COMMENT '活跃用户数', PRIMARY KEY (`dt`, `recent_days`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户新增活跃统计' ROW_FORMAT = DYNAMIC;
6）用户行为漏斗分析
DROP TABLE IF EXISTS `ads_user_action`; CREATE TABLE `ads_user_action` ( `dt` date NOT NULL COMMENT '统计日期', `home_count` bigint(20) NULL DEFAULT NULL COMMENT '浏览首页人数', `good_detail_count` bigint(20) NULL DEFAULT NULL COMMENT '浏览商品详情页人数', `cart_count` bigint(20) NULL DEFAULT NULL COMMENT '加购人数', `order_count` bigint(20) NULL DEFAULT NULL COMMENT '下单人数', `payment_count` bigint(20) NULL DEFAULT NULL COMMENT '支付人数', PRIMARY KEY (`dt`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户行为漏斗分析' ROW_FORMAT = DYNAMIC;
7）新增下单用户统计
DROP TABLE IF EXISTS `ads_new_order_user_stats`; CREATE TABLE `ads_new_order_user_stats` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近n日,1:最近1日,7:最近7日,30:最近30日', `new_order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增下单用户数', PRIMARY KEY (`recent_days`, `dt`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '新增下单用户统计' ROW_FORMAT = Dynamic;
8）最近7日内连续3日下单用户数
DROP TABLE IF EXISTS `ads_order_continuously_user_count`; CREATE TABLE `ads_order_continuously_user_count` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近天数,7:最近7天', `order_continuously_user_count` bigint(20) NULL DEFAULT NULL COMMENT '连续3日下单用户数', PRIMARY KEY (`dt`, `recent_days`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '最近7日内连续3日下单用户数统计' ROW_FORMAT = Dynamic;
9）最近30日各品牌复购率
DROP TABLE IF EXISTS `ads_repeat_purchase_by_tm`; CREATE TABLE `ads_repeat_purchase_by_tm` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近天数,30:最近30天', `tm_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '品牌ID', `tm_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品牌名称', `order_repeat_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '复购率', PRIMARY KEY (`dt`, `recent_days`, `tm_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '最近30日各品牌复购率统计' ROW_FORMAT = DYNAMIC;
10）各品牌商品下单统计
DROP TABLE IF EXISTS `ads_order_stats_by_tm`; CREATE TABLE `ads_order_stats_by_tm` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `tm_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '品牌ID', `tm_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品牌名称', `order_count` bigint(20) NULL DEFAULT NULL COMMENT '下单数', `order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '下单人数', PRIMARY KEY (`dt`, `recent_days`, `tm_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各品牌商品下单统计' ROW_FORMAT = DYNAMIC;
11）各品类商品下单统计
DROP TABLE IF EXISTS `ads_order_stats_by_cate`; CREATE TABLE `ads_order_stats_by_cate` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `category1_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '一级品类ID', `category1_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '一级品类名称', `category2_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '二级品类ID', `category2_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二级品类名称', `category3_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '三级品类ID', `category3_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '三级品类名称', `order_count` bigint(20) NULL DEFAULT NULL COMMENT '下单数', `order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '下单人数', PRIMARY KEY (`dt`, `recent_days`, `category1_id`, `category2_id`, `category3_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各品类商品下单统计' ROW_FORMAT = DYNAMIC;
12）各品类商品购物车存量Top3
DROP TABLE IF EXISTS `ads_sku_cart_num_top3_by_cate`; CREATE TABLE `ads_sku_cart_num_top3_by_cate` ( `dt` date NOT NULL COMMENT '统计日期', `category1_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '一级品类ID', `category1_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '一级品类名称', `category2_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '二级品类ID', `category2_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二级品类名称', `category3_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '三级品类ID', `category3_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '三级品类名称', `sku_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'SKU_ID', `sku_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'SKU名称', `cart_num` bigint(20) NULL DEFAULT NULL COMMENT '购物车中商品数量', `rk` bigint(20) NULL DEFAULT NULL COMMENT '排名', PRIMARY KEY (`dt`, `sku_id`, `category1_id`, `category2_id`, `category3_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各品类商品购物车存量Top3' ROW_FORMAT = DYNAMIC;
13）各品牌商品收藏次数Top3
DROP TABLE IF EXISTS `ads_sku_favor_count_top3_by_tm`; CREATE TABLE `ads_sku_favor_count_top3_by_tm` ( `dt` date NOT NULL COMMENT '统计日期', `tm_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '品牌ID', `tm_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品牌名称', `sku_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'SKU_ID', `sku_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'SKU名称', `favor_count` bigint(20) NULL DEFAULT NULL COMMENT '被收藏次数', `rk` bigint(20) NULL DEFAULT NULL COMMENT '排名', PRIMARY KEY (`dt`, `tm_id`, `sku_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各品牌商品收藏次数Top3' ROW_FORMAT = Dynamic;
14）下单到支付时间间隔平均值
DROP TABLE IF EXISTS `ads_order_to_pay_interval_avg`; CREATE TABLE `ads_order_to_pay_interval_avg` ( `dt` date NOT NULL COMMENT '统计日期', `order_to_pay_interval_avg` bigint(20) NULL DEFAULT NULL COMMENT '下单到支付时间间隔平均值', PRIMARY KEY (`dt`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT '下单到支付时间间隔平均值统计' ROW_FORMAT = Dynamic;
15）各省份交易统计
DROP TABLE IF EXISTS `ads_order_by_province`; CREATE TABLE `ads_order_by_province` ( `dt` date NOT NULL COMMENT '统计日期', `recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `province_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '省份ID', `province_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '省份名称', `area_code` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地区编码', `iso_code` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '旧版国际标准地区编码', `iso_code_3166_2` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '新版国际标准地区编码', `order_count` bigint(20) NULL DEFAULT NULL COMMENT '订单数', `order_total_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单金额', PRIMARY KEY (`dt`, `recent_days`, `province_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各省份交易统计' ROW_FORMAT = DYNAMIC;
16）优惠券使用统计
DROP TABLE IF EXISTS `ads_coupon_stats`; CREATE TABLE `ads_coupon_stats` ( `dt` date NOT NULL COMMENT '统计日期', `coupon_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '优惠券ID', `coupon_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '优惠券名称', `used_count` bigint(20) NULL DEFAULT NULL COMMENT '使用次数', `used_user_count` bigint(20) NULL DEFAULT NULL COMMENT '使用人数', PRIMARY KEY (`dt`, `coupon_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT '优惠券使用统计' ROW_FORMAT = Dynamic;
