在数据库应用开发中,我们经常需要面对复杂的SQL式计算,行列转置就是其中一种。实现这类算法,Oracle可以使用pivot函数,但其他数据库没有相应的函数,因此代码比较难写,也不易理解和维护。另外,pivot函数只能实现固定列的转置,对于非固定列则无能为力,其他数据库同样无法实现非固定列的转置,通常都要求助于高级语言来实现动态SQL。
用集算器实现此类算法会更加简洁易懂,下面用一个例子来说明。
数据库表SALES存储着订单数据,部分数据如下:
现在要计算出2013年各月份订单的总金额、最大订单金额、最小订单金额,以及总订单数,并将数据转置成13列4行,即:四种算法是第一列,列名为subtotal,每个月占一列,列名分别是1、2、3、4…其中前五个字段如下:
A1:执行SQL,过滤出2013年的数据,并按月份分组汇总,结果如下:
这句SQL很简单,任何数据库都支持这种分组汇总,困难在于后续的行列置换。
上面这句代码生成一个空的序表,这个序表只有一个字段:subtotal,结果如下:
说明:序表是集算器的一种数据类型,是带有结构的二维表,类似SQL数据表,但功能更强大,用法更灵活。另外,A1也是个序表。
上面这句代码在A2的基础上增加12个列,形成转置后的数据结构,结果如下:
函数derive可以给现有的序表增加新列,形成新的序表,比如derive(1)表示增加1列,字段名为1,字段值和列名相同,derive(0:field1,
A3-A5:对A1进行循环,每次访问一条记录,算法是将这条记录纵向拼接,并修改序表B2中对应的列。值得注意的是,只需要用缩进就能表达循环语句的作用范围,而不需要用{}或begin/end来指定,因此B4、B5在作用范围内,而A4或A5不在作用范围。
说明:在集算器的循环体内,循环变量就是for语句所在的单元格。换句话说,可以用A3来引用当前记录,可以用A3.MONTH来引用当前记录的MONTH字段。
上面这句代码用来将当前记录的汇总字段纵向拼接起来,运算符“|”表示拼接,比如A1中12月份的记录拼接后是这样的:
上述代码的意义是:修改B2中的字段,数据来自B4。
函数eval用来将字符串动态解析为表达式,比如eval("2+3")的计算结果是5,再比如本例中,循环到12月份时,eval中的字符串就是:B2.run(B4(#): #13),这表示按照B2的记录序号,依次将B4的成员插入B2的第13列(即12月份)。
#A3表示当前的循环计数,第一次循环时这个值等于1,第二次等于2,以此类推。
循环语句A3-B5执行后,B2中的数据就是最终的计算结果,前几列如下:
另外,集算器可被报表工具或java程序调用,调用的方法也和普通数据库相似,使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果,具体方法可参考相关文档。