本文阐述并分步骤汇总了 SQL 数据库报表系统中最常见的三类典型问题：高效的行列间转换，多路数据归并，以及信息聚合汇总计算。
文中首先阐述各类问题的背景和报表客户的需求分析，然后分别论述各类问题的解决方案。对于行列转换问题，文中介绍了自适应性的行列间转换设计方法和程序样本，并验证实验结果可适应原行列内容和数据的动态改变；对于数据归并问题，采用 oracle9i 新增分析函数高效解决多行归并问题，并通过分步骤详细介绍中间结果集，介绍了几个典型分析函数的用法；对于信息汇总计算问题，介绍了几个常规的解决方案，并阐述各方案的优缺点，给出采用分析函数解决信息汇总计算问题的有效性。
读者将了解常用报表业务中的几个典型问题行列间转换（报表聚类），行数据归并（多对一报表汇总），以及信息聚合汇总计算（分析信息整合）；并从文中了解多种解决方案的实施部署及性能比较。
报表业务中常见的归并、转换与汇总
报表业务经常会遇到数据行列间的归并、转换与信息汇总等需求。比如在股票分析报表中，对于同一只股票，通常会有多个研究员对其进行跟踪研究，由此便引发了多行归并的需求，即查询出每只股票和对应所有研究员汇总成单行信息；在学校总结以学生考试成绩排名为行的报表系统中，由于在录入学生成绩的时候往往采用单科单条录入方式，所以在汇总学生各科成绩的时候往往需要进行行列间转换以满足报表需求；又如在公司总结员工工作量和绩效考核的报表系统中，往往需要将工作量转变成总量的比例以满足各种绩效评估需求，这就需要对相应列进行汇总计算分析。上述这些应用都是在实际报表业务中的常见问题，本文将针对这典型的三类问题分别进行分析并给出可行的解决方案。
数据归并问题
很多报表项目都会遇到数据归并的问题，例如在投行的股票分析报表系统中通常会保存股票与研究员的对应关系数据。对于同一只股票，通常会有多个研究员对其进行跟踪研究和投资评级；由此引发一个非常常规的归并需求，即查询出每只股票和对应的研究员汇总归并行信息。
下面简单阐述一下上述数据归并需求。假设股票分析师报表系统中关于股票和分析师的表结构定义如下：
| NAME | Null | Type |
| Stock_Code | NOT NULL | CHAR(6) |
| Researcher_Name | NOT NULL | VARCHAR(30 |
上述数据归并的需求，即查询出每只股票和对应的研究员归并行，并将信息以某分隔符进行分离。
例如有如下数据：
000585 wangxiaoming 000585 zhangyibai 000585 zhengminnong
合并处理后需显示为：
000585 wangxiaoming/zhangyibai/zhengminnong
类似的多行归并问题通常会通过编写一些自定义多行文本合并的 UDF 来实现，并且支持的列数具有局限性。下文将分步骤地介绍一种利用 oracle9i 新增分析函数高效解决多行归并问题的巧妙方法。此方法不需要在 DB 中增加自定义函数，利用 SQL 语言一次性实现，扩充性好，并且没有列的限制。
假设上述数据表的全部数据表清单如下：
清单 1. 选择归并样表数据
SELET * FROM INVESTIGATION _ANALYSIS_TB；
表 2. 数据归并样例表数据
| Stock_Code | Researcher_Name | |
| 000585 | wangxiaoming | |
| 000586 | aiwenjun | |
| 000587 | zhanghongmin | |
| 000586 | pushaojun | |
| 000586 | liqiang | |
| 000587 | xiaopeng | |
| 000586 | baigang | |
| 000585 | zhangyibai | |
| 000585 | zhengminnong |
构建树结构
样例程序脚本清单如下：
清单 2. 构建树结构的 SQL 语句样例
select Stock_Code, Researcher_Name, to_number(Stock_Code)+row_number() over( order by Stock_Code) Tree_rank, row_number() over(partition by Stock_Code order by Stock_Code) Group_rank from INVESTIGATION _ANALYSIS_TB
| Stock_Code | Researcher_Name | Tree_Rank | Group_Rank | |
| 585 | wangxiaoming | 586 | 1 | |
| 585 | zhangyibai | 587 | 2 | |
| 585 | zhengminnong | 588 | 3 | |
| 586 | aiwenjun | 590 | 1 | |
| 586 | pushaojun | 591 | 2 | |
| 586 | liqiang | 592 | 3 | |
| 586 | baigang | 593 | 4 | |
| 587 | zhanghongmin | 595 | 1 | |
| 587 | xiaopeng | 596 | 2 |
Group_rank 列的目的是按照股票号码转变成数值后分组，根据划分后各组的起始值，我们可以在 SYS_CONNECT_BY_PATH 分析函数中用到该值。Tree_rank 列是用来做 SYS_CONNECT_BY_PATH 分析函数中的连接条件使用，树排序的值实际上就是我们将在 SYS_CONNECT_BY_PATH 中用到的树。
第一个树枝： 586，587，588 第二个树枝： 590，591，592，593 第三个树枝： 595，596
在上述树结构的基础上，采用树型函数 SYS_CONNECT_BY_PATH 来拼接我们所需要的多列值。
脚本清单如下：
清单 3. 拼接树分支的 SQL 语句样例
select Stock_Code, sys_connect_by_path(Researcher_Name,'/') from ( select Stock_Code, Researcher_Name, to_number(Stock_Code)+row_number() over( order by Stock_Code) Tree_rank, row_number() over(partition by Stock_Code order by Stock_Code) Group_rank ) start with Group_rank =1 connect by Tree_rank -1=prior Tree_rank
结果集为：
表 4. 拼接后的中间数据结果集
| Stock_Code | sys_connect_by_path(Researcher_Name,'/') | |
| 000585 | /wangxiaoming | |
| 000585 | /wangxiaoming/zhangyibai | |
| 000585 | /wangxiaoming/zhangyibai/zhengminnong | |
| 000586 | /aiwenjun | |
| 000586 | /aiwenjun/pushaojun | |
| 000586 | /aiwenjun/pushaojun/liqiang | |
| 000586 | /aiwenjun/pushaojun/liqiang/baigang | |
| 000587 | /zhanghongmin | |
| 000587 | /zhanghongmin/xiaopeng |
从结果集中可以看出文中正树排序采用股票号数值与在线行排序函数 row_number() 叠加的用意，树排序结果之所以不按照等差数列递增，而在各组间隙处增量值大于组内排序增量，就是为了使只有组内满足连接条件 Tree_rank -1=prior Tree_rank。
归并行筛选
将分组排序后的 sys_connect_by_path 中间集取最大值，并去除字符串首部的分隔符，便得到最终的行归并结果。
脚本清单如下：
清单 4. 多行归并最终 SQL 语句样例
Select Stock_Code, substr (MAX(sys_connect_by_path(Researcher_Name,'/')),2) Combined_Row from ( select Stock_Code, Researcher_Name, to_number(Stock_Code)+row_number() over( order by Stock_Code) Tree_rank, row_number() over(partition by Stock_Code order by Stock_Code) Group_rank ) start with Group_rank =1 connect by Tree_rank -1=prior Tree_rank group by Stock_Code
得到最终结果集为：
表 5. 多行归并最终结果数据集
| Stock_Code | Combined_Row |
| 000585 | wangxiaoming/zhangyibai/zhengminnong |
| 000586 | aiwenjun/pushaojun/liqiang/baigang |
| 000587 | zhanghongmin/xiaopeng |
行列转换问题
在编写报表的过程中，通常会遇到报表间的行列转换问题。比如在学校总结以学生考试成绩排名为行的报表系统中，通常会遇到需要转换的问题；因为在录入学生成绩的时候往往采用按照类似表 6 的单科单条录入方式，所以在汇总学生各科成绩的时候需要进行行列间转换以达成类似表 7 的效果。
表 6. 单科单条录入结果集样例
| Number | Name | Subject | Score | |
| 00001 | David | English | 87 | |
| 00002 | David | History | 91 | |
| 00003 | David | Math | 99 | |
| 00004 | Kevin | English | 90 | |
| 00005 | Kevin | Math | 94 | |
| 00006 | Rose | History | 78 | |
| 00007 | Rose | Math | 83 | |
| 00008 | Robin | English | 66 | |
| 00009 | Robin | History | 68 |
通常在对于某一行转列的转换过程中，要用到 SQL 自身的判断语句以及求和聚合函数来解决重复行的 null 值问题，SQL 语句清单如下所示；但这种方法不能动态适应原表中需要转列的行数据的改变，以数据种类的增加或减少（例如学科名字都改成首字母缩写，或增加学科种类等改变）。
清单 5. 对表 6 录入结果进行行列转换的 SQL
select sum(case Subject when 'English' then Score else 60 end) English, sum(case Subject when 'History' then Score else 60 end) History, sum(case Subject when 'Math' then Score else 60 end) Math from Single_ScoreRecords
为适应需转换列的数据变化，常规做法是对上述 SQL 进行静态调整；假设科目中新增了物理和音乐两门学科，则需要在原有 SQL 语句的行列转换部分新增两行语句。
清单 6. 静态处理行列转换中数据的动态变化
select sum(case Subject when 'English' then Score else 60 end) English, sum(case Subject when 'History' then Score else 60 end) History, sum(case Subject when 'Math' then Score else 60 end) Math sum(case Subject when 'Physics' then Score else 60 end) Physics sum(case Subject when 'Music' then Score else 60 end) Music from Single_ScoreRecords
下面介绍一种利用 SQL Sever 的字符串动态衍生功能和执行字符串 SQL 语句的存储过程来实现这种自适应的动态行列间转换功能。利用 SQL Sever 处理字符串执行衍生叠加的特性，假设 @sql 被定义为一个足够长的字符串。
清单 7. 动态处理行列转换中数据的动态变化
[email protected][email protected]+',sum(case when Subject='''+Subject+ ''' then Score else 60 end) As '''+Subject+'''' From Single_ScoreRecords Group By Subject [email protected]
上段语句执行的结果为：
清单 8. SqlServer 对于清单 7 中语句的处理结果
,sum(case when Subject ='English' then Score else 60 end) As 'English', sum(case when Subject='History' then Score else 60 end) As 'History', sum(case when Subject='Math' then Score else 60 end) As 'Math'
利用 SqlServer 中将字符串作为 SQL 执行语句的存储过程 sp_executesql 执行 @sql 的内容，从而实现自适应科目信息动态变化的高效行列转换功能。完整的动态 SQL 字符串定义如下。
清单 9. 行列转换最终系列代码部分
[email protected](2000); [email protected]='Select Name '; [email protected][email protected]+',sum(case when Subject='''+Subject+ ''' then Score else 60 end) As '''+Subject+'''' From Single_ScoreRecords Group By Subject; [email protected]; [email protected][email protected]+' From Single_ScoreRecords Group By Name'; [email protected]; [email protected];
转换的结果集为：
表 7. 行列转换后结果数据集
| Name | English | History | Math |
| David | 87 | 91 | 99 |
| Kevin | 90 | 60 | 94 |
| Rose | 60 | 78 | 83 |
| Robin | 66 | 68 | 60 |
当科目种类增加，现有科目减少，或者对现有科目名称进行更新替换时，上述程序段可以自动适应这些更新的变化，无需程序员对 SQL 进行修改。
信息汇总问题
信息汇总问题的描述和常规方法介绍
在各类报表内相关信息的汇总计算是报表业务中的典型问题。举一个简单的例子：在某公司统计员工工作量和绩效考核的报表系统中，假设其中三个列为雇员编号（Employee_SN），雇员姓名（Employee_Name）和完成技术支持工作中的 PMR 个数（PMR_Num）；现在为了统计单个员工的工作量在整个团队中所占比重，就是需要在汇总工作量列的所有行值的基础上，对各行员工分别进行所占比重的计算。
实现此类汇总计算的途径有很多，通常的做法可以建立一个查询总数的视图关联：这种方法的优点是表现直观，整体计算量相对较小；但缺点是在大表格上的处理比较慢，因为在目前版本的基础上，需要全表扫描至少两次。比如：
清单 10. 百分比汇总信息的常规方法
select Employee_SN, Employee_Name, PMR_Num, PMR_Num/Total_Num from Workload_TB,(select nvl(sum(PMR_Num),0) Total_Num from Workload_TB);
另一种常规方法是把数据存放在某个临时表，然后在临时表上进行统计。SQL 语句和上述情况类似，所不同的是这种方法是先把查询数据插入到一个临时表。如果中间数据集较小，那么这种方法的效率很高，因为它只要一次的全表扫描和少量几乎可以忽略的计算；但如果中间数据集较大，那么这种引入临时表的方法很有可能得不偿失。
采用分析函数和特殊函数解决信息汇总计算问题。
分析函数在各数据库产品中都占据重要地位，许多在线的信息汇总和排列都是靠数据库中高效的分析函数来完成的。无论是 DB2、Oracle、SQL Sever，都在维护着大量的分析函数以满足各种数据在线汇总和排列计算的大量需求，如 row_number、dense_rank、percent_rank 等分析函数。
下面通过介绍一个简单的分析函数 over (partitioned by value order by value...) 来实现上述工作量百分比信息的计算。
清单 11. 采用分析函数求解百分比汇总信息的 SQL 语句样例
select Employee_SN, Employee_Name, PMR_Num,to_char(percent,'990.00')||'%' percent from ( select Employee_SN, Employee_Name, PMR_Num, round(100* PMR_Num /( sum(PMR_Num) over(order by PMR_Num rows between unbounded preceding and unbounded following)),2) percent from Workload_TB )
表 8. 百分比数据汇总结果集样例
| Employee_SN | Employee_Name | PMR_Num | percent | ||
| 2007913938 | Bao, | Aiming | 102 | 24.88% | |
| 2008916342 | Jia, | Fang | 78 19.02% | ||
| 2009919067 | Shao, | Lei | 39 | 9.51% | |
| 2006911255 | Zhang, | Yibai | 179 | 43.66% | |
| 2010920476 | Zhai, | Wei | 12 | 2.93% |
这种方法的优点在于 SQL 语句的可读性很强，对于总量不为零的实际问题，由于在大部分表的处理速度上比上述两种常规方法要快（避免了多次扫描相同表数据），所以分析函数的方法是非常可行的。在运用分析函数方法解决各类数据汇总计算的时候，要考虑数据排序的代价，在排序成本很高的情况下可以考虑采用其他方法；但如果对于序列化较强的列进行汇总计算，采用分析函数无疑是最为有效的办法。所以在实际汇总问题中，根据表规模不同，采取合适的解决方案，是非常关键的。