HAWQ取代传统数仓实践（十七）——事实表技术之累积度量
HAWQ取代传统数仓实践（十七）——事实表技术之累积度量
累积度量指的是聚合从序列内第一个元素到当前元素的数据，例如统计从每年的一月到当前月份的累积销售额。本篇说明如何在销售订单示例中实现累积月销售数量和金额，并对数据仓库模式、初始装载、定期装载做相应地修改。累积度量是半可加的，而且它的初始装载要复杂一些。
一、建立累积度量事实表
执行下面的脚本创建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;腾讯云开发者
