<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"> 最近在将200W左右的期货实时数据转换成5分钟数据,再将他代入SMA等函数,开头以为比较简单,不过后边想想难点还是不少。</span>
1.有噪点,有些时间段没有交易数据
2.区间非连续,期货中间会有休息时间
3.K线整合有业务规则,8点59和9点要合为一分钟
4.按5分钟作为一个统计区间,将开始值,结束值,最小值,最大值查询出来。
实时数据的表结构如下:
create table RB1410
(
ID NUMBER not null,
TRADETIME DATE,
TRADEPRICE NUMBER,
TRADECOUNT NUMBER,
ALLCOUNT NUMBER,
STATUS NUMBER
)
查询视图SQL语句:
create or replace view vrb1410_5 as
with
--常规的分钟数据
v1 as
(
select
trunc(tradetime,'hh24')+(trunc(to_char(tradetime,'mi')/5)+1)*5/(24*60) tt,
max(tradeprice)over(partition by (trunc(tradetime,'hh24')+trunc(to_char(tradetime,'mi')/5)*5/(24*60))) hp,
min(tradeprice)over(partition by (trunc(tradetime,'hh24')+trunc(to_char(tradetime,'mi')/5)*5/(24*60))) lp,
decode(row_number()over(partition by (trunc(tradetime,'hh24')+trunc(to_char(tradetime,'mi')/5)*5/(24*60)) order by tradetime),1,tradeprice,0) op,
decode(row_number()over(partition by (trunc(tradetime,'hh24')+trunc(to_char(tradetime,'mi')/5)*5/(24*60))order by tradetime desc),1,tradeprice,0) cp
from rb1410
where
to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')>=to_date('09:05','hh24:mi') and to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')< to_date('10:10','hh24:mi') or
to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')>=to_date('10:30','hh24:mi') and to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')< to_date('11:25','hh24:mi') or
to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')>=to_date('13:30','hh24:mi') and to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')< to_date('14:55','hh24:mi')
),
--8点59和9点的分钟数据整理
v2 as
(select
trunc(tradetime,'dd')+545/(24*60) tt,
max(tradeprice)over(partition by trunc(tradetime,'dd')) hp,
min(tradeprice)over(partition by trunc(tradetime,'dd')) lp,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime),1,tradeprice,0) op,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime desc),1,tradeprice,0) cp
from rb1410
where to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')<to_date('09:05','hh24:mi')),
--10点10分后
v3 as
(select
trunc(tradetime,'dd')+615/(24*60) tt,
max(tradeprice)over(partition by trunc(tradetime,'dd')) hp,
min(tradeprice)over(partition by trunc(tradetime,'dd')) lp,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime),1,tradeprice,0) op,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime desc),1,tradeprice,0) cp
from rb1410
where to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')>=to_date('10:10','hh24:mi') and to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')<to_date('10:16','hh24:mi')),
--11点25分之后
v4 as
(select
trunc(tradetime,'dd')+690/(24*60) tt,
max(tradeprice)over(partition by trunc(tradetime,'dd')) hp,
min(tradeprice)over(partition by trunc(tradetime,'dd')) lp,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime),1,tradeprice,0) op,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime desc),1,tradeprice,0) cp
from rb1410
where to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')>=to_date('11:25','hh24:mi') and to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')<to_date('11:31','hh24:mi')),
--14:55分钟之后
v5 as
(select
trunc(tradetime,'dd')+900/(24*60) tt,
max(tradeprice)over(partition by trunc(tradetime,'dd')) hp,
min(tradeprice)over(partition by trunc(tradetime,'dd')) lp,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime),1,tradeprice,0) op,
decode(row_number()over(partition by trunc(tradetime,'dd') order by tradetime desc),1,tradeprice,0) cp
from rb1410
where to_date(to_char(tradetime,'hh24:mi'),'hh24:mi')>=to_date('14:55','hh24:mi'))
select tt,max(hp) hp,max(lp) lp,max(op) op,max(cp) cp from (
select tt,hp,lp,op,cp from v1
union
select tt,hp,lp,op,cp from v2
union
select tt,hp,lp,op,cp from v3
union
select tt,hp,lp,op,cp from v4
union
select tt,hp,lp,op,cp from v5)
group by tt;
200W的数据,查询需要10秒,各位高手看有没有更高效的方法。