`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

--

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,

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