基于postgresql的自动报表生成工具---一种基于OOSE的方法

残缺极品

1）SQL 性能提升

select
c.scan_start_time,
a.zh_name,
a.cgi,
b.region_name,
round(round(decode(c.succinitalsetup,0,0,(c.attrelenb-c.attrelenbnormal)/c.succinitalsetup),4),6) Wirelessdrops,
c.attrelenb,
c.attrelenbnormal,
c.succinitalsetup,
round(round(decode(c.nbrattestab,0,0,c.nbrsuccestab/c.nbrattestab)*decode(c.attconnestab,0,0,c.succconnestab/c.attconnestab),4),6) Wirelessconnection,
c.nbrsuccestab,
c.nbrattestab,
c.succconnestab,
c.attconnestab
from tcc_eutrancell a,outer region_city b,outer tpa_eutrancell_ne c
where a.scan_start_time=extend(current,year to day)-1 units day
and c.scan_start_time in (select first_result from tcc_time_snap where first_result >=extend(current,year to day)-1 units day
and first_result <=extend(current,year to day) and time_type=0 and s_hour in (0,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23))
and c.sum_level=0 and c.ne_type=8105 and a.state=1 and a.int_id=c.int_id and
a.city_id=b.city_id;

2）优化的SQL

select
c.scan_start_time,
a.zh_name,
a.cgi,
b.region_name,
round(round(decode(c.succinitalsetup,0,0,(c.attrelenb-c.attrelenbnormal)/c.succinitalsetup),4),6) Wirelessdrops,
c.attrelenb,
c.attrelenbnormal,
c.succinitalsetup,
round(round(decode(c.nbrattestab,0,0,c.nbrsuccestab/c.nbrattestab)*decode(c.attconnestab,0,0,c.succconnestab/c.attconnestab),4),6) Wirelessconnection,
c.nbrsuccestab,
c.nbrattestab,
c.succconnestab,
c.attconnestab
from eutrancell a, region_city b, tpa_eutrancell_ne c
where c.scan_start_time in (
extend(current,year to day)-1 units day || ' 08:00:00',
extend(current,year to day)-1 units day || ' 09:00:00',
extend(current,year to day)-1 units day || ' 10:00:00',
extend(current,year to day)-1 units day || ' 11:00:00',
extend(current,year to day)-1 units day || ' 12:00:00',
extend(current,year to day)-1 units day || ' 13:00:00',
extend(current,year to day)-1 units day || ' 14:00:00',
extend(current,year to day)-1 units day || ' 15:00:00',
extend(current,year to day)-1 units day || ' 16:00:00',
extend(current,year to day)-1 units day || ' 17:00:00',
extend(current,year to day)-1 units day || ' 18:00:00',
extend(current,year to day)-1 units day || ' 19:00:00',
extend(current,year to day)-1 units day || ' 20:00:00',
extend(current,year to day)-1 units day || ' 21:00:00',
extend(current,year to day)-1 units day || ' 22:00:00',
extend(current,year to day)-1 units day || ' 23:00:00'
)
and c.sum_level=0 and c.ne_type=8105 and a.state=1 and a.int_id=c.int_id and a.city_id=b.city_id;

这一次的改进主要在那些方面呀？

1、去掉了外关联
2、去掉了关联快照资源，直接关联的是当天的资源，因为当天的资源表比较小，快照资源表里面数据量很大，取的时候还要确定时间条件，所以取的是当天的
3、取性能表的时间用的是时间截取的写法（之前是关联的我们放时间粒度的一个表，需要做很多关联，所以比较慢）

这个时间截取，可以很多效率吗？

恩，是的，最主要是这个时间截取 取时间的时候，直接把时间写成明确的时间点，比关联取时间点效率高很多