简化SQL实现动态行列转置

? ? ? ?动态行列转换的计算在实际业务中很常见,网上各类技术论坛上都有讨论,比如下面这些问题:

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+提供了行列转置运算符pivotunpivot,前者用于行转列,后者用于列转行,使用时需要指定目标列,对于动态列的场景无法直接完成。

<!--[if !supportLists]-->21、? ? ? ??(2)使用CASE表达式

对于不支持pivot的数据库,如MysqlDB2,可以使用case when条件表达式完成。与pivot类似,需要根据目标列固定写死,无法直接写出动态列结构转换。

对于动态列的情况,只能:

<!--[if !supportLists]-->3、<!--[endif]-->拼接动态SQL

处理动态行列转换时往往需要在存储过程中拼接动态SQL完成,由于数据库间的差异,写法与难易程度也不尽相同,无法编写通用的SQL语句。

?

?????? 实际情况中中,行列转换往往还伴随列间计算,增大了转置时的难度。

行列转换的目的常常是为了进一步的数据呈现,也就是说会有个主程序(如报表工具等)接受结果以进行下一步操作。如果是Java主程序,则可以使用润乾集算器(免费版)来协助完成这类转换。集算器是动态解释执行的脚本,完成行列转换的代码更具通用性。集算器提供了JDBC接口,可以置于Java应用程序与数据库之间,让应用程序继续象访问数据库一样执行集算器脚本,不用改变应用结构。

下面以一个简单的例子说明用集算器如何实现行列转换,并集成进Java主程序中。

1、简单的行转列

?

?????? 一般的行转列只简单地将数据行转为结果列,不涉及复杂的列间计算。如将下面的学生成绩表转为分科目展示的集合:



?

? ? 目标结果:



?

? ? 实现脚本:



?

? ? ? ? A1:执行SQL取数,并按IDSUBJECT排序;

? ? ? ??A2-A3:按IDSUBJECT分组,集算器保留了分组后的子集供后面计算使用;

? ? ? ??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结果序表中;

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。