多维分析后台实践 6：大事实表关联小维表
【摘要】
用实例、分步骤，详细讲解多维分析（OLAP）的实现。点击了解多维分析后台实践 6：大事实表关联小维表
实践目标
本期目标是在上期基础上，将客户宽表全部数据与分支机构等多个维表关联，并通过维表字段进行切片和分组计算。
实践的步骤：
1、 对客户表进行预处理，完成维度序号化计算。
2、 对维表进行预计算，完成维度之间的关联。
3、 关联宽表客户数据和维表，并计算切片和分组：用 Java 代码调用 SPL 实现。
各个维表和客户表的关联关系如下图：
维表包括：
1、 分支机构表 department
2、 地址表 locations
3、 国家表 countries
4、 区域表 regions
5、 贵宾表 vip
6、 信用评级表 credit_grade
维表字段和上期相同。
多维分析计算的目标可以用下面 Oracle 的 SQL 语句表示：
select v.interest_rate1,ct.country_id,c.job_id,sum(c.balance) sum,count(c.customer_id) count
from customer c
left join department d on c.department_id=d.department_id
left join locations l on d.location_id=l.location_id
left join countries ct on l.country_id=ct.country_id
left join regions r on r.region_id=ct.region_id
left join vip v on c.vip_id=v.vip_id
left join credit_grade cg on c.credit_grade=cg.credit_grade
where d.applause_rate in (26,30,33,65,67,74,75,77)
and r.region_name in ('Asia','Europe','Americas')
and v.charge_ratio in (9,11)
and cg.quota2 in(50000,80000)
and c.job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP')
and c.flag1='1' and c.flag8='1'
group by v.interest_rate1,ct.country_id,c.job_id
维度序号化
分支机构维表数据如下：
客户表中的 department_id 字段存储的是人为的编码（如下图），需要转换为分支机构表中的序号。
我们可以在 etl.dfx 中追加转换代码：
A |
|
1 |
=file("data/customer.ctx").open().cursor() |
2 |
=file("data/department.btx").import@b().keys(department_id).derive(#:num) |
3 |
=A1.switch(department_id,A2:department_id) |
4 |
=A3.new(department_id.num:department_id,job_num,employee_id,begin_date,customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8,vip_id,credit_grade) |
5 |
=file("data/customer亿 Dept.ctx").create@y(#department_id,#job_num,#employee_id,#begin_date,#customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8,vip_id,credit_grade).append(A4) |
A1：打开客户组表游标。
A2：读取分支机构表数据，以 department_id 作为主键，并新增计算列 num 保存序号。
A3：用客户组表游标关联 A2。
A4：在游标中，用 num 代替 department_id。
A5：将转换好的游标输出到 customerDept.ctx。如下图：
维表预关联
改写 init.dfx，在内存中加载维表数据并进行关联。代码如下：
A |
B |
|
1 |
=file("data/job.btx").import@ib() |
>env(job,A1) |
2 |
=file("data/department.btx").import@b().keys(department_id) |
=file("data/vip.btx").import@b().keys(vip_id) |
3 |
=file("data/credit_grade.btx").import@b().keys(credit_grade) |
=file("data/locations.btx").import@b().keys(location_id) |
4 |
=file("data/countries.btx").import@b().keys(country_id) |
=file("data/regions.btx").import@b().keys(region_id) |
5 |
=A2.switch(location_id,B3.switch(country_id,A4.switch(region_id,B4))) |
|
6 |
=env(department,A5) |
=env(vip,B2) |
7 |
=env(credit_grades,A3) |
|
A1：取出集文件中的职业类型数据，@i 表示只有一列时读成序列。
B1：存入全局变量 job。
A2-B4：分别取出集文件中的分支机构、vip、信用评级、地址、国家、区域数据，建立主键。
A5：把 department、locations、contries 和 regions 表按照层次关联起来。
A6：department 存入全局变量。B6：vip 存入全局变量。
A7：credit_grades 存入全局变量。
写好的 init.dfx 要放入节点机主目录，启动或重启节点机时会被自动调用。
关联计算
改写前面几期的 olap.dfx 和 customer.dfx。
输入参数仍然是两个：表名 arg_table 值为 customer，其他参数采用 json 格式，arg_json 样例如下：
{
aggregate:
[
{
func:"sum",
field:"balance",
alias:"sum"
},
{
func:"count",
field:"customer_id",
alias:"count"
}
],
group:
[
"vip_id.interest_rate1:vip_id_interest_rate1",
"department_id.location_id.country_id.country_id:department_id_location_id_country_id",
"job_id"
],
slice:
[
{
dim:"department.applause_rate",
interval:null,
value:[26,30,33,65,67,74,75,77]
},
{
dim:"department.location_id.country_id.region_id.region_name",
value:["Asia","Europe","Americas"]
},
{
dim:"vip.charge_ratio",
value:[9,11]
},
{
dim:"credit_grades.quota2",
value:[50000,80000]
},
{
dim:"job_id",
value:["AD_VP","FI_MGR","AC_MGR","SA_MAN","SA_REP"]
},
{
dim:"flag1",
value:"1"
},
{
dim:"flag8",
value:"1"
}
]
}
其中，group 和 slice 中出现了 department_detail.location_id 和 department_detail.applause_rate 等。多维分析计算时要先将过滤条件转换为客户表自身的字段条件，做游标前过滤，再关联客户表和维表计算。
department_detail.location_id.country_id.country_id:department_detail_location_id_country_id 中的冒号是给外键属性化字段取一个别名，用做最后结果的字段名。
第一步：改写 customer.dfx。
在改写 olap.dfx 之前，先改写 customer.dfx。改变处理 arg_json 参数的方法，SPL 代码如下：
A |
B |
C |
D |
|
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 |
func |
|||
7 |
=[] |
=A6.select(like(dim,B6/".*")) |
||
8 |
for C7 |
=[func(A1,B8)] |
||
9 |
>B7|=C8 |
|||
10 |
=${B6}.pselect@a(${B7.concat(" &&")}) |
=A6\C7 |
||
11 |
=C10.create().record([C6,null,B10]) |
|||
12 |
return B11|C10 |
|||
13 |
=json(arg_json) |
=date("2000-01-01") |
||
14 |
=A13.aggregate.(~.func/"("/~.field/"):"/~.alias).concat@c() |
|||
15 |
=A13.group.(if(~=="job_id","job_num",~)) |
|||
16 |
=A15.(if(~=="begin_yearmonth","begin_date\\100:begin_yearmonth",~)).concat@c() |
|||
17 |
=A13.aggregate.(field) |
=A15.(if(~=="begin_yearmonth","begin_date",~)) |
=(A17|B17).id() |
|
18 |
=[] |
|||
19 |
for C17 |
if like(A19,"department_id.*") |
>C17(#A19)="department_id" |
>A18|=["department_id,department:#"] |
20 |
if like(A19,"vip_id.*") |
>C17(#A19)="vip_id" |
>A18|=["vip_id,vip:#"] |
|
21 |
if like(A19,"credit_grade.*") |
>C17(#A19)="credit_grade" |
>A18|=["credit_grade,credit_grade:#"] |
|
22 |
=C17.id().concat@c() |
|||
23 |
=func(A6,A13.slice,"vip","vip_id") |
=func(A6,A23,"credit_grades","credit_grade") |
=func(A6,B23,"department","department_id") |
|
24 |
=[] |
|||
25 |
for C23 |
if A25.dim=="begin_date" && A25.value!=null |
>A25.value=int(interval@m(C13,eval(A25.value))*100+day(eval(A25.value))) |
|
26 |
else if A25.dim=="begin_date" && A25.value==null |
=A25.interval.(~=int(interval@m(C13,eval(~))*100+day(eval(~)))) |
||
27 |
else if A25.dim=="job_id" |
>A25.dim="job_num" |
||
28 |
>A25.value=A25.value.(job.pos@b(~)) |
|||
29 |
else if like(A25.dim, "flag?") |
>A25.value=int(A25.value) |
||
30 |
=[func(A1,A25)] |
>A24|=B30 |
||
31 |
=A13.group.(~.split(":")).(~=~.m(-1))|A13.aggregate.(alias) |
=A31(A31.pos("job_id"))="job(job_num):job_id" |
||
32 |
=A31(A31.pos("begin_yearmonth"))="month@y(elapse@m(date(\""/C13/"\"),begin_yearmonth)):begin_yearmonth" |
|||
33 |
=A31(A31.pos("begin_date"))="elapse@m(B4,begin_date\\100)+(begin_date%100-1):begin_date" |
|||
34 |
return A22,A14,A16,A24.concat("&& "),A31.concat@c(),A18.concat(";") |
|||
A1 到 C5 是子程序，在调用的时候才会执行。代码与前面几期完全一致，没有改变。
A6 到 C12 是新增的子程序，在下面调用的时候介绍。
A12：将 arg_json 解析成序表。解析的结果是多层嵌套的序表，如下图：
其中的 aggregate 为：
其中的 group 为：
其中的 slice 为：
A14：先将 aggregate 计算成冒号相连的字符串序列，再将序列用逗号连接成一个字符串：sum(balance):sum,count(customer_id):count，也就是聚合表达式。
A15：将 group 中的 job_id 替换为 job_num。
A16：将 group 中的 begin_yearmonth 转换为整数计算。
A17-C17：计算需要从组表中读取的字段名。计算结果如下：
其中有关联维表的字段，需要转换为组表自身的字段。
A18：定义一个空序列，准备存放需要关联的维表和字段。
A19：循环计算 C17，循环体是 B19 到 C21。
B19：如果 A19 包含 "department_id.*"，那么就需要关联分支机构序表。
C19：将 C17 的相应成员赋值为 "department_id"。
D19：A18 中增加一个成员 "department_id,department:#"。客户表 department_id 预先已经做了维表序号化，所以这里可以直接写 #。
B20-D20：同样处理 vip 维表。
B21-D21：同样处理 credit_grade 维表。
A19 循环结束，此时 A18 计算结果如下：
A22：对处理之后的 C17 去重，用逗号连接为字符串 "balance,customer_id,department_id,job_num,vip_id"。
A23：以 A13.slice、维表 "vip" 和维表字段 "vip_id" 为输入参数，调用 A6 子程序。
三个参数分别存入 A6、B6、C6 中。A6 如下图：
此时 A6 的第三个成员为 vip.charge_ratio，子程序的目标是将其计算为组表自身字段 vip_id 的过滤条件。
B7：准备一个空序列，存放 vip 有关的过滤条件。
C7：过滤出 vip 有关的过滤条件，如下：
B8：开始循环计算 C7。循环体是 C8 到 C9。
C8：调用 A1，将 B8 计算为集算器表达式：
C9：将 C8 合并到 B7 中：
B10：用 B7 中所有的条件来过滤 vip 维表，得到 vip 维表的序号：
C10：将 A6 中去掉 C7：
B11：C10 创建新纪录：
B12：合并 B11 和 C10 返回结果，结束 A6 子程序。
A23：子程序返回后，A23 序列中 vip 有关的过滤条件都转换成客户表 vip_id 字段的条件了，如下图：
B23：用同样的办法将 A23 中的 credit_grades.* 条件，转换为客户表 credit_grade 字段的条件。
C23：用同样的办法将 A23 中的 department.* 条件，转换为客户表 department_id 字段的条件。
A24：定义一个空序列，准备存放切片（过滤条件）表达式。
A25：循环计算 slice，循环体是 B25 到 C30。其中：B25 到 C29 是对 slice 的 value 或者 interval 做性能优化的转换。
B25：如果 A25 的 dim 是 begin_date 并且 value 不是空，也就是 begin_date 等于单一日期的情况。此时 C25 要将单一日期转换为整数赋值给 value。
B26：如果 A25 的 dim 是 begin_date 并且 value 是空，也就是 begin_date 在一个范围内取值的情况。此时 C25 要将起止日期转换为整数赋值给 interval。
B27：如果 A10 的 dim 是 job_id，也就是 job_id 取枚举值的情况。例如：["AD_VP","FI_MGR","AC_MGR","SA_MAN","SA_REP"].contain(job_id)。此时 C27 要将 A10 的 dim 改为 job_num。C28 要将 A25 的 value 枚举值转换为在全局变量 job 序列中的位置，也就是 job_num 整数序列，例如：[5,7,2,15,16]。
B29：如果 A25 的 dim 是 flag1、flag2…flag8，也就是标志位等于 "1" 或者 "0" 的情况。此时 C29 要将 A25 的 value 值从字符串转化为整数。
B30：用 B25 到 C29 对 slice 的 value 或者 interval 做性能优化转换结果作为参数，调用子程序 A1。
子程序 A1（B2 到 B5），和第一篇 customer.dfx 的 func 代码相同，不再赘述。
C30：func A1 的返回结果追加到 A24 中。继续 A25 中的循环，到循环结束，就准备好了切片表达式的序列。
A31：准备结果集显示值转换的表达式。将 A13.group.(~.split(":")).(~=~.m(-1)) 和 A13.aggregate.alias 序列合并，如下图：
A13.group.(~.split(":")).(~=~.m(-1)) 是将 group 中有冒号的，取冒号后面的别名，否则直接用 group 的值。
C31：将 A31 中的 job_id 替换成转换语句。语句的作用是：将结果集中的 job_num 转换为 job_id。
A32-A33：生成表达式，用于计算结果中的 begin_date 和 begin_yearmonth 从整数转换为日期。
A34：返回各个表达式字符串，依次是：
需要的字段：balance,customer_id,department_id,job_num,vip_id
聚合表达式：sum(balance):sum,count(customer_id):count
分组表达式：
vip_id.interest_rate1:vip_id_interest_rate1,department_id.location_id.country_id.country_id:department_id_location_id_country_id,job_num
切片表达式：
[2].contain(department_id)
&& [5,8].contain(credit_grade)
&& [2].contain(vip_id)
&& [5,7,2,15,16].contain(job_num)
&& flag1==1 && flag8==1
结果集显示值转换表达式：
vip_id_interest_rate1,department_id_location_id_country_id,job(job_num):job_id,sum,count
外键关联表达式：
department_id,department:#;vip_id,vip:#
第二步：改写 olap.dfx。
网格参数是 arg_table（值为 customer）和 arg_json。SPL 代码如下：
A |
|
1 |
=call(arg_table/".dfx",arg_json) |
2 |
=file("data/"/arg_table/"Dept.ctx").open() |
3 |
=A2.cursor@m(${A1(1)};${A1(4)};2) |
4 |
=A3.switch(${A1(6)}) |
=A4.groups(${A1(3)};${A1(2)}) |
|
=A5.new(${A1(5)}) |
|
return A6 |
A1：根据 arg_table 参数的值调用 customer.dfx，调用参数是 arg_json，返回值是 customer.dfx 返回的各个表达式字符串。
A2：根据 arg_table 参数，打开组表文件。
A3：对 A2 建立带过滤条件的游标。实际执行的语句是：
= A2.cursor@m(balance,customer_id,department_id,job_num,vip_id; [4,5,6,12,16,18,22,23,24,27].contain(department_id) && [5,8].contain(credit_grade) && [2].contain(vip_id) && [5,7,2,15,16].contain(job_num) && flag1==1 && flag8==1;2)
A4：将游标关联内存中的维表。实际执行的语句是：
=A3.switch(department_id,department:#;vip_id,vip:#)
A5：对关联之后的游标，做小结果集分组计算。实际执行的语句是：
=A4.groups(vip_id.interest_rate1:vip_id_interest_rate1,department_id.location_id.country_id.country_id:department_id_location_id_country_id,job_num;sum(balance):sum,count(customer_id):count)，执行结果如下：
A6：将分组结果转换为显示值。实际执行的语句是：=A5.new(vip_id_interest_rate1,department_id_location_id_country_id,job(job_num):job_id,sum,count)，执行结果：
A7：返回计算结果。
第三步：用 Java 代码调用
olapMem.dfx 编写好之后，可以在多维分析中作为存储过程调用，Java 代码和前面几期大部分相同，只是 dfx 文件名称、arg_table 和 arg_json 值不同。Java 代码如下：
public void testOlapServer(){
Connection con = null;
java.sql.PreparedStatement st;
try{
// 建立连接
Class.forName("com.esproc.jdbc.InternalDriver");
// 根据 url 获取连接
con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true&sqlfirst=plus");
// 调用存储过程，其中 olap 是 dfx 的文件名
st =con.prepareCall("call olap(?,?)");
st.setObject(1, "newCustomer");
st.setObject(2, "{aggregate:[{func:\"sum\",field:\"balance\",alias:\"sum\"},{func:\"count\",field:\"customer_id\",alias:\"count\"}],group:[\"vip_id.interest_rate1:vip_id_interest_rate1\",\"department_id.location_id.country_id.country_id:department_id_location_id_country_id\",\"job_id\"],slice:[{dim:\"department.applause_rate \",interval:null,value:[26,30,33,65,67,74,75,77]},{dim:\"department.location_id.country_id.region_id.region_name\",value:[\"Asia\",\"Europe\",\"Americas\"]},{dim:\"vip.charge_ratio\",value:[9,11]},{dim:\"credit_grades.quota2\",value:[50000,80000]},{dim:\"job_id\",value:[\"AD_VP\",\"FI_MGR\",\"AC_MGR\",\"SA_MAN\",\"SA_REP\"]},{dim:\"flag1\",value:\"1\"},{dim:\"flag8\",value:\"1\"}]}");//arg_json
// 执行存储过程
st.execute();
// 获取结果集
ResultSet rs = st.getResultSet();
// 继续处理结果集，将结果集展现出来
}
catch(Exception e){
out.println(e);
}
finally{
// 关闭连接
if (con!=null) {
try {con.close();}
catch(Exception e) {out.println(e); }
}
}
}
Java 代码和 SPL 执行的总时间是 3 秒。
英文版