维度模型数据仓库（二十） —— 累积的度量

用户1148526

发布于 2022-06-14 12:42:36

4790

发布于 2022-06-14 12:42:36

（五）进阶技术 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)

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2015-11-24，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库