计算移动平均MySQL？ [英] Calculating a Moving Average MySQL?

问题描述

Good Day，

我使用以下代码计算9天移动平均线。

  SELECT SUM（close）
 FROM tbl 
 WHERE date< ='2002-07-05'
 AND name_id = 2 
日期DESC 
 LIMIT 9 
  

但它不工作，因为它首先计算所有返回字段在限制被调用之前。换句话说，它将计算所有关闭之前或之后的日期，而不只是最后的9。

所以我需要从返回的select计算SUM，而不是直接计算。

IE。从SELECT ...选择SUM ...

现在我该怎么做，这是非常昂贵的，还是有更好的方法？

解决方案

使用类似

  SELECT 
 sum（close）as sum，
 avg（close）as average 
 FROM（
 SELECT 
（close）
 FROM $ b b tbl 
 WHERE 
 date< ='2002-07-05'
 AND name_id = 2 
 ORDER BY 
 date DESC 
 LIMIT 9）temp 
  

内部查询会返回 desc order，然后你 avg ， sum 向上返回这些行。

由你提供的查询不起作用的原因是因为 sum ，然后在已经计算出 sum 之后应用 LIMIT $ c> sum 的所有行

Good Day,

I am using the following code to calculate the 9 Day Moving average.

SELECT SUM(close)
FROM tbl
WHERE date <= '2002-07-05'
AND name_id = 2
ORDER BY date DESC
LIMIT 9

But it does not work because it first calculates all of the returned fields before the limit is called. In other words it will calculate all the closes before or equal to that date, and not just the last 9.

So I need to calculate the SUM from the returned select, rather than calculate it straight.

IE. Select the SUM from the SELECT...

Now how would I go about doing this and is it very costly or is there a better way?

解决方案

Use something like

SELECT 
  sum(close) as sum,
  avg(close) as average
FROM (
    SELECT 
      (close)
    FROM 
      tbl
    WHERE 
      date <= '2002-07-05'
      AND name_id = 2
    ORDER BY 
      date DESC
    LIMIT 9 ) temp

The inner query returns all filtered rows in desc order, and then you avg, sum up those rows returned.

The reason why the query given by you doesn't work is due to the fact that the sum is calculated first and the LIMIT clause is applied after the sum has already been calculated, giving you the sum of all the rows present

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