HAWQ 取代传统数仓实践（十二） —— 维度表技术之分段维度

目录

一、分段维度简介

二、销售订单分段维度

1. 年度销售订单星型模式

2. 初始装载

3. 定期装载

---

一、分段维度简介

        在客户维度中，最具有分析价值的属性就是各种分类，这些属性的变化范围比较大。对某个个体客户来说，可能的分类属性包括：性别、年龄、民族、职业、收入和状态，例如，新客户、活跃客户、不活跃客户、已流失客户等。在这些分类属性中，有一些能够定义成包含连续值的分段，例如年龄和收入这种数值型的属性，天然就可以分成连续的数值区间，而象状态这种描述性的属性，可能需要用户根据自己的实际业务仔细定义，通常定义的根据是某种可度量的数值。

        组织还可能使用为其客户打分的方法刻画客户行为。分段维度模型通常以不同方式按照积分将客户分类，例如，基于他们的购买行为、支付行为、流失走向等，每个客户用所得的分数标记。

        一个常用的客户评分及分析系统是考察客户行为的相关度（R）、频繁度（F）和强度（I），该方法被称为 RFI 方法。有时将强度替换为消费度（M），因此也被称为 RFM 度量。相关度是指客户上次购买或访问网站距今的天数。频繁度是指一段时间内客户购买或访问网站的次数，通常是指过去一年的情况。强度是指客户在某一固定时间周期中消费的总金额。在处理大型客户数据时，某个客户的行为可以按照如图1 所示的 RFI 多维数据仓库建模。在此图中，每个维度形成一条数轴，某个轴的积分度量值从 1 到 5，代表某个分组的实际值，三条数轴组合构成客户积分立方体，每个客户的积分都在这个立方体之中。

图1

        定义有意义的分组至关重要。应该由业务人员和数据仓库开发团队共同定义可能会利用的行为标识，更复杂的场景可能包含信用行为和回报情况，例如定义如下 8 个客户标识：

        A：活跃客户，信誉良好，产品回报多
        B：活跃客户，信誉良好，产品回报一般
        C：最近的新客户，尚未建立信誉等级
        D：偶尔出现的客户，信誉良好
        E：偶尔出现的客户，信誉不好
        F：以前的优秀客户，最近不常见
        G：只逛不买的客户，几乎没有效益
        H：其他客户

        至此可以考察客户时间序列数据，并将某个客户关联到报表期间的最近分类中。例如，某个客户在最近 10 个考察期间的情况可以表示为：CCCDDAAABB。这一行为时间序列标记来自于固定周期度量过程，观察值是文本类型的，不能计算或求平均值，但是它们可以被查询。例如，可以发现在以前的第 5 个、第 4 个或第 3 个周期中获得 A 且在第 2 个或第 1 个周期中获得 B 的所有客户。通过这样的进展分析还可以发现那些可能失去的有价值的客户，进而用于提高产品回报率。

        行为标记可能不会被当成普通事实存储，因为它虽然由事实表的度量所定义，但其本身不是度量值。行为标记的主要作用在于为前面描述的例子制定复杂的查询模式。推荐的处理行为标记的方法是为客户维度建立分段属性的时间序列，这样 BI 接口比较简单，因为列都在同一个表中，可以对它们建立时间戳索引，性能也较好。除了为每个行为标记时间周期建立不同的列，建立单一的包含多个连续行为标记的连接字符串，也是较好的一种方法，例如 CCCDDAAABB。该列支持通配符模糊搜索模式，例如，“D 后紧跟着 B ”可以简单实现为“where flag like '%DB%'”。

二、销售订单分段维度

        下面以销售订单为例，说明分段维度的实现技术。分段维度包含连续的分段度量值，例如，年度销售订单分段维度可能包含有叫做“低”、“中”、“高”的三个档次，各档定义分别为消费额在 0.01 到 3000、3000.01 到 6000.00、6000.01 到 99999999.99 区间。如果一个客户的年度销售订单金额累计为 1000，则被归为“低”档。分段维度可以存储多个分段集合，例如，可能有一个用于促销分析的分段集合，另一个用于市场细分，可能还有一个用于销售区域计划。分段一般由用户定义，而且很少能从源事务数据直接获得。

1. 年度销售订单星型模式

        为了实现年度订单分段维度，我们需要两个新的星型模式，如图2 所示。

图2

        第一个星型模式由 annual_sales_order_fact 事实表、customer_dim 维度表构成。年度销售额事实表存储客户一年的消费总额，数据从现有的销售订单事实表汇总而来。第二个星型模式由 annual_customer_segment_fact 事实表、annual_order_segement_dim 维度表、customer_dim 维度表构成。客户年度分段事实表中没有度量，只有来自两个相关维度表的代理键，因此它是一个无事实的事实表，存储的数据实际上就是前面所说的行为标记时间序列。年度订单分段维度表用于存储分段的定义，在本例中，它只与年度分段事实表有关系。

        如果多个分段的属性相同，可以将它们存储到单一维度表中，因为分段通常只有很小的基数。本例中 annual_order_segment_dim 表存储了“project”和“grid”两种分段集合，它们都是按照客户的年度销售订单金额将其分类。分段维度按消费金额的定义如表1 所示，project 分六段，grid 分三段。

分段类别	分段名称	开始值	结束值
Project	bottom	0.01	2500.00
Project	low	2500.01	3000.00
Project	mid-low	3000.01	4000.00
Project	mid	4000.00	5500.00
Project	mid-high	5500.01	6500.00
Project	top	6500.01	99999999.99
Grid	low	0.01	3000.00
Grid	mid	3000.01	6000.00
Grid	high	6000.01	99999999.99

表1

        每一分段有一个开始值和一个结束值，分段的粒度就是本段和下段之间的间隙。粒度必须是度量的最小可能值，在销售订单示例中，金额的最小值是 0.01。最后一个分段的结束值是销售订单金额可能的最大值。下面的脚本用于建立分段维度。

set search_path=tds;

-- 建立分段维度表   
create table annual_order_segment_dim (    
    segment_sk serial,   
    segment_name varchar(30),    
    band_name varchar(50),    
    band_start_amount numeric(10,2),  
    band_end_amount numeric(10,2),  
    isdelete boolean default false,
    version int default 1,  
    effective_date date default current_date 
);

-- 添加分段定义数据
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('project', 'bottom', 0.01, 2500.00); 
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('project', 'low', 2500.01, 3000.00);  
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('project', 'mid-low', 3000.01, 4000.00);  
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('project', 'mid', 4000.01, 5500.00);  
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('project', 'mid_high', 5500.01, 6500.00);  
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('project', 'top', 6500.01, 99999999.99);    
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('grid', 'low', 0.01, 3000);    
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('grid', 'med', 3000.01, 6000.00);  
insert into annual_order_segment_dim (segment_name, band_name, band_start_amount, band_end_amount)
values ('grid', 'high', 6000.01, 99999999.99);    
  
-- 建立分段维度当前视图
create or replace view v_annual_order_segment_dim_latest as     
select segment_sk,    
       segment_name,     
       band_name,    
       band_start_amount, 
       band_end_amount,	   
       version,    
       effective_date     
  from (select distinct on (segment_name, band_name) 
               segment_sk,
               segment_name, 
               band_name,
               band_start_amount, 
               band_end_amount,
               isdelete,
               version,    
               effective_date            
          from annual_order_segment_dim    
         order by segment_name, band_name, segment_sk desc) as latest     
  where isdelete is false;   

-- 建立分段维度历史视图
create or replace view v_annual_order_segment_dim_his as     
select *, date(lead(effective_date,1,date '2200-01-01') over (partition by segment_name, band_name order by effective_date)) expiry_date     
  from annual_order_segment_dim;  

-- 建立年度销售订单事实表  
create table annual_sales_order_fact (    
    customer_sk int,    
    year int,    
    annual_order_amount numeric(10,2)    
);   

-- 建立年度销售订单分段事实表    
create table annual_customer_segment_fact (    
    segment_sk int,    
    customer_sk int,    
    year int    
);

        上面的语句新建三个表，分别是分段维度表、年度销售事实表和年度客户消费分段事实表，并向分段维度表插入 9 条分段定义数据。假设分段维度表需要 SCD 处理，于是该表有删除标志、版本号、生效日期等附加属性，并建立了该表的当前视图和历史视图。

2. 初始装载

        执行下面的脚本初始装载分段相关数据。

insert into annual_sales_order_fact    
select customer_sk,   
       year_month/100,   
       sum(order_amount)    
  from sales_order_fact  
 where year_month/100 < 2017
 group by customer_sk, year_month/100;    
    
insert into annual_customer_segment_fact    
select d.segment_sk,   
       a.customer_sk,   
       a.year   
  from annual_sales_order_fact a,   
       v_annual_order_segment_dim_latest d    
 where annual_order_amount >= band_start_amount    
   and annual_order_amount <= band_end_amount;

        因为装载过程不能导入当年的数据，所以使用 year < 2017 过滤条件。这里是按客户代理键 customer_sk 分组求和来判断分段，实际情况可能是以 customer_number 进行分组的，因为无论客户的 SCD 属性如何变化，一般还是认为是一个客户。将年度销售事实表里与分段维度表关联，把客户、分段维度的代理键插入年度客户消费分段事实表。注意，数据装载过程中并没有引用客户维度表，因为客户代理键可以直接从销售订单事实表得到。分段定义中，每个分段结束值与下一分段的开始值是连续的，并且分段之间不存在数据重叠，所以装载分段事实表时，订单金额判断条件两端都使用闭区间。

        执行初始装载脚本后，使用下面的语句查询客户分段事实表，确认装载的数据是正确的。

select csk, y, amt, string_agg(sn||':'||bn,' / ')
  from (select a.customer_sk csk,  
               a.year y,  
               annual_order_amount amt,  
               segment_name sn,  
               band_name bn  
          from annual_customer_segment_fact a,  
               v_annual_order_segment_dim_latest b,   
               annual_sales_order_fact c  
         where a.segment_sk = b.segment_sk  
           and a.customer_sk = c.customer_sk  
           and a.year = c.year) t
 group by csk, y, amt   
 order by y, amt desc;

        查询结果如图3 所示。

图3

3. 定期装载

        定期装载与初始装载类似，年度销售事实表里的数据被导入分段事实表。每年调度执行下面的定期装载脚本，此脚本装载前一年的销售数据。

insert into annual_sales_order_fact    
select customer_sk,   
       year_month/100,   
       sum(order_amount)    
  from sales_order_fact
 where year_month/100 = extract(year from current_date) - 1  
 group by customer_sk, year_month/100;    
    
insert into annual_customer_segment_fact    
select b.segment_sk,   
        a.customer_sk,   
        a.year    
  from annual_sales_order_fact a,   
       v_annual_order_segment_dim_latest b    
 where a.year = extract(year from current_date) - 1 
   and annual_order_amount >= band_start_amount    
   and annual_order_amount <= band_end_amount;