原创

维度模型数据仓库(二十) —— 累积的度量

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://wxy0327.blog.csdn.net/article/details/50013959
(五)进阶技术
        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 16:03:42
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览