动态行列转换的计算在实际业务中很常见,网上各类技术论坛上都有讨论,比如下面这些问题:
http://www.iteye.com/problems/87788
http://bbs.csdn.net/topics/390869577
http://bbs.csdn.net/topics/391000711
http://bbs.csdn.net/topics/391001035
http://bbs.csdn.net/topics/390888703
http://bbs.csdn.net/topics/391012377
http://bbs.csdn.net/topics/390956910
http://bbs.csdn.net/topics/391004719
http://bbs.csdn.net/topics/390946260
http://bbs.csdn.net/topics/390937222?page=1#post-398564938
http://bbs.csdn.net/topics/390883416
http://bbs.csdn.net/topics/390960953
http://bbs.csdn.net/topics/390959646
行转列使用SQL完成一般有以下几种方法:
<!--[if !supportLists]-->1、 (1)<!--[endif]-->使用行列转换函数
Oracle11g及以上和MSSQL2005+提供了行列转置运算符pivot和unpivot,前者用于行转列,后者用于列转行,使用时需要指定目标列,对于动态列的场景无法直接完成。
<!--[if !supportLists]-->21、 (2)使用CASE表达式
对于不支持pivot的数据库,如Mysql、DB2,可以使用case when条件表达式完成。与pivot类似,需要根据目标列固定写死,无法直接写出动态列结构转换。
对于动态列的情况,只能:
<!--[if !supportLists]-->3、<!--[endif]-->拼接动态SQL
处理动态行列转换时往往需要在存储过程中拼接动态SQL完成,由于数据库间的差异,写法与难易程度也不尽相同,无法编写通用的SQL语句。
实际情况中中,行列转换往往还伴随列间计算,增大了转置时的难度。
行列转换的目的常常是为了进一步的数据呈现,也就是说会有个主程序(如报表工具等)接受结果以进行下一步操作。如果是Java主程序,则可以使用润乾集算器(免费版)来协助完成这类转换。集算器是动态解释执行的脚本,完成行列转换的代码更具通用性。集算器提供了JDBC接口,可以置于Java应用程序与数据库之间,让应用程序继续象访问数据库一样执行集算器脚本,不用改变应用结构。
下面以一个简单的例子说明用集算器如何实现行列转换,并集成进Java主程序中。
1、简单的行转列
一般的行转列只简单地将数据行转为结果列,不涉及复杂的列间计算。如将下面的学生成绩表转为分科目展示的集合:
目标结果:
实现脚本:
A1:执行SQL取数,并按ID、SUBJECT排序;
A2-A3:按ID和SUBJECT分组,集算器保留了分组后的子集供后面计算使用;
A4:动态创建空的目标结果集;
A5-B5:循环A2的学生分组,根据SUBJECT分组将学生ID、姓名和各科目成绩写入结果集;
A6:返回结果集。
从上面代码可以看出采用集算器实现行转列的基本步骤:先动态计算出空的目标结果集(A4),再计算出每行数据追加到结果集中(A5,B5)。在有了支持数据表对象的分步计算机制后,行转列的过程可以按自然思路编写出来。
集算脚本的计算结果可以用JDBC接口返回给JAVA主程序或报表工具,JAVA调用集算脚本代码:
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
//调用集算器脚本(类似存储过程),其中p1是集算器脚本文件名
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call p1 ()");
//执行脚本
st.execute();
//获取结果集
ResultSet rs = st.getResultSet();
……
返回值是符合JDBC标准的ResultSet对象,调用集算器脚本和访问数据库的方法完全一样,熟悉JDBC的程序员可以很快掌握。
关于集算器JDBC的部署和调用的更详细信息可参考【集算器集成应用之被JAVA调用】
2、不定长分组的行转列
上一个例子中,结果集的列(即科目)经常可以事先获知,这样用静态的pivot(或case when)语法写出来也不算很困难。但如果结果集的列需要动态计算出来,用pivot就很困难了。如本例中每类机制生产的产品列数不定:
要求根据最大的机组分组长度决定转换后的结果列数,目标结果:
实现脚本:
A1:执行sql从产量表中取数;
A2:按机组分组,在集算器中分组结果保留了分组结果(成员)以方便后续使用和计算;
A3:求分组中最大成员个数,以确定结果集列数;
A4-A5:动态创建空结果集;
A6-B7:循环A2中分组结果,将每个分组中的类别和产量写入A5结果序表中。
与上述类似,这段代码仍然是先动态生成空结果集,然后再计算出合适的数据追加。
本例的计算需要写出动态的SQL来拼出结果集,但由于要找出最大的组才知道列数,拼结果也不是像一般的pivot那样可以用字段值直接对应成列,这就要写存储过程一步步地完成才方便。
相对比较复杂的存储过程,集算脚本支持过程性计算,代码更加简洁、易编写。
3、包含列间计算的行转列
如开始提到的,行列转换的同时往往伴随列间计算,例如有数据:
要求根据指定年份(如2014),输出每月应付金额,若无当月数据,则当月应付金额为上月该值。
目标结果:
实现脚本:
A1:执行SQL取查询年数据;
A2:生成带有12个月的结果空序表;
A3:按客户分组;
A4-B7:循环分组,B5设置相应月份的应付金额,B6将空值置为前一个月的数值,B7将记录插入结果序表中。
运算过程仍然是先产生空结果集后追加数据,不同的是,这里要追加的数据需要经常一系列计算才能得到。
集算脚本支持有序运算,所以很容易取到前一条记录的值。对于动态行列转换时发生的列间计算,与复杂SQL或存储过程相比,集算脚本更清晰易懂。
4、列转行
除了上述提到的转置,有时还有将一行多列数据转为多行数据(列转行)。如下数据,其中列数不定:
目标结果:
实现脚本:
A1:执行SQL取数;
A2:创建目标结果空序表;
A3:根据A1集合的列数计算每条记录要拆分的行数;
A4-B4:循环A1集合,动态获取每列数据插入A2结果序表中;
相关推荐
6、提供行列转换(数据旋转),避免写复杂的sql或存储过程,用算法来化解对sql的高要求,同时实现数据库无关(不管是mysql还是sqlserver) 7、提供分组汇总求平均算法(用算法代替sql避免跨数据库语法不一致) 8、分库分表...
HttpModule 实现 ASP.Net (*.aspx) 中文简繁体的自动转换,不用修改原有的任何代码,直接部署即可! 服务器自定义开发二之客户端脚本回发 Web开发: 使用URL重写WEB主题切换 如何在Asp.Net1.1中实现页面模板(所谓的...
实例043 将二维数组中的行列互换 53 实例044 利用数组随机抽取幸运观众 54 实例045 用数组设置JTable表格的列名与列宽 55 3.2 数组操作 57 实例046 数组的下标界限 57 实例047 按钮控件数组实现计数器界面 58 实例...
[神2也教你学E] - 可执行动态载入&输出其他文件模块.ec _仿真shell库.ec √功能键状态√.ec √取功能键状态√.ec 万 年历.ec 万能注册验证模块.ec 世宝脚本语言引擎.ec 世恒通用安装系统文件压缩模块.ec 世恒通用...
[神2也教你学E] - 可执行动态载入&输出其他文件模块.ec _仿真shell库.ec √功能键状态√.ec √取功能键状态√.ec 万 年历.ec 万能注册验证模块.ec 世宝脚本语言引擎.ec 世恒通用安装系统文件压缩模块.ec 世恒通用...
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高级特性 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 操作...