大数量下采用sql的case语句替代分段函数plot的方法
报表需求
业务:主要供营业部柜台人员,对客户数量、有效客户数、客户资产、中高端客户进行统计。
样式:
数据量: 需要统计的数据量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>=10000000) then 1 else 0 end) as sum2, sum(case when (ZB229>=10000000) then ZB229 else 0 end) as sum3, sum(case when (ZB229>=5000000 and ZB229<10000000) then 1 else 0 end) as sum4, sum(case when (ZB229>=5000000 and ZB229<10000000) then ZB229 else 0 end) as sum5, sum(case when (ZB229>=3000000 and ZB229<5000000) then 1 else 0 end) as sum6, sum(case when (ZB229>=3000000 and ZB229<5000000) then ZB229 else 0 end) as sum7, sum(case when (ZB229>=500000 and ZB229<3000000) then 1 else 0 end) as sum8, sum(case when (ZB229>=500000 and ZB229<3000000) then ZB229 else 0 end) as sum9, sum(case when (ZB229<500000) then 1 else 0 end)as sum10, sum(case when (ZB229<500000) then ZB229 else 0 end)as sum11, sum(case when (ZB229>500000) then ZB01 else 0 end)as sum12, sum(case when (ZB229>500000) then 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本身的运算,数据的传输,报表的运算效率都能得到一定程度的提高。