(五)进阶技术 15. 累积的度量 本篇说明如何实现累积月底金额,并对数据仓库模式和初始装载、定期装载脚本做相应地修改。累积度量是半可加的,而且它的初始装载比前面做的要复杂的多。 可加、半可加、不可加事实 事实表中的数字度量可划分为三类。最灵活、最有用的度量是完全可加的,可加性度量可以按照与事实表关联的任意维度汇总。半可加度量可以对某些维度汇总,但不能对所有维度汇总。差额是常见的半可加度量,除了时间维度外,它们可以跨所有维度进行加法操作。另外,一些度量是完全不可加的,例如比率。 修改模式 建立一个新叫做month_end_balance_fact的事实表,用来存储销售订单金额的月底累积值。month_end_balance_fact表在模式中构成了另一个星型模式。新的星型模式除了包括这个新的事实表,还包括两个其它星型模式中已有的维度表,即product_dim和month_dim。图(五)- 15-1显示了新的模式。注意这里只显示了相关的表。
图(五)- 15-1
清单(五)-15-1里的脚本用于创建month_end_balance_fact表。
USE dw;
CREATE TABLE month_end_balance_fact (
month_sk INT,
product_sk INT,
month_end_amount_balance DEC(10 , 2 ),
month_end_quantity_balance INT
);
alter table month_end_balance_fact add foreign key (month_sk) references month_dim(month_sk);
alter table month_end_balance_fact add foreign key (product_sk) references product_dim(product_sk);
清单(五)-15-1
初始装载 现在要把month_end_sales_order_fact表里的数据导入month_end_balance_fact表,清单(五)-15-2里是初始装载month_end_balance_fact表的脚本。此脚本装载累月的月底销售订单,每年的年初都要重置累积金额。month_end_sales_order_fact表里月底销售数据的最后月份是2015年3月。
USE dw;
insert into 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
inner join
(select
a.*,
b.year,
b.month,
@a:=if(a.order_month_sk > @a, a.order_month_sk, @a) as max_month_sk
from
month_end_sales_order_fact a, month_dim b, (select @a:=0) c
where
a.order_month_sk = b.month_sk) b ON a.year = b.year and b.month <= a.month
where
a.month_sk <= @a
group by a.month_sk , b.product_sk;
commit;
清单(五)-15-2
使用Kettle转换初始装载销售订单金额月底累积事实表如图(五)- 15-2到图(五)- 15-9所示。
图(五)- 15-2
图(五)- 15-3
图(五)- 15-4
图(五)- 15-5
图(五)- 15-6
图(五)- 15-7
图(五)- 15-8
图(五)- 15-9
为了确认初始装载是否正确,查询month_end_sales_order_fact和month_end_balance_fact表。查询第一个表的语句和结果如下所示。 mysql> select -> order_month_sk mosk, -> product_sk psk, -> month_order_amount amt, -> month_order_quantity qty -> from -> month_end_sales_order_fact -> order by order_month_sk , product_sk; +------+------+----------+------+ | mosk | psk | amt | qty | +------+------+----------+------+ | 169 | 3 | 1000.00 | NULL | | 170 | 1 | 1000.00 | NULL | | 171 | 2 | 2000.00 | NULL | | 172 | 3 | 2500.00 | NULL | | 173 | 1 | 3000.00 | NULL | | 174 | 2 | 3500.00 | NULL | | 175 | 3 | 4000.00 | NULL | | 176 | 1 | 4500.00 | NULL | | 177 | 2 | 1000.00 | NULL | | 178 | 3 | 1000.00 | NULL | | 182 | 1 | 1000.00 | 10 | | 182 | 2 | 5000.00 | NULL | | 182 | 3 | 4000.00 | NULL | | 183 | 1 | 46500.00 | 420 | | 183 | 2 | 25000.00 | 120 | | 183 | 4 | 47000.00 | 275 | | 183 | 5 | 27000.00 | 90 | | 183 | 7 | 2000.00 | 20 | +------+------+----------+------+ 18 rows in set (0.00 sec) 执行完清单(五)-15-2里的脚本,查询month_end_balance_fact表的语句和结果如下所示。 mysql> select -> month_sk msk, -> product_sk psk, -> month_end_amount_balance amt, -> month_end_quantity_balance qty -> from -> month_end_balance_fact -> order by month_sk , product_sk; +------+------+----------+------+ | msk | psk | amt | qty | +------+------+----------+------+ | 169 | 3 | 1000.00 | NULL | | 170 | 1 | 1000.00 | NULL | | 170 | 3 | 1000.00 | NULL | | 171 | 1 | 1000.00 | NULL | | 171 | 2 | 2000.00 | NULL | | 171 | 3 | 1000.00 | NULL | | 172 | 1 | 1000.00 | NULL | | 172 | 2 | 2000.00 | NULL | | 172 | 3 | 3500.00 | NULL | | 173 | 1 | 4000.00 | NULL | | 173 | 2 | 2000.00 | NULL | | 173 | 3 | 3500.00 | NULL | | 174 | 1 | 4000.00 | NULL | | 174 | 2 | 5500.00 | NULL | | 174 | 3 | 3500.00 | NULL | | 175 | 1 | 4000.00 | NULL | | 175 | 2 | 5500.00 | NULL | | 175 | 3 | 7500.00 | NULL | | 176 | 1 | 8500.00 | NULL | | 176 | 2 | 5500.00 | NULL | | 176 | 3 | 7500.00 | NULL | | 177 | 1 | 8500.00 | NULL | | 177 | 2 | 6500.00 | NULL | | 177 | 3 | 7500.00 | NULL | | 178 | 1 | 8500.00 | NULL | | 178 | 2 | 6500.00 | NULL | | 178 | 3 | 8500.00 | NULL | | 179 | 1 | 8500.00 | NULL | | 179 | 2 | 6500.00 | NULL | | 179 | 3 | 8500.00 | NULL | | 180 | 1 | 8500.00 | NULL | | 180 | 2 | 6500.00 | NULL | | 180 | 3 | 8500.00 | NULL | | 182 | 1 | 1000.00 | 10 | | 182 | 2 | 5000.00 | NULL | | 182 | 3 | 4000.00 | NULL | | 183 | 1 | 47500.00 | 430 | | 183 | 2 | 30000.00 | 120 | | 183 | 3 | 4000.00 | NULL | | 183 | 4 | 47000.00 | 275 | | 183 | 5 | 27000.00 | 90 | | 183 | 7 | 2000.00 | 20 | +------+------+----------+------+ 42 rows in set (0.00 sec) 注意 月份代理键169是2014年1月,月份代理键183是2015年3月,意味着month_end_balance_fact表已经正确地导入了从2014年1月到2015年3月的所有月底销售订单事实数据。累积金额也都导入正确:金额和数量被累积滚到下一个月。 定期装载 清单(五)-15-3里的脚本用于定期装载销售订单金额月底累积事实表,该脚本在每个月的1日执行,装载上个月的数据。
USE dw;
SET @pre_date = SUBDATE(CURRENT_DATE,1) ;
SET @year = YEAR(@pre_date);
SET @month = MONTH(@pre_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
(select
a . *
from
month_end_sales_order_fact a, month_dim b
where
a.order_month_sk = b.month_sk
and b.year = @year
and b.month = @month) a
left join (select
*
from
month_end_balance_fact
where
month_sk = (select
max(month_sk)
from
month_end_balance_fact)) b ON (case when @month = 1 then 0 else a.product_sk end) = b.product_sk union all select
a . *
from
(select
month_sk + 1,product_sk,month_end_amount_balance,month_end_quantity_balance
from
month_end_balance_fact
where
month_sk = (select
max(case when @month = 1 then 0 else month_sk end)
from
month_end_balance_fact)) a
left join (select
a . *
from
month_end_sales_order_fact a, month_dim b
where
a.order_month_sk = b.month_sk
and b.year = @year
and b.month = @month) b ON a.product_sk = b.product_sk) t
group by order_month_sk , product_sk;
commit;
清单(五)-15-3
图(五)- 15-10到图(五)- 15-14是使用Kettle定期装载销售订单金额月底累积事实表的主转换。
图(五)- 15-10
图(五)- 15-11
图(五)- 15-12
图(五)- 15-13
图(五)- 15-14
图(五)- 15-15到图(五)- 15-24是使用Kettle定期装载非1月的销售订单金额月底累积事实表的子转换。
图(五)- 15-15
图(五)- 15-16
图(五)- 15-17
图(五)- 15-18
图(五)- 15-19
图(五)- 15-20
图(五)- 15-21
图(五)- 15-22
图(五)- 15-23
图(五)- 15-24
图(五)- 15-25到图(五)- 15-27是使用Kettle定期装载1月的销售订单金额月底累积事实表的子转换。
图(五)- 15-25
图(五)- 15-26
图(五)- 15-27
测试定期装载 使用下面步骤测试非1月的装载: 1. 使用下面的命令向month_end_sales_order_fact表添加两条记录 USE dw; insert into month_end_sales_order_fact values (184,1,1000,10),(184,6,1000,10); commit; 2. 把系统日期修改为2015年5月1日。 3. 执行清单(五)-15-3里的脚本或对应的Kettle转换。 4. 查询month_end_balance_fact表,查询语句和结果如下所示。 mysql> select * from month_end_balance_fact where month_sk>=192 order by month_sk,product_sk; +----------+------------+--------------------------+----------------------------+ | month_sk | product_sk | month_end_amount_balance | month_end_quantity_balance | +----------+------------+--------------------------+----------------------------+ | 183 | 1 | 47500.00 | 430 | | 183 | 2 | 30000.00 | 120 | | 183 | 3 | 4000.00 | NULL | | 183 | 4 | 47000.00 | 275 | | 183 | 5 | 27000.00 | 90 | | 183 | 7 | 2000.00 | 20 | | 184 | 1 | 48500.00 | 440 | | 184 | 2 | 30000.00 | 120 | | 184 | 3 | 4000.00 | NULL | | 184 | 4 | 47000.00 | 275 | | 184 | 5 | 27000.00 | 90 | | 184 | 6 | 1000.00 | 10 | | 184 | 7 | 2000.00 | 20 | +----------+------------+--------------------------+----------------------------+ 13 rows in set (0.00 sec) 注意 product_sk为6的产品是本年前面月份没有销售而4月份有销售的,product_sk为1的产品是本年前面月份和4月份都有销售的,而product_sk为2、3、4、5、7的产品是本年前面月份有销售而4月份没有销售的。 5. 使用下面的命令恢复month_end_sales_order_fact、month_end_balance_fact表。 use dw; delete from month_end_sales_order_fact where order_month_sk>=184; delete from month_end_balance_fact where month_sk>=184; commit; 使用下面步骤测试1月的装载: 1. 使用下面的命令向month_end_sales_order_fact表添加两条记录 USE dw; insert into month_end_sales_order_fact values (193,1,1000,10),(193,6,1000,10); commit; 2. 使用下面的命令向month_end_balance_fact表添加三条记录 USE dw; insert into month_end_balance_fact values (192,1,1000,10),(192,6,1000,10),(192,3,1000,10); commit; 3. 把系统日期修改为2016年2月1日。 4. 执行清单(五)-15-3里的脚本或对应的Kettle转换。 5. 查询month_end_balance_fact表,查询语句和结果如下所示。 mysql> select * from month_end_balance_fact where month_sk>=192 order by month_sk,product_sk; +----------+------------+--------------------------+----------------------------+ | month_sk | product_sk | month_end_amount_balance | month_end_quantity_balance | +----------+------------+--------------------------+----------------------------+ | 192 | 1 | 1000.00 | 10 | | 192 | 3 | 1000.00 | 10 | | 192 | 6 | 1000.00 | 10 | | 193 | 1 | 1000.00 | 10 | | 193 | 6 | 1000.00 | 10 | +----------+------------+--------------------------+----------------------------+ 5 rows in set (0.00 sec) 注意 month_sk为2016年1月份,只装载了新增的两条销售记录。 6. 使用下面的命令恢复month_end_sales_order_fact、month_end_balance_fact表。 use dw; delete from month_end_sales_order_fact where order_month_sk>=192; delete from month_end_balance_fact where month_sk>=192; commit; 查询示例 本节使用两个查询展示月底累积金额度量(也就是累积度量)必须要小心使用,因为它不是全可加(也即半可加)的。一个非全可加度量在某些维度(通常是时间维度)上是不可加的。 通过产品可加 可以通过产品正确地累加月底累积金额,查询命令和结果如下所示。 mysql> SELECT -> Year, month, SUM(month_end_amount_balance) -> FROM -> month_end_balance_fact a, -> month_dim b -> WHERE -> a.month_sk = b.month_sk -> GROUP BY year , month -> ORDER BY year , month; +------+-------+-------------------------------+ | Year | month | SUM(month_end_amount_balance) | +------+-------+-------------------------------+ | 2014 | 1 | 1000.00 | | 2014 | 2 | 2000.00 | | 2014 | 3 | 4000.00 | | 2014 | 4 | 6500.00 | | 2014 | 5 | 9500.00 | | 2014 | 6 | 13000.00 | | 2014 | 7 | 17000.00 | | 2014 | 8 | 21500.00 | | 2014 | 9 | 22500.00 | | 2014 | 10 | 23500.00 | | 2014 | 11 | 23500.00 | | 2014 | 12 | 23500.00 | | 2015 | 2 | 10000.00 | | 2015 | 3 | 157500.00 | +------+-------+-------------------------------+ 14 rows in set (0.00 sec) 通过月份累加月底金额,查询命令和结果如下所示。 mysql> SELECT -> product_name, SUM(month_end_amount_balance) -> FROM -> month_end_balance_fact a, -> product_dim b -> WHERE -> a.product_sk = b.product_sk -> GROUP BY product_code -> ORDER BY product_code; +--------------------------+-------------------------------+ | product_name | SUM(month_end_amount_balance) | +--------------------------+-------------------------------+ | Hard Disk Drive | 106000.00 | | Floppy Drive | 83500.00 | | LCD Panel | 116500.00 | | Keyboard | 27000.00 | | High End Hard Disk Drive | 2000.00 | +--------------------------+-------------------------------+ 5 rows in set (0.00 sec) 查询结果是错误的。正确的结果应该和下面的在month_end_sales_order_fact表上进行的查询结果相同。 mysql> SELECT -> product_name, sum(month_order_amount) -> FROM -> month_end_sales_order_fact a, -> product_dim b -> WHERE -> a.product_sk = b.product_sk -> group by product_code; +--------------------------+-------------------------+ | product_name | sum(month_order_amount) | +--------------------------+-------------------------+ | Hard Disk Drive | 56000.00 | | Floppy Drive | 36500.00 | | LCD Panel | 59500.00 | | Keyboard | 27000.00 | | High End Hard Disk Drive | 2000.00 | +--------------------------+-------------------------+ 5 rows in set (0.00 sec)