mysql - MYSQL:如何计算每个数据的移动平均值?
问题描述
如何检查每个项目的 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:2021-11-10 17:00:00 value1:1943.98 avg1:1951.68
(1943.98 + 1957.13 + 1953.94) / 3 = 1951.68
使用版本:MySql 5.1.47
谢谢你的帮助。
解决方案
SELECT scdate, value,
SUM(value)
OVER
(PARTITION BY scdate ORDER BY scdate ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)
FROM test;
近似查询
推荐阅读
- python - RegEx 字符串在 Python 中直接分配时有效,但不是从 PostgreSQL 数据库中分配
- react-native - this.props.navigation.goBack() 在 DrawerNavigator 中不起作用
- python-3.x - SSL:CERTIFICATE_VERIFY_FAILED YouTube v3 API
- php - $this-> 在 Laravel 中渲染什么
- image - Chromeless - 从网页获取所有图像 src
- angular - Angular 2 - 向路由器添加密钥
- angular - 我应该在 Ionic 3 中使用 ChangeDetectionStrategy OnPush
- rest - 如何安排 API 请求
- c++ - 下标 (" [ ] ") 运算符给出奇怪的错误
- python - 访问嵌套外键对象的正确方法?