oracle查询5分钟K线数据

<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;