`
datamachine
  • 浏览: 155831 次
社区版块
存档分类
最新评论

简化SQL式计算之行列转置

阅读更多

在数据库应用开发中,我们经常需要面对复杂的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形式的计算结果,具体方法可参考相关文档。

  • 大小: 27.1 KB
  • 大小: 21.6 KB
  • 大小: 69.5 KB
  • 大小: 63.3 KB
  • 大小: 10 KB
  • 大小: 30.8 KB
  • 大小: 10.4 KB
  • 大小: 32.1 KB
0
1
分享到:
评论

相关推荐

    sqltoy-orm框架系统-其他

    3、极为强大的缓存翻译查询:巧妙的结合缓存减少查询语句表关联,极大简化sql和提升性能。 4、最强大的分页查询:很多人第一次了解到何为快速分页、分页优化这种极为巧妙的处理,还有在count语句上的极度优化。 5、...

    asp.net知识库

    多样式星期名字转换 [Design, C#] .NET关于string转换的一个小Bug Regular Expressions 完整的在.net后台执行javascript脚本集合 ASP.NET 中的正则表达式 常用的匹配正则表达式和实例 经典正则表达式 delegate vs. ...

    Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3

    实例043 将二维数组中的行列互换 53 实例044 利用数组随机抽取幸运观众 54 实例045 用数组设置JTable表格的列名与列宽 55 3.2 数组操作 57 实例046 数组的下标界限 57 实例047 按钮控件数组实现计数器界面 58 实例...

    1345个易语言模块

    堕之星辰1.2.ec 填充树型框.ec 复件 我的测试易模块.EC 复件 提示框模块2.72 Alpha版.ec 复件 提示框模块3.0版.ec 复件 提示框模块3.1版.ec 复件 文本分行.EC 复 件 文本操作.EC 复件 星光灭绝.ec 复件 状态栏控件....

    1350多个精品易语言模块

    堕之星辰1.2.ec 填充树型框.ec 复件 我的测试易模块.EC 复件 提示框模块2.72 Alpha版.ec 复件 提示框模块3.0版.ec 复件 提示框模块3.1版.ec 复件 文本分行.EC 复 件 文本操作.EC 复件 星光灭绝.ec 复件 状态栏控件....

    MYSQL培训经典教程(共两部分) 2/2

    MYSQL高级特性 81 4.1 集合函数 82 4.1.1 行列计数 82 4.1.2统计字段值的数目 82 4.1.3 计算字段的平均值 83 4.1.4 计算字段值的和 84 4.1.5 计算字段值的极值 84 4.1.6 总结 86 4.2 操作...

    MYSQL培训经典教程(共两部分) 1/2

    MYSQL高级特性 81 4.1 集合函数 82 4.1.1 行列计数 82 4.1.2统计字段值的数目 82 4.1.3 计算字段的平均值 83 4.1.4 计算字段值的和 84 4.1.5 计算字段值的极值 84 4.1.6 总结 86 4.2 操作...

Global site tag (gtag.js) - Google Analytics