数据库中生成数据透视表的SQL的效率比较
来源:互联网 发布:mac os x 10.11.5 编辑:程序博客网 时间:2024/06/03 15:49
(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;
(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;
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分钟即可。
- 数据库中生成数据透视表的SQL的效率比较
- 数据透视表生成的简略步骤
- SQL Server 生成数据透视表
- Sql Server 生成数据透视表(交叉分析数据)
- Excel的数据透视表
- 怎样将一个字符串与数据库中加密过(生成二进流)的数据比较
- 数据库集中分页效率的比较
- SQLServer中监视sql执行的时间,比较sql语句的效率
- SQLServer中监视sql执行的时间,比较sql语句的效率
- 生成表中数据的sql语法的存储过程
- 数据透视表的VLOOKUP模糊查找
- “筛选”与数据透视表的筛选
- 数据透视表的发明历史
- python3的数据透视表pivot_table
- 用Python实现数据的透视表
- 用SQL行列转换实现数据透视的一些思考
- SQL语句生成SQL Server数据库的数据字典
- 10万数据量的数据库查询效率比较 纯sql语句 和 view 视图
- C语言面试题精粹(三)
- Oracle时间与Unix时间戳的转换
- .NET面试题
- 2005年Delphi认证考试真实考题
- Delphi 认证集
- 数据库中生成数据透视表的SQL的效率比较
- Delphi软件工程师试题
- Java读取Excel方式对比
- ReversingLabsProtector 0.7.4beta by Ap0x
- dijkstra算法的Applet演示
- 博士论文的开题报告写作
- 图像分区算法
- 因为没有犯错,意味着工作不够多-Intel企业文化
- 如何做研究