数仓事实表设计

事实表设计

数仓里面的事实表具体分为两大类:
明细事实表
聚合事实表

明细事实表 (dwd)

明细事实表:
    事实表有粒度大小之分，基于数据仓库层次架构，明细事实表一般存在于dwd层，该层事实表设计
不进行聚合、汇总操作，仅做数据规范化、数据降维动作，同时数据保持业务事务粒度，确保数据信息
无丢失。
    
数据降维: (利用数据冗余来达到降维的目的，提高任务的执行效率)
    为了提升模型易用性，将常规维度表中的常用的属性数据冗余到相应的事实表中，从而在使用的时候
    避免维表关联的方式，即为数据降维。
    
事实表中不一定有事实
    一般将事实表中包含两部分信息: 维度、度量， 度量即为事实，但有些特殊情况下，事实表中无度量
信息，只是记录一个实际业务动作。
    比如: 信息审核表: 申请人id, 状态，审核人id, 审核时间

设计事实表

    设计事实表的主要依据是业务过程，之前说过，每一个业务动作事件，都可以作为一个事实，那么在一个
订单处理过程中，会有多个动作，这个过程中的事实表怎么设计呢？
    方案一: 单个事实表
        对于每一个业务动作事件，设计一个事实表，仅记录该事件的事实以及状态
    方案二: 流程事实表
        对于一个业务流程主体，设计一个事实表，跟踪整个流程的事实以及状态转换

单事件事实表和流程事实表的优缺点分析

单事件事实表: 
    更方便跟踪业务流程细节数据，针对特殊的业务分析场景比较灵活和方便，数据处理上也更加灵活；不方便
的地方就是数仓中需要管理太多的事实表，同时跟踪业务流转不够直观。

流程事实表:
    能够更直观的跟踪业务流转和当前状态，流程事实表中，方便大部分的通用分析应用场景，由于和业务侧的
数据模型设计思路一致，也是目前最常用的事实表设计；但是细节数据跟踪不到位，特殊场景的分析不够灵活。

两种表的设计区别在于对业务流程的拆分思路不同，具体选择事实表的构建思路，需要根据实际的业务确定，一般
建议两者结合。

事实表设计方案

增量存储:
    即每周期仅处理增量部分的数据，针对状态无变化的数据比较适合。
    
全量快照:
    状态有变化，但每天保存当前的快照数据，对于数据量在可控范围内的情况下可以采用。
    保存策略: 
        1、如果存储空间和成本可接受，完整存储，确保能够追溯到历史每天数据状态
        2、存储空间有限，考虑移动历史快照数据到冷盘，需要使用的时候可恢复
        3、数据历史状态数据无太大价值，可以考虑部分删除，比如仅保留每月最后一天的快照数据

拉链:
    数据量大，但缓慢变化，需要跟踪历史状态，和缓慢变化维类似
    credit_amount: card_id, user_id, amount, used_amount, created_time, updated_time
    
    -- 1、采集数据----> s_credit_amount (得到mysql数据变化表，新增的和更新后的)
    -- query "select col_name from ... where updated_time>='2019-01-02 00:00:00'" 
    
    -- 2、初始化拉链表，对导入的credit_amount添加start_date(当天), end_date("9999-12-31")
    
    -- 3、dwd拉链表: d_credit_amount_1    ----- 2019-01-01
    
    -- 4、拉链临时表
    drop table if exists tmp_credit_amount;
    create table tmp_credit_amount as 
    select * 
        from 
        (select ta.card_id
                ,ta.user_id
                ,ta.amount
                ,ta.used_amount
                ,ta.created_time
                ,ta.updated_time
                ,ta.start_date
                ,(case when tb.card_id is not null and ta.end_date>'2019-01-02' then '2019-01-01' 
                       else ta.end_date end) as end_date 
            from d_credit_amount_1 as ta 
        left join 
             s_credit_amount as tb 
             on ta.card_id = tb.card_id 
        union all 
        select card_id
              ,user_id
              ,amount
              ,used_amount
              ,created_time
              ,updated_time
              ,'2019-01-02' as start_date
              ,'9999-12-31' as end_date
            from 
            s_credit_amount) tmp
            
    -- 5、把临时表覆盖拉链表
    insert overwrite into d_credit_amount_1 
        select * from tmp_credit_amount;

聚合事实表 (dws)

    相对于明细事实表，聚合事实表通常是在明细事实表的基础上，按照一定的粒度粗细进行的汇总、聚合操作，
它的粒度较明细数据粒度粗，同时伴随着细节信息的丢失；在数仓层次结构中，通常位于dws层，一般作为通用
汇总数据存在，也可以是更高粒度的指标数据, 但同一个事实表中，尽可能保证事实粒度一致。
    1、日粒度
    2、周期性积累
    3、历史累积
    
1、可累加事实
    在一定粒度范围内，可累加的事实度量，比如: 订单金额，订单数
2、不可累加事实
    通常情况下，在更高粒度上不可累加的事实，比如通过率、转化率，下单用户数
        
    通常情况下，比率这种不可累积的事实，建议拆分存储，比如通过率拆分为通过数，申请数，由细粒度数据去重
计算而来事实，正常存储，但是更粗粒度事实是不可直接使用。(例如我们的三率统计)

聚合事实表类别

数据仓库中，按照日期范围的不同，通常包括以下类别的聚合事实表
1、公共维度层-通用汇总
    应对大部分可预期的、常规的数据需求，通常针对模式相对稳定的分析、BI指标计算、特征提取等场景，封装部分业务
    处理、计算逻辑，尽量避免用户直接使用底层明细数据，该层用到的数据范围比较广泛。
    
2、日粒度
    主要应对模式稳定的分析、BI日报、特征提取等场景，同时日粒度也为后续累积计算提供粗粒度的底层，数据范围一般为
    上一日的数据。

3、周期性累积
    主要应对明确的周期性分析、BI周期性报表，数据范围一般在某周期内的。
    
4、历史累积
    顾名思义，历史以来某一特定数据的累积，通常在用户画像、经营分析、特征提取方面场景较多，涉及数据范围比较广泛，
    通常是计算耗时较长的一部分，比如某门店累积营业额、某用户累积利润贡献、用户首次下单时间(非可度量、描述性)。  

案例:

三个公共维度层-通用汇总

订单聚合事实表

	fact_订单_s_d
PK	订单id
	用户id
	司机id
	汽车品牌
	性别
	年龄
	下单时间
	用户等待接单时长
	用户等待司机时长
	司乘距离
	预估时长
	预估路程
	实际时长
	实际路程
	直线距离
	应付金额
	实付金额
	优惠券减免
	新用户减免
	。。。
	。。。

用户聚合事实表

	fact_用户_s_d
PK	用户id
	性别
	年龄
	下单笔数
	接单笔数
	主动取消笔数
	被取消
	支付笔数
	行程
	优惠
	消极评价笔数
	中性评价笔数
	积极评价笔数
	投诉笔数
	。。。
	。。。

司机聚合事实表

	fact_司机_s_d
PK	司机id
	品牌
	车系
	出车时长
	做单时长
	行驶里程
	做单金额
	做单收入
	奖励收入
	被投诉
	投诉
	取消
	被取消
	。。。

日粒度汇总

日粒度聚合汇总明细表

	fact_txn_d
PK,PK1	区域id
PK,PK2	时间id
	下单用户数
	下单数
	支付用户数
	支付单数
	应收金额
	实付金额
	优惠金额
	里程
	接单
	乘客取消
	司机取消
	。。。

区域维度表

	dim_区域
PK	区域id
	。。。

时间维度表

	dim_时间
PK	时间id
	。。。

周期性累积汇总

	fact_用户_w
PK	周次id
	注册用户数
	下单用户数
	首次下单用户数
	取消订单用户数
	支付用户数
	被投诉用户数
	。。。