八、多路径和参差不齐的层次
本节讨论多路径层次，它是对单路径层次的扩展。上一节里数据仓库的月维度只有一条层次路径，即年-季度-月这条路径。在本节中加一个新的级别——促销期，并且加一个新的年-促销期-月的层次路径。这时月维度将有两条层次路径，因此具有多路径层次。本节讨论的另一个主题是不完全层次，这种层次在它的一个或多个级别上没有数据。
1. 增加一个层次
下面的脚本给month_dim表添加一个叫做campaign_session的新列，并建立rds.campaign_session过渡表。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| 1use dw;
2
3-- 增加促销期列
4alter table month_dim rename to month_dim_old;
5create table month_dim (
6 month_sk int comment 'surrogate key',
7 month tinyint comment 'month',
8 month_name varchar(9) comment 'month name',
9 campaign_session varchar(30) comment 'campaign session',
10 quarter tinyint comment 'quarter',
11 year smallint comment 'year'
12)
13comment 'month dimension table'
14clustered by (month_sk) into 8 buckets
15stored as orc tblproperties ('transactional'='true') ;
16insert into month_dim select month_sk,month,month_name,null,quarter,year from month_dim_old;
17drop table month_dim_old;
18
19-- 建立促销期过渡表
20use rds;
21create table campaign_session (
22 campaign_session varchar(30),
23 month tinyint,
24 year smallint
25)
26row format delimited fields terminated by ',' stored as textfile;
27 |
修改后的模式如下图所示。
假设所有促销期都不跨年，并且一个促销期可以包含一个或多个年月，但一个年月只能属于一个促销期。为了理解促销期如何工作，看下表的示例。
|
|
| Campaign Session |
Month |
| 2016 First Campaign |
January-April |
| 2016 Second Campaign |
May-July |
| 2016 Third Campaign |
August-August |
| 2016 Last Campaign |
September-December |
每个促销期有一个或多个月。一个促销期也许并不是正好一个季度，也就是说，促销期级别不能上卷到季度，但是促销期可以上卷至年级别。2016年促销期的数据如下，并保存在campaign_session.csv文件中。
1
2
3
4
5
6
7
8
9
10
11
12
13
| 12016 First Campaign,1,2016
22016 First Campaign,2,2016
32016 First Campaign,3,2016
42016 First Campaign,4,2016
52016 Second Campaign,5,2016
62016 Second Campaign,6,2016
72016 Second Campaign,7,2016
82016 Third Campaign,8,2016
92016 Last Campaign,9,2016
102016 Last Campaign,10,2016
112016 Last Campaign,11,2016
122016 Last Campaign,12,2016
13 |
1
2
| 1 现在可以执行下面的脚本把2016年的促销期数据装载进月维度。
2 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| 1use rds;
2load data local inpath '/root/campaign_session.csv' overwrite into table campaign_session;
3
4use dw;
5drop table if exists tmp;
6create table tmp as
7select t1.month_sk month_sk,
8 t1.month month,
9 t1.month_name month_name,
10 t2.campaign_session campaign_session,
11 t1.quarter quarter,
12 t1.year year
13 from month_dim t1 inner join rds.campaign_session t2 on t1.year = t2.year and t1.month = t2.month;
14delete from month_dim where month_dim.month_sk in (select month_sk from tmp);
15insert into month_dim select * from tmp;
16
17select year,month,campaign_session from dw.month_dim;
18 |
1
2
| 1 查询结果如下图所示，2016年的促销期已经有数据，其它年份的campaign_session字段值为null。
2 |
2. 层次查询
下面的语句查询年-促销期-月层次。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| 1USE dw;
2
3SELECT product_category, time, order_amount, order_quantity
4 FROM
5(
6SELECT *
7 FROM
8(SELECT product_category,
9 year,
10 1 month,
11 year time,
12 1 sequence,
13 SUM(month_order_amount) order_amount,
14 SUM(month_order_quantity) order_quantity
15 FROM month_end_sales_order_fact a, product_dim b, month_dim c
16 WHERE a.product_sk = b.product_sk
17 AND a.order_month_sk = c.month_sk
18 AND year = 2016
19 GROUP BY product_category, year
20UNION ALL
21SELECT product_category,
22 year,
23 month,
24 campaign_session time,
25 2 sequence,
26 SUM(month_order_amount) order_amount,
27 SUM(month_order_quantity) order_quantity
28 FROM month_end_sales_order_fact a, product_dim b, month_dim c
29 WHERE a.product_sk = b.product_sk
30 AND a.order_month_sk = c.month_sk
31 AND year = 2016
32 GROUP BY product_category, year, month, campaign_session
33UNION ALL
34SELECT product_category,
35 year,
36 month,
37 month_name time,
38 3 sequence,
39 SUM(month_order_amount) order_amount,
40 SUM(month_order_quantity) order_quantity
41 FROM month_end_sales_order_fact a, product_dim b, month_dim c
42 WHERE a.product_sk = b.product_sk
43 AND a.order_month_sk = c.month_sk
44 AND year = 2016
45 GROUP BY product_category, year, quarter, month, month_name) t
46CLUSTER BY product_category, year, month, sequence) t;
47 |
3. 不完全层次
在一个或多个级别上没有数据的层次称为不完全层次。例如在特定月份没有促销期，那么月维度就具有不完全推广期层次。本小节说明不完全层次，还有在促销期上如何应用它。
下面是一个不完全促销期（在ragged_campaign.csv文件里）的例子，2016年1月、4月、6月、9月、10月、11月和12月没有促销期。
1
2
3
4
5
6
7
8
9
10
11
12
13
| 1,1,2016
22016 Early Spring Campaign,2,2016
32016 Early Spring Campaign,3,2016
4,4,2016
52016 Spring Campaign,5,2016
6,6,2016
72016 Last Campaign,7,2016
82016 Last Campaign,8,2016
9,9,2016
10,10,2016
11,11,2016
12,12,2016
13 |
1
2
| 1 下面的命令先把campaign_session字段置空，然后向month_dim表装载促销期数据。
2 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| 1use rds;
2load data local inpath '/root/ragged_campaign.csv' overwrite into table campaign_session;
3
4use dw;
5update month_dim set campaign_session = null;
6drop table if exists tmp;
7create table tmp as
8select t1.month_sk month_sk,
9 t1.month month,
10 t1.month_name month_name,
11 case when t2.campaign_session != '' then t2.campaign_session else t1.month_name end campaign_session,
12 t1.quarter quarter,
13 t1.year year
14 from month_dim t1 inner join rds.campaign_session t2 on t1.year = t2.year and t1.month = t2.month;
15delete from month_dim where month_dim.month_sk in (select month_sk from tmp);
16insert into month_dim select * from tmp;
17
18select year,month,campaign_session from dw.month_dim;
19 |
再次执行上面的层次查询语句，结果如下图所示。
在有促销期月份的路径，月级别行的汇总与促销期级别的行相同。而对于没有促销期的月份，其促销期级别的行与月级别的行相同。也就是说，在没有促销期级别的月份，月上卷了它们自己。例如，6月没有促销期，所以在输出看到了两个6月的行（第2行和第3行）。第3行是月份级别的行，第2行表示是没有促销期的行。对于没有促销期的月份，促销期行的销售订单金额（输出里的order_amount列）与月分行的相同。
