HAWQ取代传统数仓实践(十七)——事实表技术之累积度量

原创 2017年06月09日 18:38:13
        累积度量指的是聚合从序列内第一个元素到当前元素的数据,例如统计从每年的一月到当前月份的累积销售额。本篇说明如何在销售订单示例中实现累积月销售数量和金额,并对数据仓库模式、初始装载、定期装载做相应地修改。累积度量是半可加的,而且它的初始装载要复杂一些。

一、建立累积度量事实表

        执行下面的脚本创建month_end_balance_fact事实表,用来存储销售订单金额和数量的月累积值。
set search_path=tds;
create table month_end_balance_fact (    
    year_month int,    
    product_sk int,    
    month_end_amount_balance numeric(10,2),    
    month_end_quantity_balance int ); 

comment on table month_end_balance_fact is '累积度量事实表';   
comment on column month_end_balance_fact.year_month is '年月';   
comment on column month_end_balance_fact.product_sk is '产品代理键';        
comment on column month_end_balance_fact.month_end_amount_balance is '累积金额';        
comment on column month_end_balance_fact.month_end_quantity_balance is '累积数量';   

二、初始装载

        现在要把month_end_sales_order_fact表里的数据装载进month_end_balance_fact表,下面显示了初始装载month_end_balance_fact表的脚本。此脚本装载累的月销售订单汇总数据,从每年的一月累积到当月,累积数据不跨年。
insert into month_end_balance_fact   
select a.year_month,
       b.product_sk,
       sum(b.month_order_amount) month_order_amount,    
       sum(b.month_order_quantity) month_order_quantity 
  from (select distinct 
               year_month, 
               year_month/100 year1, 
               year_month - year_month/100*100 month1
          from v_month_end_sales_order_fact) a,
       (select *,
               year_month/100 year1, 
               year_month - year_month/100*100 month1,
               max(year_month) over () max_year_month  
          from v_month_end_sales_order_fact) b
 where a.year_month <= b.max_year_month
   and a.year1 = b.year1 and b.month1 <= a.month1
 group by a.year_month, b.product_sk;
        子查询获取month_end_sales_order_fact表的数据,及其年月和最大月份代理键。外层查询汇总每年一月到当月的累积销售数据,a.year_month <= b.max_year_month条件用于限定只统计到现存的最大月份为止。
        为了确认初始装载是否正确,在执行完初始装载脚本后,分别查询month_end_sales_order_fact和month_end_balance_fact表。
        查询周期快照:
select year_month,  
       product_sk psk,  
       month_order_amount amt,  
       month_order_quantity qty  
  from v_month_end_sales_order_fact 
 order by year_month, psk;
        查询结构如图1所示。

图1

        查询累积度量:
select year_month,
       product_sk psk,  
       month_end_amount_balance amt,  
       month_end_quantity_balance qty  
  from month_end_balance_fact
 order by year_month, psk;
        查询结构如图2所示。
图2

        可以看到,2016年3月的商品销售金额被累积到了2016年4月,2016年3月和4月的商品销售金额被累积到了2016年5月,等等。

三、定期装载

        下面所示的month_balance_sum.sql脚本用于定期装载销售订单累积度量,每个月执行一次,装载上个月的数据。可以在执行完月周期快照表定期装载后执行该脚本。
insert into month_end_balance_fact    
select year_month,    
         product_sk,    
         sum(month_order_amount),    
         sum(month_order_quantity)    
  from (select *    
          from v_month_end_sales_order_fact 
         where year_month = :v_year_month  
       union all    
        select :v_year_month,  
               product_sk product_sk,  
               month_end_amount_balance month_order_amount,  
               month_end_quantity_balance month_order_quantity   
          from month_end_balance_fact    
         where year_month in 
(select max(case when :v_year_month - :v_year_month/100*100 = 1 then 0 else year_month end)    
   from month_end_balance_fact)) t  
 group by year_month, product_sk;
        子查询将累积度量表和月周期快照表做并集操作,增加上月的累积数据。最外层查询执行销售数据按月和产品的分组聚合。最内层的case语句用于在每年一月时重新归零再累积。:v_year_month以是年月参数。

四、测试

        执行月周期快照函数,装载2017年6月的数据。
select fn_month_sum(201706);
        执行累积度量定期装载脚本,以shell命令`date +%Y%m`的输出作为年月参数传入month_balance_sum.sql文件中。
su - gpadmin -c 'export PGPASSWORD=123456;psql -U dwtest -d dw -h hdp3 -v v_year_month=''`date +%Y%m`'' -f ~/month_balance_sum.sql'
        执行和前面初始装载后相同的查询,周期快照表和累积度量表的查询结果分别如图3、图4所示。

图3


图4


        可以看到,2017年5月的商品销售金额和数量被累积到了2017年6月。产品1、2、5累加了5、6两个月的销售数据,产品3、4在6月没有销售,所以5月的销售数据顺延到6月。

五、查询

        事实表中的数字度量值可划分为可加、半可加、不可加三类。可加性度量可以按照与事实表关联的任意维度汇总,就是说按任何维度汇总得到的度量和是相同的,事实表中的大部分度量属于此类。半可加度量可以对某些维度汇总,但不能对所有维度汇总。余额是常见的半可加度量,除了时间维度外,它们可以跨所有维度进行加法操作。另外还有些度量是完全不可加的,例如比例。对非可加度量,较好的处理方法是尽可能存储构成非可加度量的可加分量,如构成比例的分子和分母,并将这些分量汇总到最终的结果集合中,而对不可加度量的计算通常发生在BI层或OLAP层。
        累积度量必须要小心使用,因为它是“半可加”的。一个半可加度量在某些维度(通常是时间维度)上是不可加的。例如,可以通过产品正确地累加月底累积销售金额。 
dw=> select year_month, sum(month_end_amount_balance) s  
dw->   from month_end_balance_fact    
dw->  group by year_month  
dw->  order by year_month; 
 year_month |     s     
------------+-----------
     201603 | 191158.00
     201604 | 345600.00
     201605 | 455772.00
     201606 | 572190.00
     201705 | 253400.00
     201706 | 272086.00
(6 rows)

        而通过月份累加月底金额:

dw=> select product_name, sum(month_end_amount_balance) s  
dw->   from month_end_balance_fact a,  
dw->        product_dim b  
dw->  where a.product_sk = b.product_sk  
dw->  group by product_name
dw->  order by product_name;
  product_name   |     s     
-----------------+-----------
 flat panel      |  99332.00
 floppy drive    | 927195.00
 hard disk drive | 932285.00
 keyboard        | 125220.00
 lcd panel       |   6174.00
(5 rows)

        以上查询结果是错误的。正确的结果应该和下面的在month_end_sales_order_fact表上进行的查询结果相同。

dw=> select product_name, sum(month_order_amount) s  
dw->   from month_end_sales_order_fact a,  
dw->        product_dim b  
dw->  where a.product_sk = b.product_sk  
dw->  group by product_name
dw->  order by product_name; 
  product_name   |     s     
-----------------+-----------
 flat panel      |  49666.00
 floppy drive    | 348655.00
 hard disk drive | 375481.00
 keyboard        |  67387.00
 lcd panel       |   3087.00
(5 rows)

        注意,迟到的事实对累积度量的影响非常大。例如,2016年1月的数据到了2017年1月才进入数据仓库,那么2016年2月以后每个月的累积度量都要改变。如果重点考虑迟到事实数据和HAWQ无法行级更新的限制,也许使用查询视图方式实现累积度量是更佳选择。

create view v_month_end_balance_fact as 
select a.year_month,
       b.product_sk,
       sum(b.month_order_amount) month_order_amount,    
       sum(b.month_order_quantity) month_order_quantity 
  from (select distinct 
               year_month, 
               year_month/100 year1, 
               year_month - year_month/100*100 month1
          from v_month_end_sales_order_fact) a,
       (select *,
               year_month/100 year1, 
               year_month - year_month/100*100 month1,
               max(year_month) over () max_year_month  
          from month_end_sales_order_fact) b
 where a.year_month <= b.max_year_month
   and a.year1 = b.year1 and b.month1 <= a.month1
 group by a.year_month, b.product_sk;


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

HAWQ取代传统数仓实践(七)——维度表技术之维度子集

有些需求不需要最细节的数据。例如更想要某个月的销售汇总,而不是某天的数据。再比如相对于全部的销售数据,可能对某些特定状态的数据更感兴趣等。此时事实数据需要关联到特定的维度,这些特定维度包含在从细节维度...
  • wzy0623
  • wzy0623
  • 2017年05月24日 14:21
  • 3275

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

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

HAWQ取代传统数仓实践(十四)——事实表技术之累积快照

一、累积快照简介        累积快照事实表用于定义业务过程开始、结束以及期间的可区分的里程碑事件。通常在此类事实表中针对过程中的关键步骤都包含日期外键,并包含每个步骤的度量,这些度量的产生一般都会...
  • wzy0623
  • wzy0623
  • 2017年06月05日 14:52
  • 2531

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

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

HAWQ取代传统数仓实践(十五)——事实表技术之无事实的事实表

一、无事实事实表简介        在多维数据仓库建模中,有一种事实表叫做“无事实的事实表”。普通事实表中,通常会保存若干维度外键和多个数字型度量,度量是事实表的关键所在。然而在无事实的事实表中没有这...
  • wzy0623
  • wzy0623
  • 2017年06月06日 11:02
  • 3221

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

有一种合并维度的情况,就是本来属性相同的维度,因为某种原因被设计成重复的维度属性。例如,在销售订单示例中,随着数据仓库中维度的增加,我们会发现有些通用的数据存在于多个维度中。客户维度的客户地址相关信息...
  • wzy0623
  • wzy0623
  • 2017年05月31日 15:03
  • 2680

HAWQ取代传统数仓实践(十)——维度表技术之杂项维度

一、什么是杂项维度        简单地说,杂项维度就是一种包含的数据具有很少可能值的维度。事务型商业过程通常产生一系列混杂的、低基数的标志位或状态信息。与其为每个标志或属性定义不同的维度,不如建立单...
  • wzy0623
  • wzy0623
  • 2017年05月27日 13:59
  • 2485

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

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

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

一、分段维度简介        在客户维度中,最具有分析价值的属性就是各种分类,这些属性的变化范围比较大。对某个个体客户来说,可能的分类属性包括:性别、年龄、民族、职业、收入和状态,例如,新客户、活跃...
  • wzy0623
  • wzy0623
  • 2017年06月01日 11:42
  • 3389

HAWQ取代传统数仓实践(十九)——OLAP

一、OLAP简介1. 概念        OLAP是英文是On-Line Analytical Processing的缩写,意为联机分析处理。此概念最早由关系数据库之父E.F.Codd于1993年提出...
  • wzy0623
  • wzy0623
  • 2017年06月14日 13:37
  • 3130
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HAWQ取代传统数仓实践(十七)——事实表技术之累积度量
举报原因:
原因补充:

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