计算移动平均 MySQL?

2021-11-20 00:00:00 sql database mysql

美好的一天，

我正在使用以下代码来计算 9 天移动平均线.

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

但它不起作用，因为它在调用限制之前首先计算所有返回的字段.换句话说，它将计算该日期之前或等于该日期的所有收盘价，而不仅仅是最后 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.

所以我需要根据返回的选择计算 SUM，而不是直接计算.

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

浏览器.从 SELECT... 中选择 SUM...

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?

推荐答案

使用类似的东西

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

内部查询以desc的顺序返回所有过滤的行，然后你avg，sum把返回的那些行加起来.

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

你给出的query不工作的原因是先计算sum，LIMIT在 sum 已经计算之后应用子句，为您提供所有存在行的 sum

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

相关文章