简化SQL式计算之行列转置

2015年5月5日 17点热度 0条评论 来源: iteye_15107

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

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

 

数据库表SALES存储着订单数据,部分数据如下:



 

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



 

集算器代码:



 

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列,字段名分别为field1field2,字段值分别为0null

根据转置要求,这里应当增加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来指定,因此B4B5在作用范围内,而A4A5不在作用范围。

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

 

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

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



 

代码中的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形式的计算结果,具体方法可参考相关文档。

    原文作者:iteye_15107
    原文地址: https://blog.csdn.net/iteye_15107/article/details/82618681
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。