版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433133
十六、累积的度量
代码语言：txt复制 本篇说明如何实现累积月底金额，并对数据仓库模式和初始装载、定期装载脚本做相应地修改。累积度量是半可加的，而且它的初始装载比前面做的要复杂的多。 可加、半可加、不可加事实 事实表中的数字度量可划分为三类。最灵活、最有用的度量是完全可加的，可加性度量可以按照与事实表关联的任意维度汇总。半可加度量可以对某些维度汇总，但不能对所有维度汇总。差额是常见的半可加度量，除了时间维度外，它们可以跨其它所有维度进行加法操作。另外，一些度量是完全不可加的，例如比率。1. 修改模式
代码语言：txt复制 建立一个新叫做month_end_balance_fact的事实表，用来存储销售订单金额的月底累积值。month_end_balance_fact表在模式中构成了另一个星型模式。新的星型模式除了包括这个新的事实表，还包括两个其它星型模式中已有的维度表，即product_dim和month_dim。下图显示了新的模式。注意这里只显示了相关的表。
下面的脚本用于创建month_end_balance_fact表。use dw;
create table month_end_balance_fact (
month_sk int,
product_sk int,
month_end_amount_balance decimal(10,2),
month_end_quantity_balance int
); 因为对此事实表只有insert操作，没有update、delete操作，所以这里没有用orc文件格式，而是采用了缺省的文本格式。2. 初始装载
代码语言：txt复制 现在要把month_end_sales_order_fact表里的数据导入month_end_balance_fact表，下面显示了初始装载month_end_balance_fact表的脚本。此脚本装载累月的月底销售订单，每年的年初都要重置累积金额。use dw;
insert overwrite table month_end_balance_fact
select a.month_sk,
b.product_sk,
sum(b.month_order_amount) month_order_amount,
sum(b.month_order_quantity) month_order_quantity
from month_dim a,
(select a.*,
b.year,
b.month,
max(a.order_month_sk) over () max_month_sk
from month_end_sales_order_fact a, month_dim b
where a.order_month_sk = b.month_sk) b
where a.month_sk <= b.max_month_sk and a.year = b.year and b.month <= a.month
group by a.month_sk , b.product_sk; 为了确认初始装载是否正确，先查询month_end_sales_order_fact表，然后在执行完初始装载后查询month_end_balance_fact表。 使用下面的语句查询month_end_sales_order_fact表。use dw;
select b.year year,
b.month month,
a.product_sk psk,
a.month_order_amount amt,
a.month_order_quantity qty
from month_end_sales_order_fact a,
month_dim b
where a.order_month_sk = b.month_sk
cluster by year, month, psk; 查询结果如下图所示。
使用下面的语句查询month_end_balance_fact表。use dw;
select b.year year,
b.month month,
a.product_sk psk,
a.month_end_amount_balance amt,
a.month_end_quantity_balance qty
from month_end_balance_fact a,
month_dim b
where a.month_sk = b.month_sk
cluster by year, month, psk; 查询结果如下图所示。
从图中可也看到，2016年6月的商品销售金额和数量被累积到了了2016年7月。商品1和2累加了6月和7月的销售，商品3在7月没有销售，所以6月的销售顺延到7月，商品4和5只有7月的销售。3. 定期装载
代码语言：txt复制 下面所示的month_balance_sum.sql脚本用于定期装载销售订单金额月底累积事实表，该脚本在每个月执行一次，装载上个月的数据。-- 设置变量以支持事务
set hive.support.concurrency=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.dbtxnmanager;
set hive.compactor.initiator.on=true;
set hive.compactor.worker.threads=1;
use dw;
set hivevar:pre_month_date = add_months(current_date,-1);
set hivevar:year = year(${hivevar:pre_month_date});
set hivevar:month = month(${hivevar:pre_month_date});
insert into month_end_balance_fact
select order_month_sk,
product_sk,
sum(month_order_amount),
sum(month_order_quantity)
from (select a.*
from month_end_sales_order_fact a,
month_dim b
where a.order_month_sk = b.month_sk
and b.year = ${hivevar:year}
and b.month = ${hivevar:month}
union all
select month_sk 1 order_month_sk,
product_sk product_sk,
month_end_amount_balance month_order_amount,
month_end_quantity_balance month_order_quantity
from month_end_balance_fact a
where a.month_sk in (select max(case when ${hivevar:month} = 1 then 0 else month_sk end)
from month_end_balance_fact)) t
group by order_month_sk, product_sk;4. 测试定期装载
代码语言：txt复制 使用下面步骤测试非1月的装载：（1）使用下面的命令向month_end_sales_order_fact表添加两条记录
代码语言：javascript复制insert into dw.month_end_sales_order_fact values (200,1,1000,10),(200,6,1000,10);（2）设置时间
将set hivevar:pre_month_date = add_months(current_date,-1); 行改为set hivevar:pre_month_date = current_date;，装载2016年8月的数据。
（3）执行定期装载
代码语言：javascript复制beeline -u jdbc:hive2://cdh2:10000/dw -f month_balance_sum.sql（4）查询month_end_balance_fact表
代码语言：javascript复制select *
from dw.month_end_balance_fact a
cluster by a.month_sk, a.product_sk; 查询结果如下图所示。
从图中可以看到，product_sk为6的产品是本年前面月份没有销售而8月份有销售的，product_sk为1的产品是本年前面月份和8月份都有销售的，而product_sk为2、3、4、5的产品是本年前面月份有销售而8月份没有销售的。 使用下面步骤测试1月的装载：（1）使用下面的命令向month_end_sales_order_fact表添加两条记录，month_sk的值是205，指的是2017年1月
代码语言：javascript复制insert into dw.month_end_sales_order_fact values (205,1,1000,10);
insert into dw.month_end_sales_order_fact values (205,6,1000,10);（2）使用下面的命令向month_end_balance_fact表添加三条记录
代码语言：javascript复制insert into dw.month_end_balance_fact values (204,1,1000,10);
insert into dw.month_end_balance_fact values (204,6,1000,10);
insert into dw.month_end_balance_fact values (204,3,1000,10);（3）将set hivevar:pre_month_date = add_months(current_date,-1); 行改为set hivevar:pre_month_date = add_months('2017-02-01',-1);，装载2017年1月的数据。
（4）执行定期装载
代码语言：javascript复制beeline -u jdbc:hive2://cdh2:10000/dw -f month_balance_sum.sql（5）查询month_end_balance_fact表
代码语言：javascript复制select *
from dw.month_end_balance_fact a
cluster by a.month_sk, a.product_sk; 查询结果如下图所示。
从图中可以看到，2017年1月只装载了新增的两条销售记录。（6）删除测试数据
代码语言：javascript复制delete from dw.month_end_sales_order_fact where order_month_sk >=200;
create table t1 as select * from month_end_balance_fact where month_sk < 200;
insert overwrite table month_end_balance_fact select * from t1;
drop table t1;5. 查询
代码语言：txt复制 本小节使用两个查询展示月底累积金额度量（也就是累积度量）必须要小心使用，因为它不是“全可加”的。一个非全可加度量在某些维度（通常是时间维度）上是不可加的。 通过产品可加，可以通过产品正确地累加月底累积金额。use dw;
select year, month, sum(month_end_amount_balance) s
from month_end_balance_fact a,
month_dim b
where a.month_sk = b.month_sk
group by year, month
cluster by year, month; 查询结果如下图所示。
通过月份累加月底金额。use dw;
select product_name, sum(month_end_amount_balance) s
from month_end_balance_fact a,
product_dim b
where a.product_sk = b.product_sk
group by product_name; 查询结果如下图所示。
查询结果是错误的。正确的结果应该和下面的在month_end_sales_order_fact表上进行的查询结果相同。use dw;
select product_name, sum(month_order_amount) s
from month_end_sales_order_fact a,
product_dim b
where a.product_sk = b.product_sk
group by product_name; 查询结果如下图所示。
