如何使用MySQL计算移动平均线? [英] How do I calculate a moving average using MySQL?

问题描述

我需要做类似的事情:

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

除了value_column1之外，我还需要获取移动平均值 value_column1的前20个值.

Except in addition to value_column1, I also need to retrieve a moving average of the previous 20 values of value_column1.

首选标准SQL，但如有必要，我将使用MySQL扩展.

Standard SQL is preferred, but I will use MySQL extensions if necessary.

推荐答案

这只是我的头顶，而且我正要出门，所以未经测试.我也无法想象它在任何种类的大数据集上都能表现出色.我确实确认它至少可以正常运行. :)

This is just off the top of my head, and I'm on the way out the door, so it's untested. I also can't imagine that it would perform very well on any kind of large data set. I did confirm that it at least runs without an error though. :)

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

这篇关于如何使用MySQL计算移动平均线?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！