公共汇总事实表的设计原则和规范_大数据开发治理平台 DataWorks(DataWorks)-阿里云帮助中心

公共汇总粒度事实层以分析的主题对象为建模驱动，根据上层应用和产品的指标需求构建公共粒度的汇总指标事实表。每个公共汇总层的表通常最少包含一个派生指标，这些派生指标通过计算和组合基础指标生成，旨在提供更深层次的数据洞察和分析支持。本文为您详细讲解公共汇总事实表的设计原则和表命名规范。

公共汇总事实表设计原则

聚集是指对原始明细数据进行汇总。DWS公共汇总层是面向分析对象的主题聚集建模。在本教程中，最终的分析目标包括：

最近一天某个类目（例如：厨具）商品在各省的销售总额。
该类目Top 10销售额商品名称。
各省用户购买力分布。

因此，我们可以从最终交易成功的商品、类目、买家等角度对最近一天的数据进行汇总。

重要

聚集是指对原始星型模型的数据进行汇总，而不跨越不同的事实表。聚集的维度和度量必须与原始模型保持一致，以确保查询结果与原始模型一致。
聚集可以显著提升查询性能，但也会增加ETL维护的难度。当子类目对应的一级类目发生变更时，先前已经汇总到聚集表中的数据需要重新调整。

此外，进行DWS层设计时需要遵循以下原则：

数据公用性：在决定是否将汇总的聚集提供给第三方使用时，需要考虑基于某个维度的聚集是否经常用于数据分析。如果这些数据经常用于数据分析，就可以将明细数据汇总并沉淀到聚集表中。
不跨数据域：数据域是在较高层次上对数据进行分类和抽象的集合。通常，数据域按照业务过程进行分类，例如将交易相关的数据归类到交易域下，商品的新增和修改操作则归类到商品域下。
区分统计周期：在表的命名上要能说明数据的统计周期，例如_1d表示最近1天，_td表示截至当天，_nd表示最近N天。

公共汇总事实表规范

公共汇总事实表命名规范：dws_{业务板块缩写/pub}_{数据域缩写}_{数据粒度缩写}[_{自定义表命名标签缩写}]_{统计时间周期范围缩写}。

关于统计周期范围的缩写，默认情况下，离线计算应包括最近一天（_1d）、最近N天（_nd）和历史截止当天（_td）三个表，如果 _nd 表的字段过多需要拆分时，只允许以一个统计周期单元作为原子单位进行拆分，例如最近7天（_1w）拆分一个表，不允许一个表存储多个统计周期的数据。
对于小时表（无论是天刷新还是小时刷新），都用_hh来表示。
对于分钟表（无论是天刷新还是小时刷新），都用_mm来表示。

具体的表命名可参考以下内容：

示例表名	表说明
dws_asale_trd_byr_subpay_1d	买家交易分阶段付款的日汇总表（1日维度）。
dws_asale_trd_byr_subpay_td	买家分阶段付款截至当日汇总表（当日维度）。
dws_asale_trd_byr_cod_nd	买家货到付款交易汇总事实表（多日维度）。
dws_asale_itm_slr_td	卖家商品截至当日存量汇总表（当日维度）。
dws_asale_itm_slr_hh	卖家商品小时汇总表（小时维度）。
dws_asale_itm_slr_mm	卖家商品分钟汇总表（分钟维度）。

DWS层数据存储及生命周期管理规范请参见CDM汇总层设计规范。

建表示例

满足业务需求的DWS层建表语句如下。

CREATE TABLE IF NOT EXISTS dws_asale_trd_byr_ord_1d
(
    buyer_id                BIGINT COMMENT '买家ID',
    buyer_nick              STRING COMMENT '买家昵称',
    mord_prov               STRING COMMENT '收货人省份',
    cate_id                 BIGINT COMMENT '商品类目ID',
    cate_name               STRING COMMENT '商品类目名称',
    confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天订单已经确认收货的金额总和'
)
COMMENT '买家粒度所有交易最近一天汇总事实表'
PARTITIONED BY (ds STRING COMMENT '分区字段YYYYMMDD')
LIFECYCLE 36000;

CREATE TABLE IF NOT EXISTS dws_asale_trd_itm_ord_1d
(
    item_id                 BIGINT COMMENT '商品ID',
    item_title               STRING COMMENT '商品名称',
    cate_id                 BIGINT COMMENT '商品类目ID',
    cate_name               STRING COMMENT '商品类目名称',
    mord_prov               STRING COMMENT '收货人省份',
    confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天订单已经确认收货的金额总和'
)
COMMENT '商品粒度交易最近一天汇总事实表'
PARTITIONED BY (ds STRING COMMENT '分区字段YYYYMMDD')
LIFECYCLE 36000;