如何使用MySQL计算移动平均值？

Question

我需要做类似以下的事情：

SELECT value_column1 
FROM table1 
WHERE datetime_column1 >= '2009-01-01 00:00:00' 
ORDER BY datetime_column1;

除了value_column1，我还需要检索前20个值的移动平均线的value_column1。

标准SQL是首选，但如果必要，我将使用MySQL扩展。

- Travis Beale

Answer 1

这只是我脑海中的一个想法，而且我现在要出门了，所以它没有经过测试。我也无法想象它在任何大型数据集上性能会很好。不过，我确认它至少可以运行而不会出错。 :)

SELECT
     value_column1,
     (
     SELECT
          AVG(value_column1) AS moving_average
     FROM
          Table1 T2
     WHERE
          (
               SELECT
                    COUNT(*)
               FROM
                    Table1 T3
               WHERE
                    date_column1 BETWEEN T2.date_column1 AND T1.date_column1
          ) BETWEEN 1 AND 20
     )
FROM
     Table1 T1

- Tom H

Answer 2

Tom H的方法是可行的。如果有一个identity列，您可以像这样简化它：

SELECT T1.id, T1.value_column1, avg(T2.value_column1)
FROM table1 T1
INNER JOIN table1 T2 ON T2.Id BETWEEN T1.Id-19 AND T1.Id

- Andomar

---

我不了解MySQL，但在MS SQL Server中这是行不通的。IDENTITY列不能保证是顺序或连续的。 - Tom H

如果您不使用SET IDENTITY_INSERT ON或删除价格，那么它们将会是什么？在这种情况下，您可以将数据移动到一个按日期排序的带有标识列的临时表中。 - Andomar

我同意Tom的观点。IDENTITY（或在MySQL术语中，auto_increment主键）可能不是连续的。如果您从表中删除某些行，那么会出现键中的间隙。 - Travis Beale

我同意这不是理想的... 但是我会点赞，因为在某些情况下，这种方法的性能优势超过了由于缺少记录等可能出现的不准确性。 - Eric

Answer 3

我知道这个回答晚了7年。我有一个类似的需求，所以我想分享我的解决方案，以便对其他人有用。

有一些MySQL扩展可以进行技术分析，其中包括简单移动平均值。它们非常容易安装和使用：https://github.com/mysqludf/lib_mysqludf_ta#readme

一旦你按照README中的说明安装了UDF，就可以在select语句中包含一个简单的移动平均值，如下所示：

SELECT TA_SMA(value_column1, 20) AS sma_20 FROM table1 ORDER BY datetime_column1 

- Alex Woolford

Answer 4

我的解决方案在表格中添加了行号。下面的示例代码可能会有所帮助：

set @MA_period=5;
select id1,tmp1.date_time,tmp1.c,avg(tmp2.c) from 
(select @b:=@b+1 as id1,date_time,c from websource.EURUSD,(select @b:=0) bb order by date_time asc) tmp1,
(select @a:=@a+1 as id2,date_time,c from websource.EURUSD,(select @a:=0) aa order by date_time asc) tmp2
where id1>@MA_period and id1>=id2 and id2>(id1-@MA_period)
group by id1
order by id1 asc,id2 asc

- Michel Aspron

---

如果您在表（此处命名为websource.EURUSD）中使用条件选择特定记录，则必须在两个子选择（tmp1和tmp2别名）中使用完全相同的条件。 - Michel Aspron

Answer 5

当我遇到类似的问题时，最终我使用了临时表来解决各种问题，这使得问题变得更容易！我所做的看起来与你正在做的非常相似，就模式而言。

将模式设置为ID自增、开始日期、结束日期和值。在选择时，根据ID进行子选择，计算前20个的平均值。

只有在你已经因其他原因使用临时表时才这样做（我反复访问同一行以获取不同指标，所以拥有小数据集非常有帮助）。

- overslacked

---

我不确定临时表何时出现，但我可以在没有它们的情况下使用您的解决方案。尽管如此，它存在依赖于标识列连续性的问题。 - Travis Beale

身份列连续是临时表的重点......在我的情况下，我有多年的数据，但每个月的数据都是单独处理的。我将数据提取到临时表中，并对其执行许多指标。在我的情况下，使用临时表（或表值函数）使得处理的许多方面更加容易。 - overslacked

Answer 6

在 MySQL 8 中，窗口函数帧可用于获取平均值。

SELECT value_column1, AVG(value_column1) OVER (ORDER BY datetime_column1 ROWS 19 PRECEDING) as ma
FROM table1
WHERE datetime_column1 >= '2009-01-01 00:00:00' 
ORDER BY datetime_column1;

这个计算当前行和前19行的平均值。

- vahbuna

Answer 7

在我的经验中，Mysql 5.5.x 版本不倾向于在依赖子查询或连接的情况下使用索引。当依赖选择条件在每一行发生变化时，这可能对性能产生非常重要的影响。

移动平均是属于这种类别的查询示例。执行时间可能随着行数的平方而增加。为了避免这种情况，选择一个可以执行依赖选择的索引查找的数据库引擎。我发现 Postgres 在这个问题上工作得很有效。

- Nick Hill