数据库中生成数据透视表的SQL的效率比较

2009-12-14 18:52| 发布者: admin| 查看: 67| 评论: 0|原作者: 琴姬

●（1）以下SQL语句执行效率较低，大约在......

　　（1）以下SQL语句执行效率较低，大约在90分钟执行完毕
select distinct a.pulse_id,a.field_id,
(select sum(kwh_val) from his_pbs_20041201 where pulse_id = a.pulse_id and to_number(to_char(unix_to_oracle(occur_time),'hh24'))=0 ) as v0,
(select sum(raw_val) from his_pbs_20041201 where pulse_id = a.pulse_id and to_number(to_char(unix_to_oracle(occur_time),'hh24'))=0 ) as r0,
(select sum(status) from his_pbs_20041201 where pulse_id = a.pulse_id and to_number(to_char(unix_to_oracle(occur_time),'hh24'))=0 ) as st0,
from his_pbs_20041201 a
where pulse_id = 164000029;
　　（2）通过在SQL中加入Case语句，可以极大提高SQL的执行速度
select distinct a.pulse_id,a.field_id,
sum(case to_number(to_char(unix_to_oracle(occur_time),'hh24')) when 0 then kwh_val else 0 end) as v0,
sum(case to_number(to_char(unix_to_oracle(occur_time),'hh24')) when 0 then raw_val else 0 end) as r0,
sum(case to_number(to_char(unix_to_oracle(occur_time),'hh24')) when 0 then status else 0 end) as st0,
from his_pbs_20041201 a
group by a.pulse_id,a.field_id;
　　以上两种取得数据透视表的方法在Oracle与Sql Server中均适用。第一种方法要耗时90分钟，第二种方法只需3分钟即可。

收藏分享邀请

下级分类

GMT+8, 2024-9-30 09:21 , Processed in 0.085969 second(s), 12 queries , Gzip On, MemCache On.

		自动登录	找回密码
密码			注册

数据库中生成数据透视表的SQL的效率比较

最新评论

相关分类

下级分类