多层外键连接的文件计算实现 博客分类： DB 多层外键连接文件计算集算器

在结构化数据计算任务中，会出现源数据来自多层外键关联的多个数据表的情况。例如要在订单系统中计算出比较特殊的订单，就遇到了下图中的多层外键结构。具体的计算需求是：求产品供应商和订单的客户在同一个地区的订单，这些订单按照客户分组求每组的订单总价和个数。

  数据结构示意图如下：

 如果用数据库完成，需要的SQL脚本如下：

select cid,count(ordered),sum(price*quantity) from orders
left join customer on orders.cid=customer.cid
left join region a1 on customer.city=a1.city 
left join product on orders.pid=product.pid
left join supplier on product.pid=supplier.sid
left join region a2 on supplier.city=a2.city
where a1.region=a2.region and to_char(orderdate,’YYYY’)=?
Group by cid

 

 面来看一下采用集算器文件计算方案实现这个需求的方法。

  定义网格参数year，编写多层外键关联计算的集算脚本orders.dfx如下：

 

   A1：从本地文件建立订单表的文件游标，游标可以分段处理数据，防止出现内存溢出。

  A2：在A1中过滤一年的订单数据，year是预先定义好的网格参数。

  A3-A6：从本地读取维表的数据，依次是：客户、产品、供应商和地区。

  A7：将客户序表中的“city”键值切换成地区序表中对应的记录引用。

  A8：将供应商序表中的“city”键值切换成地区序表中对应的记录引用。

  A9：将产品序表中的“sid”键值切换成供应商序表中对应的记录引用。

  A10：将订单序表中的“pid”键值切换成产品序表中对应“pid”的记录引用。

  A11：将订单序表中的“cid”键值切换成客户序表中对应“cid”的记录引用。

  A12：在订单序表中，按照条件过滤。因为经过了A7-A11的切换，条件写为：pid.sid.city.region==cid.city.region。

  A13：在过滤结果的基础上分组汇总。

  A14：将A13返回给外部程序。

  如果事实表orders表数据量很大，可以采用集算器的多线程并行计算方案，以提高处理性能。其他的维表比较小，可以装入内存。多个线程之间可以共享维表。

  实现脚本如下：

 

   A1-A4：从本地读取维表的数据，依次是：客户、产品、供应商和地区。

  A5：将客户序表中的“city”键值切换成地区序表中对应的记录引用。

  B5：将供应商序表中的“city”键值切换成地区序表中对应的记录引用。

  C5：将产品序表中的“sid”键值切换成供应商序表中对应的记录引用。

  A6：设置并行数。

  A7：按照A6并行执行。

  B7：从本地文件建立订单表的文件游标，每个线程处理一部分数据。

  B8：将订单序表中的“pid”键值切换成产品序表中对应“pid”的记录引用。

  B9：将订单序表中的“cid”键值切换成客户序表中对应“cid”的记录引用。

  B10：在订单序表中，按照条件过滤。因为经过了A7-A11的切换，条件写为：pid.sid.city.region==cid.city.region。按照年份过滤。

  B11：分组汇总。

  B12：线程返回结果B11。

  A13：纵向合并A7。

  A14：再做一次分组汇总。

  A15：向外部程序返回结果A14。

  需要说明的是，集算脚本支持从数据库或者从文件中读取数据。因此，可以根据业务情况决定各个维表和事实表存储的位置，比如：数据不经常变动的存储在文件中，经常变动的存在数据库中。存储在文件系统中的数据越多，性能越好，数据库压力也越小。