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;

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

《Apache Kylin对基于相同字段值的多个维度cube建立的数据处理》

1、生产场景 一张表或多张表,在一张表内部存在完全相同的记录值或者部分相同的记录值,或者多张表之间,关联字段值的记录值相同,基于这些相同字段建立维度,进行cube,Kylin如何对这些相同记录值的数...

维度模型数据仓库(十二) —— 多路径和参差不齐的层次

(五)进阶技术         7. 多路径和参差不齐的层次         本篇讨论多路径层次,它是对单路径层次的扩展。上一篇里数据仓库的月维度只有一条层次路径,即年-季度-月这条路径。在本篇中加一...
  • wzy0623
  • wzy0623
  • 2015年11月11日 14:13
  • 1828

Kylin-百度地图的实践

百度地图开放平台业务部数据智能组主要负责百度地图内部相关业务的大数据计算分析,处理日常百亿级规模数据,为不同业务提供单条SQL毫秒级响应的OLAP多维分析查询服务。 对于Apache Kylin在实...

kylin入门到实战:cube详述

版权申明:转载请注明出处。 文章来源:http://bigdataer.net/?p=306 排版乱?请移步原文获得更好的阅读体验 1.什么是cube? cube是所有dime...
  • lgdlxc
  • lgdlxc
  • 2017年04月14日 11:24
  • 624

Kylin性能调优记——业务技术两手抓

背景最近开始使用了新版本的Kylin,在此之前对于新版本的了解只是代码实现和一些简单的新功能测试,但是并没有导入实际场景的数据做分析和查询,线上Hadoop稳定之后,逐渐得将一些老需求往新的环境迁移,...

Hawq学习笔记 --- postgresql中的数据类型

Hawq学习笔记 --- postgresql中的数据类型

TimeCacheMap的解析与使用

在storm的开发中,经常有这样的业务场景,比如统计1分钟或者5分钟粒度的UV,我们可能会这样想设置一个set,其中用用户user_id与logtime组合起来作为key存入set,每次来一条用户消息...

四十六、利用yarn多队列实现hadoop资源隔离

大数据处理离不开hadoop集群的部署和管理,对于本来硬件资源就不多的创业团队来说,做好资源的共享和隔离是很有必要的,毕竟不像BAT那么豪,那么怎么样能把有限的节点同时分享给多组用户使用而且互不影响呢...

HAWQ取代传统数仓实践(九)——维度表技术之退化维度

退化维度技术减少维度的数量,简化维度数据仓库模式。简单的模式比复杂的更容易理解,也有更好的查询性能。        有时,维度表中除了业务主键外没有其它内容。例如,在本销售订单示例中,订单维度表除了订...
  • wzy0623
  • wzy0623
  • 2017年05月26日 13:29
  • 2148

HAWQ取代传统数仓实践(十一)——维度表技术之维度合并

有一种合并维度的情况,就是本来属性相同的维度,因为某种原因被设计成重复的维度属性。例如,在销售订单示例中,随着数据仓库中维度的增加,我们会发现有些通用的数据存在于多个维度中。客户维度的客户地址相关信息...
  • wzy0623
  • wzy0623
  • 2017年05月31日 15:03
  • 2675
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HAWQ取代传统数仓实践(十二)——维度表技术之分段维度
举报原因:
原因补充:

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