简化SQL式计算之行列转置

在数据库应用开发中，我们经常需要面对复杂的SQL式计算，行列转置就是其中一种。实现这类算法，Oracle可以使用pivot函数，但其他数据库没有相应的函数，因此代码比较难写，也不易理解和维护。另外，pivot函数只能实现固定列的转置，对于非固定列则无能为力，其他数据库同样无法实现非固定列的转置，通常都要求助于高级语言来实现动态SQL。

用集算器实现此类算法会更加简洁易懂，下面用一个例子来说明。

 

数据库表SALES存储着订单数据，部分数据如下：

 

现在要计算出2013年各月份订单的总金额、最大订单金额、最小订单金额，以及总订单数，并将数据转置成13列4行，即：四种算法是第一列，列名为subtotal，每个月占一列，列名分别是1、2、3、4…其中前五个字段如下：

 

集算器代码：

 

A1：执行SQL，过滤出2013年的数据，并按月份分组汇总，结果如下：

 

 

这句SQL很简单，任何数据库都支持这种分组汇总，困难在于后续的行列置换。

 

A2=create(subtotal).record(["OSum","OMAX","OMIN","OCount"])

上面这句代码生成一个空的序表，这个序表只有一个字段：subtotal，结果如下：

 

       说明：序表是集算器的一种数据类型，是带有结构的二维表，类似SQL数据表，但功能更强大，用法更灵活。另外，A1也是个序表。

 

B2=A2.derive(${to(A1.len()).string()})

 

上面这句代码在A2的基础上增加12个列，形成转置后的数据结构，结果如下：

 

函数derive可以给现有的序表增加新列，形成新的序表，比如derive(1)表示增加1列，字段名为1，字段值和列名相同，derive(0:field1, null:field2)表示增加2列，字段名分别为field1和field2，字段值分别为0和null。

根据转置要求，这里应当增加12个列，代码应当是derive(1,2,3,4,5,6,7,8,9,10,11,12)，为了动态生成这段代码，这里使用了宏，即${}，宏的作用是将字符串转为表达式。其中to(A1.len())是个序列，值为[1,2,3,4,5,6,7,8,9,10,11,12]，函数string()可以将这个序列转为字符串“1,2,3,4,5,6,7,8,9,10,11,12”。

 

A3-A5：对A1进行循环，每次访问一条记录，算法是将这条记录纵向拼接，并修改序表B2中对应的列。值得注意的是，只需要用缩进就能表达循环语句的作用范围，而不需要用{}或begin/end来指定，因此B4、B5在作用范围内，而A4或A5不在作用范围。

说明：在集算器的循环体内，循环变量就是for语句所在的单元格。换句话说，可以用A3来引用当前记录，可以用A3.MONTH来引用当前记录的MONTH字段。

 

B4=A3.OSum | A3.OMAX | A3.OMIN | A3.OCount

上面这句代码用来将当前记录的汇总字段纵向拼接起来，运算符“|”表示拼接，比如A1中12月份的记录拼接后是这样的：

 

代码中的A3.OSum表示当前记录的OSum字段，由于OSum字段是记录中的第2个字段，因此也可以按序号来引用，写作A3.#2，所以上述语句等价为：B4=A3.#2 | A3.#3 | A3.#4 | A3.#5

 

B5=eval("B2.run(B4(#):#"+ string(#A3+1)+ ")")

上述代码的意义是：修改B2中的字段，数据来自B4。

函数eval用来将字符串动态解析为表达式，比如eval("2+3")的计算结果是5，再比如本例中，循环到12月份时，eval中的字符串就是：B2.run(B4(#): #13)，这表示按照B2的记录序号，依次将B4的成员插入B2的第13列（即12月份）。

修改字段使用的函数是run，比如run(field1+field2:field1, 0:#2)表示将field1的值改为field1+field2，将第2个字段（即#2）的值变成0。

#A3表示当前的循环计数，第一次循环时这个值等于1，第二次等于2，以此类推。

 

 

循环语句A3-B5执行后，B2中的数据就是最终的计算结果，前几列如下：

 

另外，集算器可被报表工具或java程序调用，调用的方法也和普通数据库相似，使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果，具体方法可参考相关文档。