iT邦幫忙

1

MYSQL：如何計算每筆數據的移動平均值？

mysql sql
sundayjoe 2021-11-13 15:35:37 ‧ 1982 瀏覽

  • 分享至 

  • xImage

請問一下怎麼計算出正確的 每筆的3筆平均後 的 移動平均線...
以下是我的原始資料

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 command

SELECT  scdate,avg(tt.value) as avg_v FROM
(
SELECT t.scdate,t.value,(@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

結果會跳筆數

scdate avg_v
2021-11-10 17:00:00 1951.68
2021-11-10 17:00:54 1952.32
2021-11-10 17:01:47 1951.82
2021-11-10 17:02:41 1951.98

請問各位先進怎麼樣才可以變成以下結果，每筆執行移動平均

scdate value avg_v
2021-11-10 17:00:00 1943.98 1951.68
2021-11-10 17:00:18 1957.13 1952.45
2021-11-10 17:00:36 1953.94 1953.50
2021-11-10 17:00:54 1946.28 1952.32
2021-11-10 17:01:12 1960.29 1951.1
2021-11-10 17:01:29 1950.39 1949.21
2021-11-10 17:01:47 1942.62 1951.82
2021-11-10 17:02:05 1954.64 1954.37
2021-11-10 17:02:23 1958.21 1951.26
2021-11-10 17:02:41 1950.26 1951.98
2021-11-10 17:02:58 1945.33 1952.84
2021-11-10 17:03:16 1960.35 1960.35

MySql 5.1.47 (版本)
麻煩先進幫忙...謝謝

kuosheng iT邦新手 4 級 ‧ 2021-11-15 08:12:27 檢舉
找找看windows function
sundayjoe iT邦新手 5 級 ‧ 2021-11-17 15:49:34 檢舉
好像最新版本的有提供類似fun~但礙於資料量大~可能無法去升級
感謝
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

3
rogeryao
iT邦超人 7 級 ‧ 2021-11-13 19:41:22
最佳解答 
CREATE TABLE `scantech_data` (
`scdate` varchar(20),
`value` float
);

INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:00',1943.98);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:18',1957.13);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:36',1953.94);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:54',1946.28);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:12',1960.29);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:29',1950.39);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:47',1942.62);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:05',1954.64);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:23',1958.21);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:41',1950.26);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:58',1945.33);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:03:16',1960.35);
-- 額外加入
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:04:33',NULL);

SELECT T1.scdate,T1.value,
CASE WHEN IFNULL(T1.value,0)+IFNULL(T2.value,0)+IFNULL(T3.value,0) > 0 THEN
ROUND((IFNULL(T1.value,0)+IFNULL(T2.value,0)+IFNULL(T3.value,0))/(
CASE WHEN T1.value IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v
FROM (
SELECT t.scdate,t.value,
(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate

Demo

看更多先前的回應...收起先前的回應...
sundayjoe iT邦新手 5 級 ‧ 2021-11-14 11:25:53 檢舉

to rogeryao
非常感謝指導
請問如果是多筆value1,value2,value3,value4 ~ N 欄
可以同時算出各自的avg嗎...謝謝

rogeryao iT邦超人 7 級 ‧ 2021-11-14 13:02:04 檢舉 
CREATE TABLE `scantech_data` (
`scdate` varchar(20),
`value1` float,
`value2` float
);

INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:00',1943.98,2943.98);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:18',1957.13,2957.13);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:36',1953.94,2953.94);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:54',1946.28,2946.28);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:12',1960.29,2960.29);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:29',1950.39,2950.39);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:47',1942.62,2942.62);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:05',1954.64,2954.64);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:23',1958.21,2958.21);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:41',1950.26,2950.26);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:58',1945.33,2945.33);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:03:16',1960.35,2960.35);
-- 額外加入
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:04:33',NULL,NULL);

SELECT T1.scdate,
--
T1.value1,
CASE WHEN IFNULL(T1.value1,0)+IFNULL(T2.value1,0)+IFNULL(T3.value1,0) > 0 THEN
ROUND((IFNULL(T1.value1,0)+IFNULL(T2.value1,0)+IFNULL(T3.value1,0))/(
CASE WHEN T1.value1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value1 IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v1,
-- Copy 上面的 value1
T1.value2,
CASE WHEN IFNULL(T1.value2,0)+IFNULL(T2.value2,0)+IFNULL(T3.value2,0) > 0 THEN
ROUND((IFNULL(T1.value2,0)+IFNULL(T2.value2,0)+IFNULL(T3.value2,0))/(
CASE WHEN T1.value2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value2 IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v2
--
FROM (
SELECT *,(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT *,(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT *,(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate

Demo

sundayjoe iT邦新手 5 級 ‧ 2021-11-14 16:21:10 檢舉

to rogeryao
我大概有100多個需要被算出，速度是還行的
謝謝您提供解決方式，學習了~~感恩

sundayjoe iT邦新手 5 級 ‧ 2021-11-17 15:47:05 檢舉

to rogeryao
抱歉，再請教一個問題
如果改成 移動大於數量，如value1,value2,value3,value4 > 1950 的有多少個~~是否可以用以上來修改呢
謝謝您

rogeryao iT邦超人 7 級 ‧ 2021-11-17 16:26:51 檢舉

看無啦，貼個範例吧

sundayjoe iT邦新手 5 級 ‧ 2021-11-17 17:12:53 檢舉

to rogeryao
抱歉，好的，以下是結果
一樣是3筆判斷一次
是否可以用以上來修改呢
感謝

scdate value >1945 數量
2021/11/10 17:00:00 1943.98 2
2021/11/10 17:00:18 1957.13 3
2021/11/10 17:00:36 1953.94 3
2021/11/10 17:00:54 1946.28 3
2021/11/10 17:01:12 1960.29 2
2021/11/10 17:01:29 1950.39 2
2021/11/10 17:01:47 1942.62 NULL
2021/11/10 17:02:05 1954.64 NULL
rogeryao iT邦超人 7 級 ‧ 2021-11-17 17:42:23 檢舉 
SELECT T1.scdate,T1.value,T2.value,T3.value,
CASE WHEN T1.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T2.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T3.value>1945 THEN 1 ELSE 0 END AS TempCount
FROM (
SELECT t.scdate,t.value,
(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate

Demo

sundayjoe iT邦新手 5 級 ‧ 2021-11-17 18:39:49 檢舉

to rogeryao
學習了~~
真利害，非常謝謝您的指導

登入發表回應
0
Felix
iT邦研究生 2 級 ‧ 2021-11-14 03:17:10

只有查詢的話，我會讓查詢配合資料表的結構。

如果要向下查詢 5 筆資料的平均，只要將註解處的 + 3 改為 + 5 即可。

附上示範。

SELECT `scdate`, `value`, `avg_v` FROM (
	SELECT `scdate`, `value`, IFNULL(
		(SELECT AVG(`value`) FROM (
			SELECT * FROM (
				SELECT @no := @no + 1 `no`, `scdate`, `value` FROM (
					`scantech_data`,
					(SELECT @no := 0) `var`
				)
				ORDER BY `scdate` ASC
			) `t4`
		) `t3`
		WHERE `t3`.`no` >= `t2`.`id`
        -- 向下查詢 3 筆資料
		AND `t3`.`no` < `t2`.`id` + 3),
		0
	) `avg_v`
	FROM (
		SELECT *, @id := @id + 1 `id` FROM (
			`scantech_data`,
			(SELECT @id := 0) `var`
		)
		ORDER BY `scdate` ASC
	) `t2`
) `t1`
WHERE `scdate` >= '2021-11-10 17:00:00'
AND `scdate` <='2021-11-10 17:10:00';
sundayjoe iT邦新手 5 級 ‧ 2021-11-14 11:29:44 檢舉

to Felix
非常感謝指導
請問如果是多筆value1,value2,value3,value4 ~ N 欄
可以同時算出各自的avg嗎...
是否每一個AVG都要各自下查詢....謝謝

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22178 篇
完賽人數
597 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙