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),

`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 *

FROM scantech_data

ORDER BY scdate

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