MySQL 8.0

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

select version();


version()


8.0.36


CREATE TABLE `Measurement` (
`Timestamp` timestamp NULL DEFAULT current_timestamp(),
`IDB` tinyint(8) unsigned DEFAULT NULL,
`IDC` tinyint(8) unsigned DEFAULT NULL,
`MoistMin` tinyint(8) unsigned DEFAULT NULL,
`Moist` tinyint(8) unsigned DEFAULT NULL,
`MoistMax` tinyint(8) unsigned DEFAULT NULL,
`HumMin` tinyint(8) unsigned DEFAULT NULL,
`Humidity` tinyint(8) unsigned DEFAULT NULL,
`HumMax` tinyint(8) unsigned DEFAULT NULL,
`Airpressure` int(11) unsigned DEFAULT NULL,
`Temperature` float DEFAULT NULL,
`Light` tinyint(8) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
CREATE TABLE `ChannelState` (
`Timestamp` timestamp NULL DEFAULT current_timestamp(),
`IDB` tinyint(8) unsigned DEFAULT NULL,
`IDC` tinyint(8) unsigned DEFAULT NULL,
`MoistMin` tinyint(8) unsigned DEFAULT NULL,
`Moist` tinyint(8) unsigned DEFAULT NULL,
`MoistMax` tinyint(8) unsigned DEFAULT NULL,
`HumMin` tinyint(8) unsigned DEFAULT NULL,
`Humidity` tinyint(8) unsigned DEFAULT NULL,
`HumMax` tinyint(8) unsigned DEFAULT NULL,
`Airpressure` int(11) unsigned DEFAULT NULL,
`Temperature` float DEFAULT NULL,
`Light` tinyint(8) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
-- Die Inserts in Channelstate berücksichtigen noch keine Aggregation, 
-- erstmal nur, dass überhaupt eine Differenz zustande kommt
INSERT INTO ChannelState (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,0,50,55,95,40,46,50,99,21.11,62);
INSERT INTO ChannelState (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,1,60,55,95,40,39,50,99,21.11,62);
INSERT INTO ChannelState (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,2,30,34,95,40,39,50,99,21.11,62);
INSERT INTO ChannelState (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,2,30,36,95,40,45,50,99,21.11,62);


INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (now(), 0,0,50,55,95,40,46,50,99,21.11,62);
INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (now(), 0,1,60,55,95,40,39,50,99,21.11,62);

INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (now(), 0,2,30,34,95,40,39,50,99,21.11,62);
INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (now(), 0,2,30,36,95,40,45,50,99,21.11,62);

INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (ADDTIME(now(), '0:0:3'), 0,0,50,55,95,40,46,50,99,21.11,62);
INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (ADDTIME(now(), '0:0:3'), 0,1,60,55,95,40,39,50,99,21.11,62);
INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (ADDTIME(now(), '0:0:3'), 0,2,30,34,95,40,39,50,99,21.11,62);
INSERT INTO Measurement (Timestamp, IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (ADDTIME(now(), '0:0:3'), 0,2,30,36,95,40,45,50,99,21.11,62);
select now();

-- Rohausgabe der Differenz
SELECT * FROM Measurement WHERE Timestamp > (SELECT MAX(timestamp) FROM ChannelState);

-- erstes Aggregat über block und channel
SELECT max(Timestamp), IDB, IDC, avg(MoistMin), avg(Moist), avg(MoistMax), avg(HumMin), avg(Humidity), avg(HumMax), avg(Airpressure), avg(Temperature), avg(Light)
 FROM Measurement WHERE Timestamp > (SELECT MAX(timestamp) FROM ChannelState)
group by IDB, IDC;



now()


2024-11-13 11:08:47




Timestamp
IDB
IDC
MoistMin
Moist
MoistMax
HumMin
Humidity
HumMax
Airpressure
Temperature
Light


2024-11-13 11:08:50
0
0
50
55
95
40
46
50
99
21.11
62

2024-11-13 11:08:50
0
1
60
55
95
40
39
50
99
21.11
62

2024-11-13 11:08:50
0
2
30
34
95
40
39
50
99
21.11
62

2024-11-13 11:08:50
0
2
30
36
95
40
45
50
99
21.11
62




max(Timestamp)
IDB
IDC
avg(MoistMin)
avg(Moist)
avg(MoistMax)
avg(HumMin)
avg(Humidity)
avg(HumMax)
avg(Airpressure)
avg(Temperature)
avg(Light)


2024-11-13 11:08:50
0
0
50.0000
55.0000
95.0000
40.0000
46.0000
50.0000
99.0000
21.110000610351562
62.0000

2024-11-13 11:08:50
0
1
60.0000
55.0000
95.0000
40.0000
39.0000
50.0000
99.0000
21.110000610351562
62.0000

2024-11-13 11:08:50
0
2
30.0000
35.0000
95.0000
40.0000
42.0000
50.0000
99.0000
21.110000610351562
62.0000

Timestamp	IDC	MoistMin	Moist	MoistMax	HumMin	Humidity	HumMax	Airpressure	Temperature	Light
2024-11-13 11:08:50	0	50	55	95	40	46	50	99	21.11	62
2024-11-13 11:08:50	1	60	55	95	40	39	50	99	21.11	62
2024-11-13 11:08:50	2	30	34	95	40	39	50	99	21.11	62
2024-11-13 11:08:50	2	30	36	95	40	45	50	99	21.11	62

max(Timestamp)	IDC	avg(MoistMin)	avg(Moist)	avg(MoistMax)	avg(HumMin)	avg(Humidity)	avg(HumMax)	avg(Airpressure)	avg(Temperature)	avg(Light)
2024-11-13 11:08:50	0	50.0000	55.0000	95.0000	40.0000	46.0000	50.0000	99.0000	21.110000610351562	62.0000
2024-11-13 11:08:50	1	60.0000	55.0000	95.0000	40.0000	39.0000	50.0000	99.0000	21.110000610351562	62.0000
2024-11-13 11:08:50	2	30.0000	35.0000	95.0000	40.0000	42.0000	50.0000	99.0000	21.110000610351562	62.0000