【摘要】
用实例、分步骤，详细讲解多维分析（OLAP）的实现。点击了解多维分析后台实践 4：预汇总和冗余排序

实践目标

本期目标，是在前期基础上实现预汇总和冗余排序，继续提升计算速度。

实践的步骤：

1、 预处理数据：在已有宽表基础上，按照计算需要实现“部分预汇总”和“切片维度的冗余排序”。

2、 多维分析计算：利用预处理的结果，实现多维分析计算。

本期基础数据是上期形成的宽表 customer.ctx，用集算器的数据文件工具查看如下：

多维分析计算的主要目标不变，用下面 Oracle 的 SQL 语句表示：

SQL1：

select department_id,job_id,to_char(begin_date,'yyyymm') begin_month ,sum(balance) sum,count(customer_id) count

from customer

where department_id in (10,20,50,60,70,80)

and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP')

and begin_date>=to_date('2002-01-01','yyyy-mm-dd')

and begin_date<=to_date('2020-12-31','yyyy-mm-dd')

and flag1='1' and flag8='1'

group by department_id,job_id,to_char(begin_date,'yyyymm')

针对 SQL1，要在预处理数据时，生成包含所有分组字段和切片字段的 cube 文件 cu_1。在用集算器实现 SQL1 对应的多维分析计算时，可以正好用上 cu_1 来提高性能。

 

在 SQL1 基础上增加两个计算目标，用来验证预汇总的效果：

SQL2：

select department_id,job_id,sum(balance) sum,count(customer_id) count

from customer

where department_id in (10,20,50,60,70,80)

and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP')

and begin_date>=to_date('2002-01-01','yyyy-mm-dd')

and begin_date<=to_date('2020-12-31','yyyy-mm-dd')

and flag1='1' and flag8='1'

group by department_id,job_id

用集算器实现 SQL2 对应的多维分析计算时，分组字段比 cu_1 少了一个 begin_yearmonth, 不能用 cu_1 直接得到结果，还要在 cu_1 的基础上再次汇总。

 

SQL3：

select department_id,job_id,to_char(begin_date,'yyyymm') begin_month ,sum(balance) sum,count(customer_id) count

from customer

where department_id in (10,20,50,60,70,80)

and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP')

and begin_date>=to_date('2002-01-01','yyyy-mm-dd')

and begin_date<=to_date('2020-12-31','yyyy-mm-dd')

and flag1='1' and flag8='1'

and flag3='1' and flag6='1'

group by department_id,job_id,to_char(begin_date,'yyyymm')

用集算器实现 SQL3 对应的多维分析计算时，切片字段比 cu_1 多了 flag3 和 flag6, 不能用 cu_1 了，只能在原宽表 customer.ctx 上做汇总。

 

除此之外，再增加两个计算目标 SQL4、SQL5，分别用来验证冗余排序和时间段预汇总的效果。

SQL4：

Select department_id,job_id,to_char(begin_date,'yyyymm') begin_month,department_id,job_id, sum(balance) sum,count(customer_id) count

from customer

where begin_date>=to_date('2006-01-01','yyyy-mm-dd')

and begin_date<=to_date('2006-01-12','yyyy-mm-dd')

group by to_char(begin_date,'yyyymm') ,department_id,job_id

 

SQL5：

Select sum(balance) sum,count(customer_id) count

from customer

where begin_date>=to_date('2002-01-01','yyyy-mm-dd')

and begin_date<=to_date('2020-12-31','yyyy-mm-dd')

 

预处理数据

一、部分预汇总

改写 etl.dfx，在最后加入下面的代码：

	A
1	=file("data/customer.ctx").open()
2	=A1.cuboid(cu_1,department_id,job_num,begin_date\100,begin_date,flag1,flag8;sum(balance):sum,count(customer_id):count)

A1：打开组表文件。

A2：为组表文件建立部分预汇总的 cube 文件 cu_1。注意：切片字段和分组字段都要包含，也要包含 begin_date\100 这样的表达式。

 

每天产生新增数据时，我们需要追加到组表中。无论是按照第一期直接 append 追加数据，还是按照第三期建立补文件，每月重整的方式追加数据，集算器都会自动更新 cube 文件。

 

生成的 cube 文件和组表文件 customer.ctx 在同一目录中，文件名为：customer.ctx__CUBOID@cu_1。当组表文件有 1 亿条数据时，cube 文件大小为 16M。

 

二、冗余排序

改写 etl.dfx，在最后加入下面的代码：

	A	B
1	=file("data/customer.ctx").open().cursor()
2	=A1.sortx(begin_date,department_id,job_num,   employee_id,customer_id)
3	=A2.new(begin_date,department_id,job_num,employee_id,customer_id   ,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8)
4	=file("data/customer_begin_date.ctx").create@y(#begin_date,#department_id,#job_num,#employee_id,#customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8)
5	=A4.append(A3)	>A4.close()

A1：建立组表文件的游标。

A2：对组表文件，按照 begin_date,department_id,job_num, employee_id,customer_id，外存排序。

A3：对游标字段调整顺序。

A4：新建一个组表文件 customer_begin_date.ctx，排序字段是 begin_date,department_id,job_num, employee_id,customer_id。

A5：将排好序的数据输出到新组表文件中。B5：关闭新组表文件。

 

当组表文件有 1 亿条数据时，冗余文件大小是 2.4G，和原组表 customer.ctx 大小基本相同。

 

三、时间段预汇总

时间段预汇总需要利用日期型字段，因此 begin_date 就不能预处理成小整数了。要用 customer_begin_date.ctx 生成一个新的 customerDate.ctx，begin_date 还原成日期型，其他字段不变。

改写 etl.dfx，在最后加入下面的代码：

	A
1	=file("data/customerDate.ctx").open()
2	=A1.cuboid(cu_date_year_month,month@y(begin_date);sum(balance),count(customer_id))
3	=A1.cuboid(cu_date_year,year(begin_date);sum(balance),count(customer_id))

A1：打开组表。

A2：建立 cube 名为 cu_date_year_month，采用年月为时间段预汇总的汇总维度。

A3：建立 cube 名为 cu_date_year，采用年为时间段预汇总的汇总维度。

多维分析计算

一、部分预汇总

按照 SQL1 的需求改写 olap.dfx，将普通的分组计算改为部分预汇总计算。

olap.dfx 参数是 arg_table 和 arg_json 不变。

SPL 代码修改如下：

	A
1	=call(arg_table/".dfx",arg_json)
2	=file("data/"/arg_table/".ctx").open()
3	=A2.cgroups@m(${A1(3)};${A1(2)};${A1(4)})
4	=A3.new(${A1(5)})
5	return A4

除 A3 之外的代码不变。

A3：用预先生成的 cube 文件对组表计算条件过滤和小结果集分组汇总，这里没有指定 cube 的名字，集算器会自动选择最适合的 cube，也就是 cu_1。@m 选项是指多线程并行计算，函数的最后一个参数 2 是指 2 线程。

实际执行的语句是：

=A2.cgroups(department_id,job_num,begin_date\100:begin_yearmonth; sum(balance):sum,count(customer_id):count; [10,20,50,60,70,80].contain(department_id) && [5,7,2,15,16].contain(job_num) && between(begin_date,2401:25131) && flag1==1 && flag8==1;2)

 

Java 代码和上期相比也没有变化。Java 代码加上后台计算返回结果总的执行时间，和前期比较如下：

期数	单线程	二线程并行	备注
第一期	84秒	42秒
第二期	31 秒	14 秒
第三期	9秒	5秒
第四期	0.1 秒	0.1 秒	部分预汇总

通过上表的对比可以看出，部分预汇总对计算性能提升非常明显。

 

对于 SQL2 来说，上述代码无需改动，只要把参数 arg_json 的 group 改为：

              group:

              [

                     "department_id",

                     "job_id"

              ]

集算器计算时，要在 cu_1 的基础上再次汇总。

 

对于 SQL3 来说，也不需要改动代码，只要把参数 arg_json 的 slice 增加两个条件：

              slice:

              [

                     {

                            dim:"department_id",

                            value:[10,20,50,60,70,80]

                     },

                     {

                            dim:"job_id",

                      value:["AD_VP","FI_MGR","AC_MGR","SA_MAN","SA_REP"]

                     },

                     {

                            dim:"begin_date",

                            interval:[date("2002-01-01"),date("2020-12-31")]

                     },

                     {

                            dim:"flag1",

                            value:"1"

                     },

                     {

                            dim:"flag3",

                            value:"1"

                     },

                     {

                            dim:"flag6",

                            value:"1"

                     },

                     {

                            dim:"flag8",

                            value:"1"

                     }

              ]

集算器计算时，因为有两个字段 flag3、flag6 不在 cu_1 中，所以不能利用 cu_1，只能用原宽表来做过滤汇总计算。

 

SQL1、SQL2、SQL3 对应集算器 SPL 代码执行时间对比如下：

期数	单线程	二线程并行	备注
SQL1	0.1秒	0.1秒
SQL2	0.1 秒	0.1 秒
SQL3	4秒	4秒

cu_1本身较小，所以在其基础上再汇总执行时间变化不大，所以 SQL1 和 SQL2 时间差不多。SQL3 无法使用 cu_1，要用原组表做计算，所以时间比较长。可以通过增加 cu_1 字段，或者再建立新的 cube 文件 cu_2 来进一步提速。

需要说明的是：因为 SQL3 增加了两个过滤条件，从组表中取出数据较少，所以比第三期计算时间短。

 

组表文件和 cube 文件大小对比如下：

文件名	文件大小	备注
组表	2.4GB
cube 文件	16MB

从上表中可以看出，cube 文件相对组表来说是比较小的。Cube 文件多占用的存储空间并不大，却能换取显著的计算性能提升，是比较值得的。因此，我们可以根据多维分析系统的实际情况，针对出现频次较多的计算，在硬盘允许的条件下尽可能多的建立 cube 文件，实现空间换时间。

 

二、冗余排序

按照 SQL4 的需求调整网格参数 arg_json 中的 slice：

       slice:

              [

                     {

                            dim:"begin_date",

                            interval:[date("2006-01-01"),date("2006-01-12")]

                     }

              ]

按照 SQL4 的需求改写 customer.dfx，增加一个返回参数，也就是冗余排序组表的文件名称。

	A	B	C
1	func
2		if A1.value==null	return   "between("/A1.dim/","/A1.interval(1)/":"/A1.interval(2)/")"
3		else if ifa(A1.value)	return   string(A1.value)/".contain("/A1.dim/")"
4		else if   ifstring(A1.value)	return A1.dim/"==\""/A1.value/"\""
5		else	return   A1.dim/"=="/A1.value
6	=json(arg_json)		=date("2000-01-01")
7	=A6.aggregate.(~.func/"("/~.field/"):"/~.alias).concat@c()
8	=A6.group.(if(~=="job_id","job_num",~))
9	=A8.(if(~=="begin_yearmonth","begin_date\\100:begin_yearmonth",~)).concat@c()
10	=A6.aggregate.(field)		=A8.(if(~=="begin_yearmonth","begin_date",~))
11	=(A10|C10).id().concat@c()
12	=[]
13	for A6.slice	if   A13.dim=="begin_date" && A13.value!=null	>A13.value=int(interval@m(C6,A13.value)*100+day(A13.value))
14		else if   A13.dim=="begin_date" && A13.value==null	=A13.interval.(~=int(interval@m(C6,eval(~))*100+day(eval(~))))
15		else if   A13.dim=="job_id"	>A13.dim="job_num"
16			>A13.value=A13.value.(job.pos@b(~))
17		else if like(A13.dim,   "flag?")	>A13.value=int(A13.value)
18		=[func(A1,A13)]	>A12|=B18
19	=A6.group|A6.aggregate.(alias)		=A19(A19.pos("job_id"))="job(job_num):job_id"
20	=A19(A19.pos("begin_yearmonth"))="month@y(elapse@m(date(\""/C6/"\"),begin_yearmonth)):begin_yearmonth"
21	=A19(A19.pos("begin_date"))="elapse@m(B4,begin_date\\100)+(begin_date%100-1):begin_date"
22	=if(A6.slice.(dim).pos("begin_date")   &&   !A6.slice.(dim).pos("department_id"),"data/customer_begin_date.ctx","data/customer.ctx")
23	return A11,A7,A9,A12.concat("  &&"),A19.concat@c(),A22

A1-A21 不变。

A22：判断 arg_json 参数中的切片 slice，如果有 begin_date 维度并且没有 department_id 维度，那么就返回冗余排序组表文件名 customer_begin_date.ctx。否则还返回原组表文件名 customer.ctx。

A23：增加一个返回参数，返回 A22 中组表的文件名。

 

按照 SQL4 的需求改写 olap.dfx，将普通的分组计算改为冗余排序汇总计算。

olap.dfx 参数是 arg_table 和 arg_json。

SPL 代码修改如下：

	A
1	=call(arg_table/".dfx",arg_json)
2	=file(A1(6)).open()
3	=A2.cursor@m(${A1(1)};${A1(4)};2)
4	=A3.groups(${A1(3)};${A1(2)})
5	=A4.new(${A1(5)})
6	return A5

只有 A2、A3 有变化，其他不变。

A2: 用 customer.dfx 返回的第六个参数，也就是文件名来打开组表文件。如果切片字段只有 begin_date，没有 departmen_id，这里实际执行的语句是：

=file("data/customer_begin_date.ctx").open()

否则就执行：

=file("data/customer.ctx").open()

 

A3：实际执行的语句是：

=A2.cursor@m(department_id,job_num,begin_date\100:begin_yearmonth; sum(balance):sum,count(customer_id):count; between(begin_date,7201:7212));2)

 

Java 代码和上期相比只需要改变参数 arg_json 中的 slice，其他部分没有变化。Java 代码加上后台计算返回结果总的执行时间，和不做冗余排序的情况比较如下：

期数	单线程	二线程并行	备注
无冗余排序	2.3秒	1.5秒
冗余排序	0.2 秒	0.2 秒

通过上表的对比可以看出，冗余排序对计算性能有明显提升。

 

冗余排序的适用范围：

1、 在宽表列存的情况下，如果切片字段在排序字段比较靠后的位置，比较适合冗余排序。

例如：宽表 customer.ctx 的排序字段是 department_id,job_num,employee_id,begin_date,customer_id，这时候用 begin_date 做切片字段需要遍历 begin_date 列，计算速度较慢。而冗余排序组表 customer_begin_date.ctx 的排序字段是 begin_date,employee_id,department_id,job_num,customer_id，切片字段 begin_date 是组表排序的第一个维度，此时用 begin_date 做切片时不需要遍历 begin_date 列，计算速度会快很多。

2、 在满足上一条的情况下，切片时满足过滤条件的记录数越少，冗余排序的效果会更明显。例如：begin_date 的日期过滤条件如果是 2000 年到 2021 年，那么宽表中的数据基本都符合这个条件，这样的切片计算耗时和遍历也差不多了，冗余排序的效果就不会很明显。缩小日期范围时，如果切片条件能过滤掉 90% 以上的数据，冗余排序的效果会比较明显。

 

三、时间段预汇总

按照 SQL5 的需求调整网格参数 arg_json：

'{

       aggregate:

              [

                     {

                            func:"sum",

                            field:"balance",

                            alias:"sum"

                     },

                     {

                            func:"count",

                            field:"customer_id",

                            alias:"count"

                     }

              ],

       group:

              [

              ],

       slice:

              [

                     {

                            dim:"begin_date",

                            interval:[date("2002-01-01"),date("2020-12-31")],

                            value:null

 

                     }

              ]

}

重新编写一个 customerDate.dfx，处理 arg_json，代码如下：

	A	B	C
1	func
2		if A1.value==null	return "between("/A1.dim/","/A1.interval(1)/":"/A1.interval(2)/")"
3		else if ifa(A1.value)	return   string(A1.value)/".contain("/A1.dim/")"
4		else if   ifstring(A1.value)	return   A1.dim/"==\""/A1.value/"\""
5		else	return   A1.dim/"=="/A1.value
6	=json(arg_json)
7	=A6.aggregate.(~.func/"("/~.field/"):"/~.alias).concat@c()
8	=A6.group.(if(~=="job_id","job_num",~))
9	=A8.(if(~=="begin_yearmonth","begin_date\\100:begin_yearmonth",~)).concat@c()
10	=A6.aggregate.(field)		=A8.(if(~=="begin_yearmonth","begin_date",~))
11	=(A10|C10).id().concat@c()
12	=[]
13	for A6.slice	if   A13.dim=="job_id"	>A13.dim="job_num"
14			>A13.value=A13.value.(job.pos@b(~))
15		else if like(A13.dim,   "flag?")	>A13.value=int(A13.value)
16		=[func(A1,A13)]	>A12|=B16
17	=A6.group|A6.aggregate.(alias)			=A17(A17.pos("job_id"))="job(job_num):job_id"
18	=if(A6.slice.(dim).pos("begin_date")   &&   !A6.slice.(dim).pos("department_id"),"data/5customer_test1亿 _date.ctx","data/4customer_test1 亿.ctx")
19	return   A11,A7,A9,A12.concat("&&"),A17.concat@c(),A18

与前面的 customer.dfx 相比，去掉了将 begin_date 从日期型转为整数，最后再转换为日期型的代码，其他不变。

 

按照 SQL5 的需求改写 olap.dfx，将普通的分组计算改为时间段预汇总计算。

olap.dfx 参数是 arg_table（值为 customerDate）和 arg_json。

SPL 代码修改如下：

	A
1	=call(arg_table/".dfx",arg_json)
2	=file(A1(6)).open()
3	=A2.cgroups@m(${A1(3)};${A1(2)};${A1(4)};2)
4	=A3.new(${A1(5)})
5	return A4

除A2，A3 之外的代码不变。

A2: 用 customerDate.dfx 返回的第六个参数，也就是文件名来打开组表文件。如果切片字段只有 begin_date，没有 departmen_id，这里实际执行的语句是：

=file("data/customerDate.ctx").open()

否则就执行：

=file("data/customer.ctx").open()

A3：用预先生成的时间段预汇总 cube 文件对组表计算全表汇总，这里没有指定 cube 的名字，集算器会自动选择最适合的 cube，也就是 cu_date_year。

实际执行的语句是：

=A2.cgroups@m(;sum(balance):sum,count(customer_id):count;between(begin_date, date("2001-01-01"):date("2020-12-31"));2)

 

Java 代码和上期相比也没有变化。Java 代码加上后台计算返回结果总的执行时间，和不使用时间段预汇总比较如下：

计算方法	单线程	二线程并行	备注
无时间段预汇总	29秒	18秒
时间段预汇总	0.1 秒	0.1 秒

通过上表的对比可以看出，时间段预汇总对计算性能提升也非常明显。

组表文件和 cube 文件大小对比如下：

文件名	文件大小	备注
组表	2.4GB
cube 文件	16MB	年
cube文件	16MB	年月