mysql - 如果数据集中存在间隙,使用 mysql 计算移动平均值会导致问题
问题描述
我的问题是我尝试计算表中某些值的移动平均值(每行一个平均值)。它确实有效,但如果涉及到id[20,18,17]或date[2018-05-11,2018-05-9,2018-05-8]之类的空白,计算就会出错。我正在寻找一种方法来使用特定数量的下一行来防止这种情况发生。
该表包含id (auto_increment), date and close (Float)
.
这是我的代码:
CREATE DEFINER=`root`@`localhost` PROCEDURE `moving_avg`(IN periode INT)
NO SQL
BEGIN
select hist_ask.id, hist_ask.date, hist_ask.close, round(avg(past.close),2) as mavg
from hist_ask
join hist_ask as past
on past.id between hist_ask.id - (periode-1) and hist_ask.id
group by hist_ask.id, hist_ask.close
ORDER BY hist_ask.id DESC
LIMIT 10;
END
我使用的表是这样的
id , date , close
20 , 2018-10-13 , 12086.5
19 , 2018-10-12 , 12002.2
17 , 2018-10-11 , 12007.0
and so on
输出如下所示:
提前致谢!
解决方案
我最终使用临时表使其工作。我现在可以为该过程提供两个参数:
- periode:计算移动平均线的周期
- _limit:限制结果集
对性能重要的是
ALTER TABLE temp
ENGINE=MyISAM;
语句,因为它显着减少了执行时间。例如,在处理 2000 行时大约需要 0.5 秒,在添加之前大约需要 6 秒
那是代码:
CREATE DEFINER=`root`@`localhost` PROCEDURE `moving_avg`(IN periode INT, IN _limit INT)
NO SQL
BEGIN
DECLARE a FLOAT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE count_limit INT DEFAULT 0;
SET @rn=0;
CREATE TEMPORARY TABLE IF NOT EXISTS temp (
SELECT
@rn:=@rn+1 AS pri_id,
date,
close , a AS
mavg
FROM hist_ask);
ALTER TABLE temp
ENGINE=MyISAM;
SET i=(SELECT pri_id FROM temp ORDER by pri_id DESC LIMIT 1);
SET count_limit= (i-_limit)-periode;
WHILE i>count_limit DO
SET a= (SELECT avg(close) FROM temp WHERE pri_id BETWEEN i-(periode-1) AND i);
UPDATE temp SET mavg=a WHERE pri_id=i;
SET i=i-1;
END WHILE;
SELECT pri_id,date,close,round(mavg,2) AS mavg FROM temp ORDER BY pri_id DESC LIMIT _limit;
DROP TABLE temp;
END
结果如下所示:
CALL `moving_avg`(3,5)
- pri_id,日期,关闭,mavg
- 1999 2018-09-13 12086.6 12032.03
- 1998 2018-09-11 12002.2 11983.47
- 1997 2018-09-10 12007.3 11976.53
- 1996 2018-09-07 11940.9 11993.80
- 1995 2018-09-06 11981.4 12089.23
5 行返回 0.047 秒 / 0.000 秒
推荐阅读
- java - StringTemplates,如何在代码中创建一组模板
- python - 如何在 Tkinter GUI 中附加删除功能
- c# - 在单独的线程工作时暂停应用程序的正确方法
- java - akka 中的 AbstractActor、UntypedActor 和 AbstractBehavior 有什么区别?
- c++ - 使用具有多态行为的重载函数
- c++ - 在 OpenGL 中使用 SOIL 向三角形添加纹理的问题
- javascript - 从 JavaScript 类发出 VueJS 事件
- javascript - fullpage.js 位置滑动导航位置到底部中心
- angular - 如何在启用 AOT 的角度项目中正确使用 HttpInterceptors
- gradle - 在 IntelliJ 中打开项目时如何自动导入 Gradle 构建脚本?