大数量下采用sql的case语句替代分段函数plot的方法
1. 报表需求
业务：主要供营业部柜台人员，对客户数量、有效客户数、客户资产、中高端客户进行统计。
样式：
数据量需要统计的数据量200-500万。
性能要求：秒级出来报表，支持50左右的并发。
2. 原实现方式
2.1 数据集
2.2 报表设计
从表样式看起来非常简单，主要是对营业部进行分组，同时对资产进行分段即可完成报表。
2.3 性能问题
后期用户对性能进行跟踪测试的结果：
汇总数据量 |
总数据量 |
Web方式 |
65万 |
65万 |
112秒 |
130万 |
130万 |
7分30秒以后出现内存占用太多，无法正常计算报表 |
|
|
增加内存后，报表能正常显示，不过数据库运算时间，报表运算时间都在20s左右，离用户的需求还是有不小差距。
3. 解决方案
3.1 问题分析
在报表设计时，采取把数据一次提前到报表内存中运运算， 当查询出来的数据量超过一定的范围（本案例中为32万条）时，就会导致内存不够使用，报表查询效率比较低。
根据报表本身不复杂，只要调整sql，让涉及到数据的汇总，放到case中实现，报表本身只是做数据的展现，页面汇总，百分比等运算。
3.2 调整后的sql
SELECT JGID, COUNT(khid) as sum0, COUNT(DECODE(ZB158,’1′,1,NULL)) as sum1, sum(case when (ZB229>=10000000then 1 else 0 endas sum2, sum(case when (ZB229>=10000000then ZB229 else 0 endas sum3, sum(case when (ZB229>=5000000 and ZB229<10000000then 1 else 0 endas sum4, sum(case when (ZB229>=5000000 and ZB229<10000000then ZB229 else 0 endas sum5, sum(case when (ZB229>=3000000 and ZB229<5000000then 1 else 0 endas sum6, sum(case when (ZB229>=3000000 and ZB229<5000000then ZB229 else 0 endas sum7, sum(case when (ZB229>=500000 and ZB229<3000000then 1 else 0 endas sum8, sum(case when (ZB229>=500000 and ZB229<3000000then ZB229 else 0 endas sum9, sum(case when (ZB229<500000then 1 else 0 end)as sum10, sum(case when (ZB229<500000then ZB229 else 0 end)as sum11, sum(case when (ZB229>500000then ZB01 else 0 end)as sum12, sum(case when (ZB229>500000then ZB27 else 0 end)as sum13, sum(ZB01) as sum14, sum(ZB27)as sum15 FROM T_ZB_KH_Y WHERE NY =? and (JGID = ? or ? is null) group by JGID |
3.3 调整后的报表设计
3.4 调整后的性能表现
根据日志，调整后，同样250万数据量下，数据库的查询时间耗费2秒，报表运算耗费时间1秒，能满足用户的具体需求。
4. 总结
分段函数plot，需要避免在 数据集查询数据量比较大时，做全表扫描操作。
把对数据库的汇总，分组在数据库层面进行，这样sql本身的运算，数据的传输，报表的运算效率都能得到一定程度的提高。