Geek asks and answers

MySQL: two moving averages in one query?

Is it possible to get two different moving averages from the same MySQL dataset at the same time?

I am trying to retrieve data from a MySQL database that gives me raw data, plus two different moving averages of the same dataset. My best attempt below is the problem is that two moving averages seem to produce the same results?

Also, is there a more efficient way to query data? Is the data set large enough and this query takes too long to run?

SELECT
  t1.`DateTime`,

  t1.`Positive` AS `RawData`,

(SELECT AVG(t2.`Positive`)
 FROM `tbl_DATA_KeywordResults` as t2
WHERE t2.`DateTime` <= t1.`DateTime`
ORDER BY t2.`DateTime` DESC
LIMIT 96
)  AS `DailyAverage`,

(SELECT AVG(t3.`Positive`)
FROM `tbl_DATA_KeywordResults` as t3
WHERE t3.`DateTime` <= t1.`DateTime`
ORDER BY t3.`DateTime` DESC
LIMIT 674
)  AS `WeeklyAverage`

FROM `tbl_DATA_KeywordResults` AS t1
ORDER BY t1.`DateTime`;
+3
source share
1 answer

You make a limit after you make an average. The correct subquery form will be:

(select avg(Positive)
 from (SELECT t2.`Positive`
       FROM `tbl_DATA_KeywordResults` as t2
       WHERE t2.`DateTime` <= t1.`DateTime`
       ORDER BY t2.`DateTime` DESC
       LIMIT 96
      ) t
)  AS `DailyAverage`

100%, . , MySQL ( where) .

MySQL :

select t1.DateTime, t1.RawData,
       avg(case when t2.DateTime between avg_96_dt and t1.DateTime then t2.Positive end) as avg96,
       avg(case when t2.DateTime between avg_674_dt and t1.DateTime then t2.Positive end) as avg674
from (SELECT t1.`DateTime`, t1.`Positive` AS `RawData`,
             (SELECT t2.DateTime
              FROM `tbl_DATA_KeywordResults` t2
              WHERE t2.`DateTime` <= t1.`DateTime`
              ORDER BY t2.`DateTime` DESC
              LIMIT 95, 1
             )  as avg_96_dt,
             (SELECT t2.DateTime
              FROM `tbl_DATA_KeywordResults` t2
              WHERE t2.`DateTime` <= t1.`DateTime`
              ORDER BY t2.`DateTime` DESC
              LIMIT 673, 1
             ) as avg_674_dt
      FROM `tbl_DATA_KeywordResults` t1
     ) t1 join
     tbl_DATA_KeywordResults t2
group by t1.DateTime, t1.RawData
ORDER BY t1.`DateTime`;

, .

+1

More articles:


All Articles