MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 *
FROM scantech_data
ORDER BY scdate

scdate	value1	value2
2021-11-10 17:00:00	1943.98	2943.98
2021-11-10 17:00:18	1957.13	2957.13
2021-11-10 17:00:36	1953.94	2953.94
2021-11-10 17:00:54	1946.28	2946.28
2021-11-10 17:01:12	1960.29	2960.29
2021-11-10 17:01:29	1950.39	2950.39
2021-11-10 17:01:47	1942.62	2942.62
2021-11-10 17:02:05	1954.64	2954.64
2021-11-10 17:02:23	1958.21	2958.21
2021-11-10 17:02:41	1950.26	2950.26
2021-11-10 17:02:58	1945.33	2945.33
2021-11-10 17:03:16	1960.35	2960.35
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

scdate	value1	avg_v1	value2	avg_v2
2021-11-10 17:00:00	1943.98	1951.68	2943.98	2951.68
2021-11-10 17:00:18	1957.13	1952.45	2957.13	2952.45
2021-11-10 17:00:36	1953.94	1953.50	2953.94	2953.50
2021-11-10 17:00:54	1946.28	1952.32	2946.28	2952.32
2021-11-10 17:01:12	1960.29	1951.10	2960.29	2951.10
2021-11-10 17:01:29	1950.39	1949.22	2950.39	2949.22
2021-11-10 17:01:47	1942.62	1951.82	2942.62	2951.82
2021-11-10 17:02:05	1954.64	1954.37	2954.64	2954.37
2021-11-10 17:02:23	1958.21	1951.27	2958.21	2951.27
2021-11-10 17:02:41	1950.26	1951.98	2950.26	2951.98
2021-11-10 17:02:58	1945.33	1952.84	2945.33	2952.84
2021-11-10 17:03:16	1960.35	1960.35	2960.35	2960.35
2021-11-10 17:04:33	null	null	null	null