MYSQL :如何计算每个数据的移动平均值？

Question

如何检查每一项的3个和(移动平均值)？

我不能使用下面的结果来计算每一项。

Original data : 
scdate              | value  |
--------------------|--------|
2021-11-10 17:00:00 | 1943.98|
2021-11-10 17:00:18 | 1957.13|
2021-11-10 17:00:36 | 1953.94|
2021-11-10 17:00:54 | 1946.28|
2021-11-10 17:01:12 | 1960.29|
2021-11-10 17:01:29 | 1950.39|
2021-11-10 17:01:47 | 1942.62|
2021-11-10 17:02:05 | 1954.64|
2021-11-10 17:02:23 | 1958.21|
2021-11-10 17:02:41 | 1950.26|
2021-11-10 17:02:58 | 1945.33|
2021-11-10 17:03:16 | 1960.35|

使用mysql

   SELECT  scdate,value1,value2,avg(tt.value1) as avg_v1,avg(tt.value2) as avg_v2 ,avg(tt.value2) as avg_v FROM
(
SELECT t.scdate,t.value1,t.value2,(@rownum := @rownum + 1) - 1 AS rownum FROM (SELECT @rownum := 0) 
r,scantech_data t 
ORDER BY  scdate asc
) tt
WHERE scdate >= '2021-11-10 17:00:00' and  scdate <='2021-11-10 17:10:00'
GROUP BY tt.rownum - tt.rownum % 3
order by scdate

***Wrong result

 scdate              |  value1 |  value2 |  avg1   |  avg2   |
 --------------------|---------|---------|---------|---------|
 2021/11/10 17:00:00 | 1943.98 | 1942.22 | 1951.68 | 1950.93 |
 2021/11/10 17:00:54 | 1946.28 | 1946.15 | 1952.32 | 1951.55 |
 2021/11/10 17:01:47 | 1942.62 | 1943.05 | 1951.82 | 1952.49 |
 2021/11/10 17:02:41 | 1950.26 | 1949.86 | 1951.98 | 1951.96 |

如何获得以下结果?？

我应该怎么修改?？

***Correct result

 scdate              | value   |  value2 |  avg_v1  | avg_v2  |
 --------------------|---------|---------|----------|---------|
 2021/11/10 17:00:00 | 1943.98 | 1942.22 | 1951.68  | 1950.93 |
 2021/11/10 17:00:18 | 1957.13 | 1957.25 | 1952.45  | 1952.24 |
 2021/11/10 17:00:36 | 1953.94 | 1953.32 | 1953.50  | 1952.78 |
 2021/11/10 17:00:54 | 1946.28 | 1946.15 | 1952.32  | 1951.56 |
 2021/11/10 17:01:12 | 1960.29 | 1958.88 | 1951.10  | 1950.52 |
 2021/11/10 17:01:29 | 1950.39 | 1949.64 | 1949.22  | 1949.50 |
 2021/11/10 17:01:47 | 1942.62 | 1943.05 | 1951.82  | 1952.49 |
 2021/11/10 17:02:05 | 1954.64 | 1955.81 | 1954.37  | 1954.76 |
 2021/11/10 17:02:23 | 1958.21 | 1958.62 | 1951.27  | 1951.24 |
 2021/11/10 17:02:41 | 1950.26 | 1949.86 | 1951.98  | 1951.97 |
 2021/11/10 17:02:58 | 1945.33 | 1945.25 | 1952.84  | 1953.02 |
 2021/11/10 17:03:16 | 1960.35 | 1960.79 | 1960.35  | 1960.79 |

ps : 1943.98 -11-10 17:00:00 value1 :1943.98 avg1 : 1951.68

(1943.98 + 1957.13 + 1953.94 )/3= 1951.68

使用版本: MySql 5.1.47

谢谢你的帮助。

Answer 1

Link to view windows features

SELECT scdate, value,
SUM(value) 
OVER 
(PARTITION BY scdate ORDER BY scdate ROWS BETWEEN CURRENT ROW  AND 3 FOLLOWING)
FROM test;

近似查询