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

原创 2017年06月01日 11:42:25

一、分段维度简介

        在客户维度中,最具有分析价值的属性就是各种分类,这些属性的变化范围比较大。对某个个体客户来说,可能的分类属性包括:性别、年龄、民族、职业、收入和状态,例如,新客户、活跃客户、不活跃客户、已流失客户等。在这些分类属性中,有一些能够定义成包含连续值的分段,例如年龄和收入这种数值型的属性,天然就可以分成连续的数值区间,而象状态这种描述性的属性,可能需要用户根据自己的实际业务仔细定义,通常定义的根据是某种可度量的数值。
        组织还可能使用为其客户打分的方法刻画客户行为。分段维度模型通常以不同方式按照积分将客户分类,例如,基于他们的购买行为、支付行为、流失走向等。每个客户用所得的分数标记。
        一个常用的客户评分及分析系统是考察客户行为的相关度(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;

 
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

HAWQ取代传统数仓实践(十三)——事实表技术之周期快照

一、周期快照简介        周期快照事实表中的每行汇总了发生在某一标准周期,如一天、一周或一月的多个度量。其粒度是周期性的时间段,而不是单个事务。周期快照事实表通常包含许多数据的总计,因为任何与事...

HAWQ取代传统数仓实践(三)——初始ETL(Sqoop、HAWQ)

一、用sqoop用户建立初始抽取脚本        本示例要用Sqoop将MySQL的数据抽取到HDFS上的指定目录,然后利用HAWQ外部表功能将HDFS数据文件装载到内部表中。表1汇总了示例中维度表...

HAWQ论文笔记

1、背景HAWQ是一个构建在HDFS之上的MPP(massively parallel processing)SQL引擎,不像其他构建在hadoop之上的SQL引擎,HAWQ支持标准SQL,并且完整的...

基于Hadoop生态圈的数据仓库实践 —— 进阶技术(一)

一、增加列         数据仓库最常碰到的扩展是给一个已经存在的维度表和事实表添加列。本节说明如何在客户维度表和销售订单事实表上添加列,并在新列上应用SCD2,以及对定时装载脚本所做的修改。假设需...

HAWQ技术解析(八) —— 大表分区

一、HAWQ中的分区表        与大多数关系数据库一样,HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表,外部分区表在后面“外部数据”篇讨论。在数据仓库应用中,事实表通常有非常多的...

HAWQ取代传统数仓实践(八)——维度表技术之角色扮演维度

单个物理维度可以被事实表多次引用,每个引用连接逻辑上存在差异的角色维度。例如,事实表可以有多个日期,每个日期通过外键引用不同的日期维度,原则上每个外键表示不同的日期维度视图,这样引用具有不同的含义。这...

挑战数据结构与算法面试题——80题全解析(一)

题目来源“数据结构与算法面试题80道”。

HAWQ技术解析(六) —— 定义对象

HAWQ本质上是一个数据库系统,所以这里所说的对象指的是数据库对象。和其它关系数据库类似,HAWQ中有数据库、表空间、表、视图、自定义数据类型、自定义函数、序列等对象。本篇将简述这些对象的创建与管理。...

基于Hadoop生态圈的数据仓库实践 —— 环境搭建(三)

三、建立数据仓库示例模型         Hadoop及其相关服务安装配置好后,下面用一个小而完整的示例说明多维模型及其相关ETL技术在Hadoop上的具体实现。 1. 设计ERD         ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)