在SQL中计算移动平均值

本文介绍了在SQL中计算移动平均值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想从MySQL列中计算12个月的移动平均值.数据代表时序功率测量，它是一个比较大的数据集(几年中每10分钟一次).高性能查询会很好，但是速度是我以后可以解决的问题.

I want to calculate a 12 month moving average from a MySQL column. The data represents time-series power measurements, it is a largish dataset (every 10 minutes for several years). A high performance query would be nice but speed is something I can work out later.

DTE Active 2012-1-3 00:10 500 2012-1-3 00:20 520 ... etc

以下查询为我提供了每月的总活动量:

The following query gives me the total Active for each month :

SELECT YEAR(DTE) AS year, MONTH(DTE) AS month, SUM(Active)/6 as total FROM saturne s GROUP BY YEAR(DTE), MONTH(DTE)

以下查询为我提供了给定月份和年份的移动平均有效-例如2011年10月

The following query gives me the moving average Active for a given month and year - say october 2011

SELECT SUM(Active)/6 AS average FROM saturne WHERE (YEAR(DTE) = 2011 AND MONTH(DTE) <= 10) OR (YEAR(DTE) = 2010 AND MONTH(DTE) > 10)

但是我想生成一个查询，该查询在下一列中返回月总计和12个月移动平均值.

I would however like to generate a query which returns the monthly total and the 12 month moving average in the next column.

year month total average 2012 2 701474 9258089 2012 1 877535 9386664 ... etc

(因数6是因为数据代表每10分钟记录的瞬时功率，将总功率除以6得到总能量)

(The factor of 6 is because the data represents instantaneous power recorded every 10 minutes, dividing the total by 6 gives the total energy)

推荐答案

尝试:

SELECT YEAR(GDTE) AS year, MONTH(GDTE) AS month, SUM(case when i=0 then Active end)/6 as total, SUM(Active)/(MAX(i+1)*6) as avg FROM (select s.*, DATE_ADD(DTE, INTERVAL m.i MONTH) GDTE, m.i FROM saturne s cross join (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11) m ) sq WHERE GDTE <= curdate() GROUP BY YEAR(GDTE), MONTH(GDTE)