利用数据库存储过程计算K线均值算法

算法实现：

K线存储过程，定义一个n的日均线，存储过程如下图，初始值全部赋值为0

0	1	2	3	4	…	n-1
n%n	1%n	2%n	3%n	4%n	…	（n-1）%n

n  .....

Java程序直接处理从数据库中获取到的数据

①当i<n时，均值结果为avg=sum(a[0]，a[n-1])/i

②当i>=n时，均值结果为avg=sum(a[0]，a[n-1])/n

MySql存储过程算法实现：

 set i=i+1;
 IF i<n THEN
 SELECT SUM(rate) INTO total
 FROM lineK
 WHERE id>=1 AND id<=i;
 set rate_in_stock=total/i;
 END IF;

 IF i>=n THEN
 SELECT SUM(rate) INTO total
 FROM lineK
 WHERE id>=i+1-n AND id<=i;
 set rate_in_stock=total/n;
 END IF;

存储过程代码

DELIMITER $$

DROP PROCEDURE IF EXISTS `yggl`.`CursorProc` $$
CREATE PROCEDURE `yggl`.`CursorProc` (IN n INT)

BEGIN
 DECLARE  no_more_lineK ,i INT DEFAULT 0;
 DECLARE  total DOUBLE DEFAULT 0;
 DECLARE  rate_in_stock DOUBLE DEFAULT 0;
 DECLARE  SUM DOUBLE DEFAULT 0;
 DECLARE  prd_id VARCHAR(255);
 DECLARE  cur_lineK CURSOR FOR   SELECT id FROM lineK;  /*First: Delcare a cursor,首先这里对游标进行定义*/
 DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_lineK = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/

 /* for  loggging information 创建个临时表格来保持*/
 DROP TABLE IF EXISTS `infolog`;
 CREATE TABLE infolog (
 Id int(11) NOT NULL AUTO_INCREMENT,
 Rate DOUBLE NOT NULL,
 PRIMARY KEY (Id)
 );

 OPEN  cur_lineK; /*Second: Open the cursor 接着使用OPEN打开游标*/
 FETCH  cur_lineK INTO prd_id; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/

 REPEAT
 set i=i+1;
 IF i<n THEN
 SELECT SUM(rate) INTO total
 FROM lineK
 WHERE id>=1 AND id<=i;
 set rate_in_stock=total/i;
 END IF;

 IF i>=n THEN
 SELECT SUM(rate) INTO total
 FROM lineK
 WHERE id>=i+1-n AND id<=i;
 set rate_in_stock=total/n;
 END IF;

 INSERT  INTO infolog(Rate)
 VALUES  (rate_in_stock);

 FETCH  cur_lineK INTO prd_id;

 UNTIL  no_more_lineK = 1
 END REPEAT;
 CLOSE  cur_lineK;  /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
 SELECT *  FROM infolog;
END $$

DELIMITER ;

完整案例打包：http://download.csdn.net/detail/kevin_iot/9707791

点击打开链接

技术参考文档：

1、draw2D参考事例http://www.ibm.com/developerworks/cn/opensource/os-draw2dswt/

2、java连接mysql示例：

http://hzy3774.iteye.com/blog/1689525

3、hFreechart参考示例

http://www.iteye.com/problems/23761