SQL 在常用报表业务中的归并、转换与信息汇总中的应用技巧

本文阐述并分步骤汇总了 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. 动态处理行列转换中数据的动态变化

Select　@sql=@sql+',sum(case　when　Subject='''+Subject+　'''　 
　then　Score　else　60　end)　As　'''+Subject+''''　 
　From　Single_ScoreRecords　Group　By　Subject　 
　Print　@sql　

上段语句执行的结果为：

清单 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. 行列转换最终系列代码部分

　declare　@sql　nvarchar(2000);　 
　set　@sql='Select　Name　';　 
　Select　@sql=@sql+',sum(case　when　Subject='''+Subject+　'''　 
　then　Score　else　60　end)　As　'''+Subject+''''　 
　From　Single_ScoreRecords　Group　By　Subject;　 
　Print　@sql;　 
　set　@sql=@sql+'　From　Single_ScoreRecords　Group　By　Name';　 
　Print　@sql;　 
　execute　sp_executesql　@sql;　

转换的结果集为：

表 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 语句的可读性很强，对于总量不为零的实际问题，由于在大部分表的处理速度上比上述两种常规方法要快（避免了多次扫描相同表数据），所以分析函数的方法是非常可行的。在运用分析函数方法解决各类数据汇总计算的时候，要考虑数据排序的代价，在排序成本很高的情况下可以考虑采用其他方法；但如果对于序列化较强的列进行汇总计算，采用分析函数无疑是最为有效的办法。所以在实际汇总问题中，根据表规模不同，采取合适的解决方案，是非常关键的。