关注一下。
不过在SQLSERVER里最后求95%概率不需要分两步,一步就可以了。
我觉得也不需要做循环,游标或循环的效率不高。
“表tmp 中的记录按时间排序,然后两个小时的记录作为一组,
求得time的中间值(该组的开始时间再加一个小时),”这一部是要生成一个结果集。生成的结果集只要一个SQL语句应该就可以得到最后的结果集了。不知道你分组的结果集的格式。要不我写给你看了:)以上的看法应该在ORACLE中也是一样,应该不需要用循环的。
不过在SQLSERVER里最后求95%概率不需要分两步,一步就可以了。
我觉得也不需要做循环,游标或循环的效率不高。
“表tmp 中的记录按时间排序,然后两个小时的记录作为一组,
求得time的中间值(该组的开始时间再加一个小时),”这一部是要生成一个结果集。生成的结果集只要一个SQL语句应该就可以得到最后的结果集了。不知道你分组的结果集的格式。要不我写给你看了:)以上的看法应该在ORACLE中也是一样,应该不需要用循环的。
解决方案 »
- 关于aa<=to_Date('20120731','yyyymmdd')的问题
- 存储过程中with as的应用
- 关于在eclipse的data source explorer上连接oracle 11g数据库的存储过程问题
- v$osstat各字段的含义
- oracle查询语句 求各种大神指点
- shmmax 的这句话要怎么理解!
- 在线等待解决问题:vc中如何实现oracle数据库的net服务名配置
- 能不能用一条SQL语句,令某个sequence变为0?
- ODBC怎样调用存储过程?
- 怎样根据错误号查找相应的帮助?
- 千万火急求救,高分赠送,怎样备份oracle数据库所有对象(包括表,存储过程,函数,触发器…)
- 怎样恢复我的"OracleOraHome81TNSListener"服务?
2、您可能还没有明白我的意思吧,您再仔细看一下我的需求谢谢关注。
1、两个SQL语句是可以合成一个,但我觉得SQLSERVER对它的处理是一样的。
2、您可能还没有明白我的意思吧,您再仔细看一下我的需求谢谢关注。
请解释一下
什么情况下ORACLE使用的临时表?
什么情况下临时表是可以提高性能的?
您说的这段话我比较认同可惜我的问题还没怎么解决
您号召他们关注一下吗!
我相信ORACLE在性能上要比SQL Server 好。
^=^
我也相信ORACLE
牛,
并且我下决心与她为伴,靠她吃饭
create table time_period
( start_time date,
end_time date);
数据示例如下:
start_time end_time
----------------- -------------
02-12-12 12:00:00 02-12-12 13:59:59
02-12-12 14:00:00 02-12-12 15:59:59
02-12-12 16:00:00 02-12-12 17:59:592.用一句SQL能>95的返回结果集:
select y.start_time + 1, value
from tmp x, time_period y
where x.time between x.start_time and x.end_time
and percent_rank() over (partition by y.start_time, order by value desc) < 0.05;
1. create temp table
create table time_period
(start_time date,
end_time date,
rowcount number(10),
per95_pos number(10));2. init time_period
start_time end_time
----------------- -----------------
11-04-02 12:00:00 11-04-02 13:59:59
11-04-02 14:00:00 11-04-02 15:59:59
11-04-02 16:00:00 11-04-02 17:59:59
11-04-02 18:00:00 11-04-02 19:59:59
3. update time_period with rowcount, per95_pos
update time_period x
set rowcount = (select count(*) from tmp
where time between x.start_time and x.end_time);
update time_period x
set per95_pos = trunc(rowcount * .05);4. get recordset you want:
select y.start_time + 1, value
from tmp x, time_period y
where x.time between x.start_time and x.end_time
and trunc(percent_rank() over (partition by y.start_time, order by value desc) * rowcount) = per95_pos;
您这样做的方法会比我用的方法性能好吗?
若是,
请解释一下原因好吗?
create table time_period
(start_time date,
end_time date);2. init time_period
start_time end_time
----------------- -----------------
11-04-02 12:00:00 11-04-02 13:59:59
11-04-02 14:00:00 11-04-02 15:59:59
11-04-02 16:00:00 11-04-02 17:59:59
11-04-02 18:00:00 11-04-02 19:59:59
3. get all records which belongs top 5% and save to a temp table
create table tmp2 as
select *
from (select y.start_time + 1 cur_time, value,
percent_rank() over (partition by y.start_time order by value desc) pr
from tmp x, time_period y
where x.time between y.start_time and y.end_time) x
where pr < .05;4. get final recordset:
select cur_time, value
from tmp2 x
where pr = (select max(pr)
from tmp2
where cur_time = x.cur_time);comments:
your performance problem looks most likely because of 2 reasons:
1. looping many times based on time periods caused many times of table read. it's always better to reduce table read times, especially on large tables. obviously, in my solution, system will only read from table 'tmp' once, I believe it's much better solution than your original one.
2. 'order by' activities will slow down query speed seriously, especially on large tables. because 'order by' only works on the query result instead of base tables. that means each 'order by' activity will force system to get all rows from result set before performing. so it's better to aviod using 'order by' if possible. so in my solution, I use 'order by' clause only once.I believe there must be other solutions better than mine. but I don't have enough data volumn to run a full test on performance. You are more than welcome to give out more detail information about your situation, like how many rows in main table, how many for each time period, indexes and etc, then we can discuss it deeply further.I'm sorry to write it in English, there are some problem with my NT4 and Chinese IME.Good Luck!