1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
create a pipelined function
CREATE OR REPLACE FUNCTION F_DAILY_BAR
(
V_ticker IN VARCHAR2 )
RETURN EVENT_table_type PIPELINED
IS
BEGIN
DECLARE
v_max_tick data_event.id_stock%type;
v_max_price data_event.price%type;
v_max_vol data_event.volume%type;
v_max_time data_event.server_time%type;
v_min_price data_event.price%type;
v_min_tick data_event.id_stock%type;
v_min_time data_event.server_time%type;
v_volume data_event.volume%type;
v_close data_event.volume%type;
l_limit NUMBER := 1000;
type typ_tab_data
IS
TABLE OF barre_minute%ROWTYPE;
v_incoming typ_tab_data;
v_outgoing EVENT_ROW_TYPE;
l_done BOOLEAN;
CURSOR c2_cur
IS
SELECT id_stock,
dt ,
high ,
low ,
OPEN ,
CLOSE ,
volume ,
trade
FROM
( WITH dates AS
(SELECT (TRUNC(sysdate) + 8/24)+(LEVEL -1) /(24*60) dt
FROM DUAL CONNECT BY LEVEL <= 800
)
SELECT LAST_VALUE(id_stock IGNORE NULLS)OVER (PARTITION BY id_stock) id_stock ,
dt ,
CASE
WHEN trade IS NULL
THEN LAST_VALUE(CLOSE IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT)
ELSE low
END low ,
CASE
WHEN trade IS NULL
THEN LAST_VALUE(CLOSE IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT)
ELSE high
END high ,
CASE
WHEN trade IS NULL
THEN LAST_VALUE(CLOSE IGNORE NULLS) OVER (PARTITION BY id_stock ORDER BY DT)
ELSE OPEN
END 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) trade
FROM dates
LEFT JOIN vw_barre_min m PARTITION BY (id_stock)
ON dates.dt = m.server_time
ORDER BY dt
)
WHERE id_stock=v_ticker;
BEGIN
SELECT id_stock,
price ,
TIME ,
volume
INTO v_max_tick ,
v_max_price ,
v_max_time ,
v_max_vol
FROM UTIL_HISTO_EVENT
WHERE id_stock=V_ticker;
SELECT TIME ,
id_stock,
price
INTO v_min_time,
v_min_tick,
v_min_price
FROM
(SELECT TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60 TIME,
id_stock ,
price ,
volume ,
rank() over (partition BY id_stock order by TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60 ASC ,price ASC, volume ASC ) rank
FROM mv_barre_event
GROUP BY id_stock,
price ,
volume ,
TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60
ORDER BY TRUNC(server_time, 'hh24') + (TRUNC(TO_CHAR(server_time,'mi')))/24/60 ASC
)
WHERE rank =1
AND id_stock=v_ticker;
OPEN c2_cur;
LOOP
FETCH c2_cur bulk collect
INTO v_incoming limit l_limit;
l_done:= c2_cur%NOTFOUND;
FOR i IN 1..v_incoming.couNT
LOOP
IF (v_incoming(i).open IS NULL AND v_max_tick=v_incoming(i).id_stock AND v_incoming(i).id_stock=v_ticker) THEN
v_incoming(i).open := v_max_price;
v_incoming(i).close := v_max_price;
v_incoming(i).low := v_max_price;
v_incoming(i).high := v_max_price;
v_incoming(i).volume:=v_max_vol;
END IF;
IF (v_incoming(i).server_time=v_min_time AND v_incoming(i).id_stock=v_ticker) THEN
v_incoming(i).open := v_min_price;
END IF;
v_outgoing :=event_row_type(v_incoming(i).id_stock, v_incoming(i).server_time, v_incoming(i).open, v_incoming(i).low, v_incoming(i).close, v_incoming(i).high, v_incoming(i).volume, v_incoming(i).trade );
-- pipe row(event_row_type(r.id_stock,r.dt,r.open,r.low,r.high,r.close,r.volume,r.trade));
pipe row (v_outgoing);
END LOOP;
RETURN ;
END LOOP;
CLOSE c2_cur;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END F_DAILY_BAR;
/ |
