集算器实现SQL动态列计算的示例

       被数据库厂商扩展后的SQL也可动态拼接出语句执行，但SQL的集合运算和有序计算不方便，实现动态列计算很繁琐，往往要借助高级语言拼出动态SQL。

       免费的集算器支持动态脚本、有序计算、显式集合，可以弥补这一不足。下面举例说明SQL开发中常见的动态列计算问题，以及集算器对应的解法。

列间排序

 

       表order有6个整数类型的字段，现在要对每条记录的字段值进行排序，使其按从小到大的顺序横向排列。源数据如下：

 

       集算器代码：

 

       先执行SQL，再循环处理每条记录，处理过程为：将当前记录的字段值用函数array转为集合，再用sort函数排序，最后用函数record写入当前记录。结果如下：

动态拼Merge语句

 

         参数source和target代表MSSQL中两个结构相同但数据不同的表，要求以主键为标准用source更新target，比如table1和table2的主键都是A和B，数据如下：

         用table2更新table1时，应当动态生成如下的MERGE语句：

       MERGE INTO table1 as t

       USING table2 as s

       ON t.A=s.A and t.B=s.B

       WHEN MATCHED

       THEN UPDATE SET t.C=s.C,t.D=s.D

       WHEN NOT MATCHED

       THEN INSERT VALUES(s.A,s.B,s.C,s.D)

 

         更新后的table1应当如下：

       集算器代码：

         从系统表读出source的主键和字段，分别存在变量pks和columns中，之后用循环函数pks.(…)动态生成merge语句，其中~表示循环变量，#表示循环计数。

         上述代码只是生成merge语句，可将该语句返回给JAVA执行，也可以在集算器中执行，代码即：A6=myDB1.excute(A5) 。

 

跨库动态merge运算

 

         前面的例子假设source和target在同一个数据库，但如果两者分属不同的数据库（比如MSSQL和Oracle），则无法拼出相应的merge语句，此时可用集算器实现merge计算。代码如下：

       myDB2/myDB1分别指向source/target所在数据库，A3\A4以游标的形式打开数据库，使用join@x1函数对游标进行左连接，之后每次从A5取一批数据到内存，使用update函数更新target，其中@i表示只生成insert语句，@u表示只生成update语句。执行后target如下：

 

         这个方法也可以应用于那些不支持MERGE语句的数据库，如MySQL。

字段含表名的动态SQL

 

         表A有两个字段：ID、TableName，TableName存储其他表的表名，比如B、C、D等，这些表结构相同，都以ID为关键字。表A的ID字段存储其他表的ID。源数据如下：

         现在要通过表A获得其他表的相关记录，即：

 

       集算器代码：

 

         查询表A并按TableName分组，之后循环每组数据，动态取得其他表中对应的记录，最后合并记录并按ID排序，结果如下：

 

         如果表A本身无序，但计算结果要和表A保持同序，则应当使用下面的代码：A1.(mssql.query("select ID,Num from "+TableName+" where ID =?",ID)).conj()

动态跨列统计

 

         表tb字段数未知，但所有字段类型相同，现在要统计所有记录的所有字段有多少种不同的数据，每种数据各有几个。源数据如下：

 

         集算器代码：

 

         先将每条记录的每个字段合并为一个大集合，再进行分组汇总，结果如下：

 

两记录比较不同

 

         表Burger存储着对汉堡包两次检测后的成分含量，现在要比较这两条记录，列出不同的成分。源数据如下：

 

         集算器代码：

 

         A2\A3分别将两条记录转为集合，再用pselect函数找到A2\A3不同成员的序号列表，最后按字段序号创建新二维表。结果如下：

 

         如果源数据有多组数据，每组都有两条，比较的结果需要拼成name\fieldname\value三个字段，则应当使用如下代码：

 

         将数据按name分组，针对每组取出不同的成分，再追加到B1中。结果如下：