独角兽企业重金招聘Python工程师标准>>>
#处理采集数据DROP PROCEDURE IF EXISTS do_collect_price;CREATE PROCEDURE do_collect_price()BEGIN DECLARE flag INT DEFAULT 0;#声明游标循环标识DECLARE now_time INT DEFAULT UNIX_TIMESTAMP();#声明游标循环标识DECLARE start_time int DEFAULT UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now())-1 day),interval 1 month));DECLARE end_time int DEFAULT UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval 0 month));DECLARE percent FLOAT DEFAULT 0.1;#去除最高最低价格百分比DECLARE date_time char(8) DEFAULT extract(year_month from NOW());#当前年份月份10DECLARE num int DEFAULT 0;#总数DECLARE t_cut int DEFAULT 0;#需要去掉的价格一半DECLARE t_total int DEFAULT 0;#有效价格总数DECLARE temp_Price DECIMAL(18,4);DECLARE tMaterialID char(30);DECLARE tMaterialName char(250);DECLARE tMaterialUnit char(150);DECLARE tMaterialFormat char(150);DECLARE cur CURSOR FOR SELECT MaterialID,MaterialName,MaterialUnit,MaterialFormat,count(MaterialID) as num FROM collect_price WHERE add_time > start_time AND add_time < end_time GROUP BY MaterialID,MaterialName,MaterialUnit,MaterialFormat;DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;OPEN cur;#删除jcj_price当月价格包信息DELETE FROM jcj_price WHERE PriceMonth = date_time;#删除pricepacklist可用期间信息DELETE FROM pricepacklist WHERE packid = 1020 and packmonth = date_time;FETCH cur INTO tMaterialID,tMaterialName,tMaterialUnit,tMaterialFormat,num;WHILE flag <> 1 DOIF num < 3THENselect avg(MaterialPrice) from (select MaterialPrice from collect_price where add_time > start_time and add_time < end_time and MaterialID=tMaterialID and MaterialName=tMaterialName and MaterialUnit=tMaterialUnit and MaterialFormat=tMaterialFormat) p INTO temp_Price;ELSESET t_cut = ROUND(num*percent);SET t_total = num - 2*t_cut;select avg(MaterialPrice) from (select MaterialPrice from collect_price where add_time > start_time and add_time < end_time and MaterialID=tMaterialID and MaterialName=tMaterialName and MaterialUnit=tMaterialUnit and MaterialFormat=tMaterialFormat order by MaterialPrice asc limit t_cut,t_total) p INTO temp_Price;END IF;insert into jcj_price(MaterialID,PriceMonth,Price,MaterialName,MaterialUnit,MaterialFormat)values(tMaterialID,date_time,temp_Price,tMaterialName,tMaterialUnit,tMaterialFormat);FETCH cur INTO tMaterialID,tMaterialName,tMaterialUnit,tMaterialFormat,num;END WHILE;insert into pricepacklist(packid,packmonth,packver,packname,periodtype,enabled)values(1020,date_time,1,'',1,0);#备份收集数据#SELECT * INTO collect_price_bakup FROM collect_price WHERE add_time > start_time and add_time < end_time;#DELETE FROM collect_price WHERE add_time > start_time and add_time < end_time;CLOSE cur;END#call pricepack.do_collect_price()
转载自:http://www.9958.pw/post/mysql_procedure
