一个动态交叉分片报表的几种实现方法

http://blog.runqian.com.cn/?p=1382

动态列报表在报表应用中很常见，往往由于业务的不同复杂度也差异很大。复杂的动态列报表在实现上往往十分困难，常见于数据源准备困难和报表呈现样式复杂，而前者占主要部分。这就要求报表工具具备很强的计算能力和呈现能力。这里根据实际的业务场景，来看一下使用润乾集算报表是如何完成复杂动态列报表开发的。

  目标报表样式如下：

  报表说明：

  （1）报表中基础和年度分组字段相同，汇总字段也相同，但数据的过滤条件不同；

  （2）分组字段和汇总字段由参数决定，而要汇总的年度也由参数决定。

  如：根据订单表，为报表传递分组字段为“货主地区,货主城市”；汇总字段为“订单金额,运货费”；汇总年度为”1996,1997,1998”，则可以得到结果报表为：

  订单表源数据为2400万条，以下测试数据均使用该订单表作为源数据获得。

  为了说明使用集算报表开发的简化过程，这里分别给出三种实现方式：1.SQL+HTML；2.集算报表（SQL数据集）；3.集算报表（集算器数据集） 以增强对比效果，其中第1中实现方式这里只给出SQL数据计算部分。

  下面根据上述给定参数（分组字段=“货主地区,货主城市”；汇总字段=“订单金额,运货费”；汇总年度=”1996,1997,1998”），对三种实现方式分别说明。

SQL+HTML实现

  这种方式不依赖报表工具，完全根据自写SQL准备数据，再通过代码来硬画表格实现。由于完全是自编码完成，所以灵活性最高，但实现过于繁琐导致工作量代价过大。SQL可以通过两种方式准备数据，交叉表和宽表。

交叉表

  SQL如下：

select t1.*,

     t2.years,

     round(t2.num / t1.num * 100, 2) oper,

     round(t2.num2 / t1.num2 * 100, 2) yper

  from (select 货主地区, 货主城市, sum(订单金额) as num, sum(运货费) as num2

      from 订单

     group by 货主地区, 货主城市

     order by 货主地区, 货主城市) t1

 

  left join

 

 (select 货主地区,

     货主城市,

     extract(year from 订购日期) as years,

     sum(订单金额) as num,

     sum(运货费) as num2

    from 订单

   where extract(year from 订购日期) in (1996, 1997, 1998)

   group by 货主地区, 货主城市, extract(year from 订购日期)

   order by 货主地区, 货主城市) t2

 

on t1.货主地区 = t2.货主地区

and t1.货主城市 = t2.货主城市

  输出结果为：

  这种写法的好处在于SQL相对简单，但前端展现时还需要进行一步行转列实现，导致整个实现的复杂度很高。此外，由于left join左侧的记录要大量复制，从而还有导致整个SQL的效率较低。

  实测表明，仅该SQL执行时间为：43s。

宽表

  SQL如下：

select t.*,

     round(t1.num / t.num * 100, 2) oper1,

     round(t2.num / t.num * 100, 2) oper2,

     round(t3.num / t.num * 100, 2) oper3,

     round(t1.num2 / t.num2 * 100, 2) yper1,

     round(t2.num2 / t.num2 * 100, 2) yper2,

     round(t3.num2 / t.num2 * 100, 2) yper3

from

 (select 货主地区, 货主城市, sum(订单金额) as num, sum(运货费) as num2

      from 订单

     group by 货主地区, 货主城市

     order by 货主地区, 货主城市) t

  left join

 

 (select 货主地区,

     货主城市,

     extract(year from 订购日期) as years,

     sum(订单金额) as num,

     sum(运货费) as num2

    from 订单

   where extract(year from 订购日期) = 1996

   group by 货主地区, 货主城市, extract(year from 订购日期)

   order by 货主地区, 货主城市) t1

 

  on t.货主地区 = t1.货主地区

  and t.货主城市 = t1.货主城市

  left join

 

 (select 货主地区,

     货主城市,

     extract(year from 订购日期) as years,

     sum(订单金额) as num,

     sum(运货费) as num2

    from 订单

   where extract(year from 订购日期) = 1997

   group by 货主地区, 货主城市, extract(year from 订购日期)

   order by 货主地区, 货主城市) t2

  on t.货主地区 = t2.货主地区

  and t.货主城市 = t2.货主城市

  left join

 

 (select 货主地区,

     货主城市,

     extract(year from 订购日期) as years,

     sum(订单金额) as num,

     sum(运货费) as num2

    from 订单

   where extract(year from 订购日期) = 1998

   group by 货主地区, 货主城市, extract(year from 订购日期)

   order by 货主地区, 货主城市) t3

  on t.货主地区 = t3.货主地区

  and t.货主城市 = t3.货主城市

 

  根据参数拼接动态SQL，即left join 右侧的查询，为了方便查看上述SQL为拼接后的结果。输出结果为：

  这种写法的好处在于结果集可以在前端展现时直接使用，无需再进行行转列，且left join左侧的数据不会多次重复复制；但由于要拼接动态SQL使得实现复杂度仍然很高，而且left join右侧的多个查询都带有分组、过滤和排序，导致整个查询效率不高。

  实测表明，仅该SQL执行时间为：89s。

  使用SQL实现时，无论采用上述哪种方式，前端展现时还要编写大量的代码来进行数据呈现，无疑使整体实现过于复杂。

  事实上，这也是报表工具存在的意义。

  集算报表中提供了多种数据集类型用于不同业务场景下的报表实现，其中，SQL和集算器数据集是最常用的两种类型，对于复杂动态列报表两种方式都可以实现，只是后者在复杂度和效率上略胜一筹，下面来看两种实现。

集算报表（SQL数据集）实现

  新建报表并设置参数：

  设置报表宏，用于拼接动态SQL、设置报表表达式：

  设置数据集，使用“SQL检索”：

  其中ds1为：select ${macro1},${macro2} from 订单 where 货主地区 is not null  group by ${macro1}。

  ds2为：select ${macro1},${macro2},extract(year from 订购日期) as years from 订单 where extract(year from 订购日期) in (${macro3}) group by ${macro1},extract(year from 订购日期)。

  编辑报表模板如下：

  这里通过参数和宏动态设置了数据集SQL，进行动态分组和汇总，使用了交叉表的做法。这里使用了两个数据集来避免交叉表实现中的重复复制多次的问题；继而在报表模板中通过两片分别计算基础数据和年份的汇总数据及占比；报表中使用了ds.fname()和ds.field()动态获得汇总字段名和字段值。

  使用报表工具可以将原来需要写在一起（如一句SQL）的实现逻辑拆分开，从而避免其中的重复计算等问题。而且集算报表中提供了强大的数据集函数、单元格函数等，使得此类动态列报表通过比较简单的报表编辑即可实现，计算和报表呈现一并完成，避免了复杂硬编码带来的工作量问题。

  虽然上面的实现比SQL硬编码实现简单且效率更高，但由于报表中使用了大量的隐藏行列（如D列、第4行等），并且将数据计算和呈现混合在一起影响了报表效率。

  实测表明，采用这种方式报表由计算到呈现耗时：45s，仅比SQL中的交叉表方式慢了2s，而比宽表方式快了44s。这里只与SQL的计算时间做对比，如果加上硬编码实现的报表呈现耗时，集算报表的方式无疑是最优的。

集算报表（集算器数据集）实现

  在集算器中也可以通过类似交叉表和宽表两种方式实现。

交叉表

  集算脚本参数如下：

  脚本如下：

	A
1	>groups=arg1.string()
2	=arg2.(“sum(“+~+”) as ” + ~ ).string()
3	=arg2.(“sum(“+~+”) as ” + ~+ “,sum(0) as ” + ~+”占比”).string()
4	=connect(“orcl”)
5	=A4.query(“select 0 as rn,”+groups+”,”+A2+” from 订单 where 货主地区 is not null group by “+groups+” order by rn,”+groups)
6	=A4.query(“select 0 as rn,”+groups+”,”+A3+”,extract(year from 订购日期) as years from 订单 where extract(year from 订购日期) in “+arg3+” group by “+groups+”,extract(year from 订购日期)”)
7	=A4.close()
8	>A5.run(RN=#)
9	>A6.run(RN=A5.pselect(~.([${groups}])==A6.~.([${groups}])))
10	=arg2.(~+”占比=”+~+”/A5(RN).”+~).string()
11	>A6.run(${A10})
12	result A5,A6

  通过A5和A6分别向报表返回结果集，其中A5结果集如下：

  A6结果集如下：

  在集算报表中使用集算器数据集引用上述dfx脚本：

  其中，ds1和ds2分别接收脚本中A6和A5格的返回结果。

  编辑报表模板：

  在报表中仍然使用ds.fname()和ds.field()动态获取分组汇总字段名和字段值。

  使用集算器数据集后，可以将报表的的计算和展现剥离开，避免使用SQL数据集中存在的计算和呈现混合导致性能不高、某些功能需要高端报表版本才能满足的问题；而使用集算器进行数据计算又避免了SQL实现中的数据重复复制的问题，脚本的编码还可以分步编写降低了实现难度。

  实测表明，使用该方式报表从计算到展现耗时：43s。可以看到通过使用集算器数据集，报表的性能又得到进一步提升。

宽表

  集算脚本（参数与交叉表中一致）如下：

	A	B	C
1	>groups=arg1.string()
2	=arg2.(“sum(“+~+”) as ” + ~ ).string()
3	=arg3.(arg2.(“sum(0) as ” +~+string(arg3.~)+ “,sum(0) as ” + ~+string(arg3.~)+”占比”).string()).string()
4	=connect(“orcl”)
5	=A4.query(“select “+groups+”,”+A2+”,”+A3+” from 订单 where 货主地区 is not null group by “+groups+” order by “+groups)
6	=A4.query(“select “+groups+”,”+A2+”,extract(year from 订购日期) as years from 订单 where extract(year from 订购日期) in (“+arg3.string()+”) group by “+groups+”,extract(year from 订购日期)”)
7	=A4.close()
8	for A6	=A5.select@1(~.([${groups}])==A8.([${groups}]))
9		for arg2	>eval(“B8.”+B9+string(A8.YEARS)+”=A8.”+B9)
10	result A5

  在集算报表中引用该脚本，报表模板编辑如下：

  类似宽表的做法与SQL中宽表的做法基本思路一致，但不再需要拼接动态SQL，而且由于不需要对源数据进行多次分组和过滤，其性能有较大提升。

  实测表名，使用该方式报表从计算到展现共耗时：48s，比SQL的宽表实现获得近一倍的性能提升。

  通过上面的对比，使用集算器数据集进行复杂动态列报表开发可以获得开发效率和性能上的双重提升，尤其在计算复杂度较高的情况下，使用集算器进行数据源准备的效果更加明显。

【附】硬件环境

处理器：Intel(R) Core(TM)i5-3210M CPU @2.5GHz 2.50GHz；

内存（RAM）:4GB(3.88GB可用)。