logo标签列表

计算MySQL的移动平均值？

mysqlsqldatabase
18

你好，

我正在使用以下代码计算9日移动平均值。

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

但这样做不起作用，因为它在调用限制之前先计算所有返回的字段。换句话说，它将计算所有在该日期之前或等于该日期的收盘价，而不仅仅是最后9个。

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

即。选择SELECT中的SUM...

现在我该怎么做呢？这样做是否很昂贵，还是有更好的方法？

1
这个平均值是如何“移动”的？ - Strawberry
现在只是一个静态的平均计算。移动的部分是它每天都被计算。 - tread
6个回答
27
如果您想要每个日期的移动平均值，请尝试以下方法：
SELECT date, SUM(close),
       (select avg(close) from tbl t2 where t2.name_id = t.name_id and datediff(t2.date, t.date) <= 9
       ) as mvgAvg
FROM tbl t
WHERE date <= '2002-07-05' and
      name_id = 2
GROUP BY date
ORDER BY date DESC

它使用相关子查询来计算9个值的平均值。

更好的是，我正在每日触发器上使用它，因此我只需要某一天的移动平均线，因为我仅为了速度将9天移动平均线保存在数据库中。但是有了这个，我可以让用户潜在地设置他们自己的移动平均周期，缺点是速度会变慢。谢谢。 - tread
6
@surfer100 这很奇怪，因为这不是被接受的答案。 - Strawberry
因为我正在寻找使用纪元时间戳的相同答案，所以我要顶起这个旧帖子。问题在于这种方法没有考虑周末。 - fractal5
@fractal5 你可以将时间戳转换为日期。为了考虑缺失的日期，您需要连接到一个包含所有日期（预生成的）的表格。 - doublesharp
我认为datediff()函数必须在“0到9之间”，否则负的datediff()也会被计算。 - Tibor
12

从MySQL 8开始，您应该使用窗口函数来实现此功能。使用窗口 RANGE 子句，您可以创建一个逻辑窗口，它非常强大。以下是示例：

SELECT
  date,
  close,
  AVG (close) OVER (ORDER BY date DESC RANGE INTERVAL 9 DAY PRECEDING)
FROM tbl
WHERE date <= DATE '2002-07-05'
AND name_id = 2
ORDER BY date DESC

例如：

WITH t (date, `close`) AS (
  SELECT DATE '2020-01-01', 50 UNION ALL
  SELECT DATE '2020-01-03', 54 UNION ALL
  SELECT DATE '2020-01-05', 51 UNION ALL
  SELECT DATE '2020-01-12', 49 UNION ALL
  SELECT DATE '2020-01-13', 59 UNION ALL
  SELECT DATE '2020-01-15', 30 UNION ALL
  SELECT DATE '2020-01-17', 35 UNION ALL
  SELECT DATE '2020-01-18', 39 UNION ALL
  SELECT DATE '2020-01-19', 47 UNION ALL
  SELECT DATE '2020-01-26', 50
)
SELECT
  date,
  `close`,
  COUNT(*) OVER w AS c,
  SUM(`close`) OVER w AS s,
  AVG(`close`) OVER w AS a
FROM t
WINDOW w AS (ORDER BY date DESC RANGE INTERVAL 9 DAY PRECEDING)
ORDER BY date DESC

导致:

date      |close|c|s  |a      |
----------|-----|-|---|-------|
2020-01-26|   50|1| 50|50.0000|
2020-01-19|   47|2| 97|48.5000|
2020-01-18|   39|3|136|45.3333|
2020-01-17|   35|4|171|42.7500|
2020-01-15|   30|4|151|37.7500|
2020-01-13|   59|5|210|42.0000|
2020-01-12|   49|6|259|43.1667|
2020-01-05|   51|3|159|53.0000|
2020-01-03|   54|3|154|51.3333|
2020-01-01|   50|3|155|51.6667|
1
是的，MySQL支持窗口函数确实很好。我喜欢使用移动平均值进行窗口链接：https://www.db-fiddle.com/f/p9QAcJT7U24xHysqKCHFyQ/0 - Lukasz Szozda
5

使用类似以下的方法：

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。

query无法正常工作的原因是sum先被计算了，而在应用LIMIT限制之前sum已经被计算出来，导致你得到了所有行的sum。
11
这将返回一个值。移动平均指的是针对“n”个记录中每个日期的单独值。 - Gordon Linoff
是的，我假设 OP 想要特定日期 2002-07-05 的移动平均值。 - Akash
如果提供特定的name_id，则此解决方案有效，是否有一种方法可以获取表中每个name_id的移动平均值？因此，输出将是每个name_id的移动平均数表。 - user6890
您可以删除 name_id 的筛选条件，并将此字段添加到 group by 子句中，还需要删除 limit 子句并添加日期范围条件为 9 天，例如 date between your_start_date and your_end_date。 - Akash
1
另一种技术是制作一个表格：

CREATE TABLE `tinyint_asc` (
 `value` tinyint(3) unsigned NOT NULL default '0',
 PRIMARY KEY (value)
) ;
​
INSERT INTO `tinyint_asc` VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);

“在你使用它之后，可以像这样使用它：”
select 
    date_add(tbl.date, interval tinyint_asc.value day) as mydate, 
    count(*),
    sum(myvalue)
from tbl inner 
    join tinyint_asc.value <= 30 -- for a 30 day moving average
where date( date_add(o.created_at, interval tinyint_asc.value day ) ) between '2016-01-01' and current_date()
    group by mydate
0

这个查询很快：

select date, name_id,
case @i when name_id then @i:=name_id else (@i:=name_id)
and (@n:=0)
and (@a0:=0) and (@a1:=0) and (@a2:=0) and (@a3:=0) and (@a4:=0) and (@a5:=0) and (@a6:=0) and (@a7:=0) and (@a8:=0)
end as a,
case @n when 9 then @n:=9 else @n:=@n+1 end as n,
@a0:=@a1,@a1:=@a2,@a2:=@a3,@a3:=@a4,@a4:=@a5,@a5:=@a6,@a6:=@a7,@a7:=@a8,@a8:=close,
(@a0+@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8)/@n as av
from tbl,
(select @i:=0, @n:=0,
        @a0:=0, @a1:=0, @a2:=0, @a3:=0, @a4:=0, @a5:=0, @a6:=0, @a7:=0, @a8:=0) a
where name_id=2
order by name_id, date

如果您需要对50或100个值进行平均值计算，这可能会很繁琐，但是这样做是值得的。速度接近有序选择。
0
移动中位数（如果有人需要）
SELECT date,
   (SELECT AVG(close) AS median
    FROM
        (SELECT close, 
            ROW_NUMBER() OVER(ORDER BY close) AS r,
            COUNT(close) OVER() AS c
        FROM tbl
        WHERE datediff(t.date, date) 
                BETWEEN 1 AND 9
        ) AS t2

    WHERE t2.r BETWEEN FLOOR(t2.c/2)+MOD(t2.c,2) 
                    AND FLOOR(t2.c/2)+1
   ) AS mvgMedian
FROM tbl t
GROUP BY date
ORDER BY date
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接