复杂多数据源报表join后再计算的简便方法

         复杂数据源是报表开发的常见问题，比如不同数据库表先进行join运算，再进行后续的过滤分组排序等运算。JasperReport/Birt等报表工具有virtual data source或table join，可以一定程度地实现多数据源join后计算，但掌握起来并不容易。

集算器具有结构化强计算引擎，支持多样性数据源，集成简单，可以协助报表工具方便地实现此类需求，下面通过一个例子来说明多数据源join后计算的过程。

         Sales是mysql数据库中的表，存储着多名销售员每天的多个订单，其中字段SellerId是销售员编号。emp是mssql数据库中的表，存储着销售员信息，其中字段EId是销售员编号，Name是销售员名字，Dept是部门名称。现在需要在报表中展现：订单编号、日期、金额、销售员名字、部门名称，条件是：订单日期在最近N天（比如30天）或者订单属于某几个受关注的部门（比如Markeding和Finance）。

         由于订单编号、日期、金额来自于表sales，而销售员名字、部门名称来自于表emp，因此要进行不同数据库之间join运算，join之后还要进行条件过滤。部分源数据如下：

         表sales

         表emp

         集算器代码：

         A1=myDB1.query("select * fromsales")

         这句代码从数据源myDB1查询出sales表的记录，myDB1指向mysql数据库。函数query用来执行SQL查询，可以接收外部参数。A1的计算结果如下：

         A2=myDB2.query("select * fromemp")

         这句代码从数据源myDB2查询出emp表的记录，myDB2指向mssql数据库。

         A3=A1.switch(SellerId,A2:EId)

         上述代码将A1中的SellerId字段替换成A2中对应的记录，关联字段为EId。A3的计算结果如下（蓝色字体表示该数据项包含下级成员）：

         当A2中找不到对应的记录时，函数switch默认保留A1中记录，对应的SellerId显示为空，效果类似于左连接。如果想进行内连接，应当使用选项@i，形如：A1.switch@i(SellerId,A2:EId)

        

         A4=A3.select(OrderDate>=after(date(now()),days*-1)||depts.array().pos(SellerId.Dept))

         上述代码可对关联结果进行过滤，条件有2个，第1个条件是：订单日期在最近N天（对应参数days），表达式为OrderDate>=after(date(now()),days*-1)。第2个条件是：订单属于某几个受关注的部门（对应参数depts），表达式是depts.array().pos(SellerId.Dept)。运算符||表示逻辑关系“或”。

         函数now可以取当前时间，函数date将当前时间转为日期，函数after可以算出相对时间，比如after("2015-01-30",-30)表示将时间后退30天，即2015-01-01。使用不同的选项，函数after还能以年、季、月、秒为单位计算相对时间。

         函数array可以按分隔符将字符串变成集合，比如"Marketing,Finance".array()等于["Marketing","Finance"]。函数array的默认分隔符是逗号，也可以指定其他分隔符。函数pos可以找出成员在集合中的位置，比如["Marketing ","Finance"].pos("Finance")等于2，在逻辑关系中等于true。如果成员不在集合中，则返回null，在逻辑关系中等于false。

         值得注意的是SellerId.Dept这种用法，这表示SellerId字段对应的记录的Dept字段。可以看到，用switch替换字段后，表之间的关联关系就可以用对象的方式来访问，这种方式直观简单，进行多表多层关联时会体现得更明显。

         Days和depts都是来自于报表的参数，如果分别输入30、"Marketing,Finance"，则A4的结果如下：

         A5=A4.new(OrderID,OrderDate,Amount,SellerId.Name:Name,SellerId.Dept:Dept)

         上述代码从A4中取得报表需要的字段，其中SellerId.Name和SellerId.Dept分别表示emp表中的员工名字和部门名称，运算符“:”表示重命名。A5的计算结果如下：

        到此为止，报表需要的数据就全部计算出来了。最后只需用result A5将A5中的二维表返回报表工具。集算器对外提供JDBC接口，报表工具会将集算器识别为普通数据库，集成方案请参考相关文档。

 

        接下来以JasperReport为例设计一张简单报表，表样如下：

         需要定义两个报表参数pdays、pdeps，分别对应集算器中的两个参数。预览后可以看到报表结果：

         报表调用集算器的方法和调用存储过程一样，比如将本脚本保存为afterjoin1.dfx，则在JasperReport的SQL设计器中可以用afterJoin1 $P{pdays},$P{pdepts}来调用。

 

         有了esProc的协助，报表工具还可以处理更复杂的多数据源join后计算。比如：找到某日期之后每个销售员的销售额增长最快的三天，展现销售员名字、这三天的日期、销售额、增长率。

         集算器代码：

        A1=myDB1.query("select * from sales whereOrderDate>=?",beginDate)

        上述代码用来查询表sales中某日期之后的订单，其中beginDate为报表传来的参数，假设该值为”2015-01-01”，则A1的计算结果如下：

        A2=myDB2.query("select * from emp")

        上述代码查询emp表，结果如下：

        A3=A1.switch(SellerId,A2:EId)

        上述代码将A1中的SellerId字段替换成A2中对应的记录，结果如下：

        A4=A3.group(SellerId)

        上述代码将订单按SellerId分组。下图左侧是A4的计算结果，右侧是其中两个SellerId的订单。

        A5=A4.(~.groups(OrderDate,SellerId;sum(Amount):subtotal))

        上述代码将每个SellerId的订单再按照OrderDate和SellerId分组，并汇总各组的订单金额，即：每个销售员每天的销售额。如下图：

        上述代码中“A4.()”表示对A4的每个成员进行循环计算，括号中的“~”是成员变量，即某SellerId对应的订单记录。“~.groups()”表示对每个成员应用函数groups。函数groups可对数据分组并进行简单的汇总，函数group可以只分组不汇总。

 

        A6=A5.(~.derive((subtotal-subtotal[-1])/subtotal[-1]:rate))

        上述代码计算出每个销售员销售额的日增长率，计算结果如下：

         上述代码中，函数derive用来增加新字段，字段名为rate，算法为“（当日销售额-上一日的销售额）/上一日的销售额”。可以看到，集算器用subtotal[-1]来表示上一日的销售额，可以轻松进行相对位置的计算。

         值得注意的是，由于第一条记录没有对应的“上一日的销售额”，因此销售额增长率为Null。

 

        A7=A6.(~.select(#!=1))

        这句代码在A6的基础上去掉每组数据的第一条记录（因为第一条记录的增长率为无意义的Null）。

        代码中的函数select可以进行查询，“#”表示循环序号，“#!=1”即序号不等于1。同样的功能也可以用函数delete来实现，但性能稍低，这是因为函数select只返回引用，而delete需要改变实际数据。

 

        A8=A7.(~.top(-rate;3))

        上述代码可以计算出每个销售员销售额增长率最大的三天对应的记录。函数Top可以根据某字段（或某几个字段的表达式）取出前N条记录。计算结果如下：

        A9=A8.union()

        上述代码将A8中的各组数据合并起来，形成新的二维表A9，如下：

        A10=A9.new(SellerId.Name:Name,OrderDate,subtotal,rate)

        上述代码从A9中取出需要的字段，即本案例最终计算结果。

        result A10

        上述代码将A10中的二维表返回报表工具。报表的设计可以参考前一个案例，这里不再赘述。