· 4 years ago · Dec 23, 2020, 01:56 PM
1DROP PROCEDURE IF EXISTS agent_analysis;
2CREATE PROCEDURE `agent_analysis`(
3 IN v_symbol varchar(6),
4 IN v_m1 integer,
5 IN v_m2 integer,
6 IN v_m3 integer,
7 IN v_window integer,
8 IN v_limit integer,
9 IN v_period integer,
10 IN v_tbl varchar(20),
11 IN v_date1 char(10),
12 IN v_date2 char(10) )
13begin
14
15 DECLARE v_done INT DEFAULT FALSE;
16 DECLARE v_id integer;
17 DECLARE v_price decimal(20,8);
18 DECLARE v_o decimal(20,8);
19 DECLARE v_c decimal(20,8);
20 DECLARE v_l decimal(20,8);
21 DECLARE v_h decimal(20,8);
22 DECLARE v_add_date datetime;
23
24 DECLARE cur CURSOR FOR SELECT id,price,o,c,l,h,add_date FROM vm_proc order by id;
25 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
26
27 SET @stmt = concat("CREATE VIEW vm_proc as SELECT id,symbol,price,o,c,l,h,add_date FROM ", v_tbl, " where symbol = '",v_symbol,"' and add_date between '",v_date1,"' and '",v_date2,"'");
28 PREPARE stm FROM @stmt;
29 EXECUTE stm;
30 DEALLOCATE PREPARE stm;
31
32
33 OPEN cur;
34 read_loop: LOOP
35
36 FETCH cur INTO v_id,v_price,v_o,v_c,v_l,v_h,v_add_date;
37 IF v_done THEN
38 LEAVE read_loop;
39 END IF;
40
41 set @prev_price1 = (select ifnull(price,0) from vm_proc where id < v_id order by id desc limit 0,1 );
42 set @prev_price2 = (select ifnull(avg(price),0) from vm_proc where id < v_id order by id desc limit 0,v_period );
43
44 set @ema1 = ( select avg(price) from ( select price from vm_proc where id <= v_id order by id desc limit 0,v_m1 )a );
45 set @ema2 = ( select avg(price) from ( select price from vm_proc where id <= v_id order by id desc limit 0,v_m2 )a );
46 set @ema3 = ( select avg(price) from ( select price from vm_proc where id <= v_id order by id desc limit 0,v_m3 )a );
47
48 set @min = ( select min(price) from vm_proc where id <= v_id order by id desc limit 0,v_window);
49 set @max = ( select max(price) from vm_proc where id <= v_id order by id desc limit 0,v_window);
50
51 set @k = round(((v_price-@min)/(@max-@min))*100,2);
52 set @k2 = ( select round( (price-@min)/(@max-@min)*100,2) from ( select avg(price) as price from vm_proc where id <= v_id order by id desc limit 0,v_limit) a);
53
54 set @rst = ( select avg(price-v_price) from vm_proc where price > v_price order by id desc limit 0,v_window);
55 set @rsb = ( select avg(v_price-price) from vm_proc where price < v_price order by id desc limit 0,v_window);
56
57 set @ifr = round( (@rst/(@rst+@rsb))*100,2);
58
59 INSERT ignore INTO tmp_market_analysis
60 (symbol, p, o, c, l, h, d1, v1, d2, v2, k,k2,ema1, ema2, ema3, rst, rsb, ifr, add_date)
61 VALUES (v_symbol, v_price, v_o, v_c, v_l, v_h, (v_price - @prev_price1), 0, (v_price - @prev_price2), 0, @k,@k2,@ema1, @ema2, @ema3, @rst, @rsb, @ifr, v_add_date);
62
63 END LOOP;
64
65 CLOSE cur;
66 DROP VIEW vm_proc;
67
68end;
69
70
71DROP PROCEDURE IF EXISTS agent_analysis2;
72CREATE PROCEDURE `agent_analysis2`(
73 IN v_m3 integer
74)
75begin
76
77 DECLARE v_done INT DEFAULT FALSE;
78 DECLARE v_id integer;
79 DECLARE v_d1 DECIMAL(20,8);
80 DECLARE v_d2 DECIMAL(20,8);
81 DECLARE v_ema1 DECIMAL(20,8);
82 DECLARE v_ema2 DECIMAL(20,8);
83
84 DECLARE cur CURSOR FOR SELECT id, d1,d2,ema1,ema2 FROM tmp_market_analysis order by id asc;
85 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
86
87 OPEN cur;
88 read_loop: LOOP
89
90 FETCH cur INTO v_id,v_d1,v_d2,v_ema1,v_ema2;
91 IF v_done THEN
92 LEAVE read_loop;
93 END IF;
94
95 set @prev_d1 = ( select d1 from tmp_market_analysis where id < v_id order by id desc limit 0,1);
96 set @prev_d2 = ( select d2 from tmp_market_analysis where id < v_id order by id desc limit 0,1);
97
98 set @ema3 = ( select avg(c) from ( select (ema1-ema2) c from tmp_market_analysis where id <= v_id order by id desc limit 0,v_m3 )a );
99 set @macd = (v_ema1-v_ema2) - (@ema3*(v_m3/(v_m3+1)) + (v_ema1-v_ema2)*(1/(v_m3+1)));
100
101 update tmp_market_analysis set v1 = case when d1 < 0 then 0-abs( (d1 - @prev_d1)*100/@prev_d1) else abs( (d1 - @prev_d1)*100/@prev_d1) end , v2 = case when d2 <0 then 0-abs((d2 - @prev_d2)*100/@prev_d2) else abs((d2 - @prev_d2)*100/@prev_d2) end , macd = @macd , ema3 = v_ema1-v_ema2 where id = v_id;
102
103 END LOOP;
104
105 CLOSE cur;
106
107 update tmp_market_analysis a inner join tmp_market_analysis b on a.id = b.id+1 set a.macv = a.macd - b.macd;
108
109end;
110
111
112DROP PROCEDURE IF EXISTS agent_analysis3;
113CREATE PROCEDURE `agent_analysis3`(
114 IN v_tspan integer,
115 IN v_pspan integer
116)
117begin
118
119 DECLARE v_done INT DEFAULT FALSE;
120 DECLARE v_id integer;
121 DECLARE v_p_level decimal(20,8);
122 DECLARE v_p_range decimal(20,8);
123 DECLARE v_h decimal(20,8);
124 DECLARE v_l decimal(20,8);
125 DECLARE v_cnt integer;
126 DECLARE v_date datetime;
127
128 DECLARE cur CURSOR FOR SELECT id,p_level,p_range,h,l,cnt,`date` FROM tmp_market_cluster_analysis order by id;
129 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
130
131
132 truncate table tmp_market_cluster_analysis;
133 truncate table tmp_market_zigzag_analysis;
134 truncate table tmp_market_price_level;
135
136 insert into tmp_market_cluster_analysis ( p_level, p_range, cnt, l, h, `date` )
137 select round((p-ml)/(d/v_pspan)) p, ml + round((p-ml)/(d/v_pspan))*(d/v_pspan) t, count(*) cnt, min(l) l, max(h) h, from_unixtime(round(unix_timestamp(add_date)/v_tspan)*v_tspan) add_date from
138 tmp_market_analysis , (select min(l)ml, max(h)mh, max(h)-min(l)d from tmp_market_analysis) b
139 group by round(unix_timestamp(add_date)/v_tspan), round((p-ml)/(d/v_pspan)) order by round(unix_timestamp(add_date)/v_tspan) asc;
140
141 insert into tmp_market_price_level ( p_level, p_range, cnt, l, h)
142 select round((p-ml)/(d/v_pspan)) p, ml + round((p-ml)/(d/v_pspan))*(d/v_pspan) t, count(*) cnt, min(l) l, max(h) h from
143 tmp_market_analysis , (select min(l)ml, max(h)mh, max(h)-min(l)d from tmp_market_analysis) b
144 group by round((p-ml)/(d/v_pspan)) order by round((p-ml)/(d/v_pspan)) asc;
145
146 set @prev_cnt = 0;
147 set @prev_direction = '';
148 set @i = 0;
149
150 OPEN cur;
151 read_loop: LOOP
152
153 FETCH cur INTO v_id,v_p_level,v_p_range,v_h,v_l,v_cnt,v_date;
154 IF v_done THEN
155 LEAVE read_loop;
156 END IF;
157
158 set @i = @i+1;
159
160 IF(@i = 1) THEN
161 set @prev_date = v_date;
162 set @prev_pl = v_p_level;
163 set @prev_l = v_l;
164 set @prev_h = v_h;
165 END IF;
166
167 IF (v_p_level > @prev_p_level ) THEN
168 set @direction = 'up';
169 ELSEIF ( v_p_level < @prev_p_level) THEN
170 set @direction = 'down';
171 ELSE
172 set @direction = 'stay';
173 END IF;
174
175 set @duration = @duration + v_cnt;
176
177 IF (@prev_direction != @direction && @direction != 'stay') THEN
178
179 IF(v_l < @prev_l) THEN
180 set @l = v_l;
181 ELSE
182 set @l = @prev_l;
183 END IF;
184 IF(v_h > @prev_h) THEN
185 set @h = v_h;
186 ELSE
187 set @h = @prev_h;
188 END IF;
189
190 set @depth = @h-@l;
191 set @pl_start = @prev_pl;
192 set @pl_end = v_p_level;
193
194 INSERT ignore INTO tmp_market_zigzag_analysis
195 (direction, duration, depth, pl_start, pl_end, l, h, start_date, end_date)
196 VALUES (@direction, @duration, @depth, @pl_start, @pl_end, @l, @h, @prev_date, v_date);
197
198 set @prev_h = v_h;
199 set @prev_l = v_l;
200 set @duration = 0;
201 set @prev_date = v_date;
202 set @prev_pl = v_p_level;
203
204 END IF;
205
206 set @prev_p_level = v_p_level;
207 set @prev_direction = @direction;
208
209 END LOOP;
210
211 CLOSE cur;
212
213end;
214