基于Hadoop生态圈的数据仓库实践 —— 进阶技术（六）

六、维度层次
大多数维度都具有一个或多个层次。例如，日期维度就有一个四级层次：年、季度、月和日。这些级别用date_dim表里的列来表示。日期维度是一个单路径层次，因为除了年-季度-月-日这条路径外，它没有任何其它层次。本节讨论在维度的层次上进行分组和钻取查询。多路径层次在下一节“多路径和参差不齐的层次”中讨论。
为了识别数据仓库里一个维度的层次，首先要理解维度中列的含义，然后识别两个或多个列是否具有相同的主题。例如，日、月、季度和年具有相同的主题因为它们都是关于日期的。具有相同主题的列形成一个组，组中的一列必须包含至少一个组内的其它成员，例如，在前面提到的组中，月包含日。这些列的链条形成了一个层次。例如，日-月-季度-年这个链条是一个日期维度的层次。除了日期维度，产品和客户维度也有层次。
下表显示了三个维度的层次。注意客户维度具有两个路径的层次。

分组和钻取查询
可以在层次上进行分组和钻取查询。分组查询是把度量按照一个维度的一个或多个级别进行分组。下面的脚本是一个分组查询的例子。这个查询按产品（product_category列）和日期维度的三个层次级别（year、quarter和month列）分组返回销售金额。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1USE dw;  

2  

3SELECT product_category,  

4       year,  

5       quarter,  

6       month,  

7       SUM(order_amount) sum_order_amount

8  FROM sales_order_fact a,  

9       product_dim b,  

10       date_dim c  

11 WHERE a.product_sk = b.product_sk  

12   AND a.order_date_sk = c.date_sk  

13GROUP BY product_category , year , quarter , month  

14CLUSTER BY product_category , year , quarter , month;

15

1
2
1        查询结果如下图所示。分组查询的输出显示了每一行的度量（销售订单金额）都沿着年-季度-月的层次分组。  

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
48
49
50
51
52
53
54
55
56
57
1USE dw;  

2

3-- 使用union all 

4SELECT product_category, time, order_amount

5  FROM

6(

7SELECT product_category, 

8       case when sequence = 1 then concat('year: ', time)

9            when sequence = 2 then concat('quarter: ', time)

10            else concat('month: ', time)

11       end time,

12       order_amount, 

13       sequence, 

14       date

15  FROM 

16(

17SELECT product_category, min(date) date, year time, 1 sequence, SUM(order_amount) order_amount

18  FROM sales_order_fact a, product_dim b, date_dim c  

19 WHERE a.product_sk = b.product_sk  

20   AND a.order_date_sk = c.date_sk  

21 GROUP BY product_category , year

22 UNION ALL 

23SELECT product_category, min(date) date, quarter time, 2 sequence, SUM(order_amount) order_amount

24  FROM sales_order_fact a, product_dim b, date_dim c  

25 WHERE a.product_sk = b.product_sk  

26   AND a.order_date_sk = c.date_sk  

27 GROUP BY product_category , year , quarter 

28 UNION ALL 

29SELECT product_category, min(date) date, month time, 3 sequence, SUM(order_amount) order_amount

30  FROM sales_order_fact a, product_dim b, date_dim c  

31 WHERE a.product_sk = b.product_sk  

32   AND a.order_date_sk = c.date_sk  

33 GROUP BY product_category , year , quarter , month) x

34 CLUSTER BY product_category , date , sequence , time) y; 

35 

36-- 使用grouping__id函数

37SELECT product_category, time, order_amount

38  FROM

39(

40SELECT product_category, 

41       case when gid = 3 then concat('year: ', year)

42            when gid = 7 then concat('quarter: ', quarter)

43            else concat('month: ', month)

44       end time,

45       order_amount,

46       gid,

47       date

48  FROM

49(

50SELECT product_category, year, quarter, month, min(date) date, SUM(order_amount) order_amount, CAST(grouping__id AS INT) gid

51  FROM sales_order_fact a, product_dim b, date_dim c  

52 WHERE a.product_sk = b.product_sk  

53   AND a.order_date_sk = c.date_sk 

54 GROUP BY product_category , year , quarter , month with rollup

55) x WHERE gid > 1

56 CLUSTER BY product_category , date , gid , time) y;

57

        查询结果如下图所示。