Filling missing timeseries 1

[fosa]

Hi all,

I want to fill a minutelly historical table

CREATE TABLE MY_HISTO
(
ID_STOCK VARCHAR2(22 BYTE) NOT NULL,
SERVER_TIME TIMESTAMP(3) NOT NULL,
LOW NUMBER,
HIGH NUMBER,
OPEN NUMBER,
CLOSE NUMBER,
VOLUME NUMBER,
TRADE NUMBER
);

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:08.27.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),114,114,114,114
,37356,1 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:08.32.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),114,114.05,114,114.05
,95846,2 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:08.34.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),114,114,114.05,114
,50965,1 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:08.40.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),112.75,112.75,114,112.75
,52455,1 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:08.49.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),113.25,113.25,112.75,113.25
,53719,1 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:09.37.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),113,113.5,113.25,113.5
,498975,8 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:10.48.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),113.25,113.25,113.5,113.25
,672474,8 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:10.49.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),113.5,113.5,113.25,113.5
,98140,1 );


insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:10.50.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),113.25,113.25,113.5,113.25
,418216,4 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:10.51.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),113,113.25,113.25,113.25
,831882,5 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:10.52.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),113,113,113.25,113
,183417,1 );

insert into table1
values ('3IN LN Equity',to_timestamp('17/03/2008:11.00.00.000','DD/MM/YYYY:HH24.MI.SS.FF'),112.75,112.75,113,112.75
,535575,2 );


and i want to have this result

ID_STOCK SERVER_TIME LOW HIGH OPEN CLOSE VOLUME TRADE
3IN LN Equity 08:27:00 114 114 114 114 37356 1
3IN LN Equity 08:28:00 114 114 114 114 37356 0
3IN LN Equity 08:29:00 114 114 114 114 37356 0
3IN LN Equity 08:30:00 114 114 114 114 37356 0
3IN LN Equity 08:31:00 114 114 114 114 37356 0
3IN LN Equity 08:32:00 114 114.05 114 114.05 95846 2
3IN LN Equity 08:33:00 114 114.05 114 114.05 95846 0
3IN LN Equity 08:34:00 114 114.05 114 114.05 95846 0
3IN LN Equity 08:35:00 114 114.05 114 114.05 95846 0
3IN LN Equity 08:36:00 114 114.05 114 114.05 95846 0
3IN LN Equity 08:37:00 114 114.05 114 114.05 95846 0
3IN LN Equity 08:38:00 114 114.05 114 114.05 95846 0
3IN LN Equity 08:40:00 112.75 112.75 114 112.75 52455 1
3IN LN Equity 08:49:00 113.25 113.25 112.75 11My_HISO3.25 53719 1

So I want to fill the missing with the last record

I try this query

create type array
as table of number
/

CREATE OR REPLACE function
OWNER_INTRADAY.vtable( n in number default null )
return array
PIPELINED
as
begin
for i in 1 .. nvl(n,999999999)
loop
pipe row(i);
end loop;
return;
end;
/



with dates as
(
select to_date('17/03/2008 08:00:00','DD/MM/YYYY HH24:Mi:SS')+
(column_value-1)/24/60 dt
from TABLE(vtable(750))
)
select id_stock,
dt,
lag(dt)
over (partition by id_stock,dt
order by dt) last_dt,
close,
lag(close)
over (partition by id_stock
order by dt) last_prices
from (
select id_stock,
dt,
close
from dates left outer join
MY_HISTO partition by (id_stock,close)
on (dates.dt =trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60)
)
where id_stock='3IN LN Equity';

select * from my_histo;

but it doesn't fill .

How can I do to add missing times

 

[jimirvine]

Try:

WITH dates AS (SELECT TO_DATE('17/03/2008 08:26:00','DD/MM/YYYY HH24:Mi:SS')+LEVEL/(24*60) dt
               FROM DUAL
               CONNECT BY LEVEL <= 60
               )
SELECT LAST_VALUE(id_stock  IGNORE NULLS)OVER (PARTITION BY id_stock) id_stock
      ,dt
      ,LAST_VALUE(low       IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT) low
      ,LAST_VALUE(high      IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT) high
      ,LAST_VALUE(open      IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT) open
      ,LAST_VALUE(close     IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT) close
      ,LAST_VALUE(volume    IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT) volume
      ,NVL(trade,0)
FROM dates 
LEFT JOIN my_histo m 
PARTITION BY (id_stock)
ON dates.dt = m.server_time
ORDER BY dt;

 

[fosa]

Thanks a lot

you did it

 

[SantaMufasa]

Fosa,

Jim's solution is very clever and tightly coded. I know you appreciate his solution. In addition to an explicit "Thanks a lot," a convention we use at Tek-Tips to express appreciation is also to click the link, above, entitled, [Thank jimirvine for this valuable post!]. Doing so awards Jim a well-deserved

.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fosa]

Ah, whooops

the next time I will do it

So, i give hive five starts

Thanks