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

一、分段维度简介

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

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

        一个常用的客户评分及分析系统是考察客户行为的相关度(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;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Renderbus云渲染农场

GPU挖矿和GPU渲染农场有多大区别? -Renderbus云渲染

区块链是一个由基于比特币协议的所有参与节点所组成的系统的共享交易记录数据库,也就是公有账本。挖矿就是添加交易记录到该公有账本这一过程,挖矿由矿机通过运算完成。一...

892
来自专栏PPV课数据科学社区

大数据之有指导数据挖掘方法的模型

? 数据挖掘的目的,就是从数据中找到更多的优质用户。什么是有指导的数据挖掘方法模型,以及数据挖掘如何构建模型。在构建一个有指导的数据挖掘模型,首先要理解和定义...

3144
来自专栏机器学习算法与Python学习

浪潮发布最新深度学习框架CAFFE-MPI

德国当地时间6月20日,与英特尔宣布第二代至强Phi芯片“Knights Landing”(以下称KNL)正式上市同步,浪潮在刚刚举行的第31届国际超算大会(I...

3488
来自专栏Spark学习技巧

Apache Kylin优化之—Cube的高级设置

本文转载自bigdata029,从apachekylin公众号系列文章整理。 随着维度数目的增加,Cuboid 的数量会爆炸式地增长。为了缓解 Cube 的构建...

2297
来自专栏CDA数据分析师

实战 | 客户细分:如何找到最有价值的TA?

本文由CDA作者库成员麻赛原创,并授权发布 原文来自公众号麻大湿讲数据(ID:madashi_data)。 ? 客户是最宝贵的资源,没有客户资源就没有生存和发展...

1896
来自专栏数据结构与算法

奖金

【问题描述】   由于无敌的凡凡在2005年世界英俊帅气男总决选中胜出,Yali Company总经理Mr.Z心情好,决定给每位员工发奖金。公司决定以每个人本年...

3438
来自专栏QQ大数据团队的专栏

用户增长分析——用户流失预警

1 前言 针对用户增长分析这个课题,本文主要从用户防流失的角度,阐述如何基于QQ社交网络数据构建用户流失预警模型,找出高潜流失用户,用于定向开展运营激活,从而...

1032
来自专栏Soul Joy Hub

《基于机器学习的企业定价算法研究》阅读笔记

http://blog.csdn.net/u011239443/article/details/78126591 《基于机器学习的企业定价算法研究》 冯...

3344
来自专栏申龙斌的程序人生

分叉Fork【区块链生存训练】

8月1日20:20,Bitcoin Cash(前身是Bitcoin ABC)要给比特币的区块链来个硬分叉,虽然支持它的矿工的算力并不大,但也会闹出个BCC新币来...

2836
来自专栏软件成本造价评估

如何对一个软件项目的成本进行评估或估算?

  在对一个软件项目进行成本估算或评估时,应该包括从项目立项直至项目研发活动结束所花费的资源总和,并且可以按阶段进行估算或测量。 软件成本估算的基本过程...

152

扫码关注云+社区