MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `nodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB /* AUTO_INCREMENT=6 */ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `nodes` (id) VALUES (1), (2)
CREATE TABLE `monitors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB /* AUTO_INCREMENT=1315 */ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `monitors` (id) VALUES (1), (2)
CREATE TABLE `monitor_node` (
  `monitor_id` int unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `average_response_time` double(8,2) DEFAULT NULL,
  UNIQUE KEY `monitor_node_monitor_id_node_id_unique` (`monitor_id`,`node_id`),
  KEY `monitor_node_node_id_foreign` (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `monitor_node` (monitor_id, node_id, average_response_time) 
VALUES
(1,1,0),
(1,2,0),
(2,1,0),
(2,2,0)
CREATE TABLE `monitor_checks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `monitor_id` int unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `response_time` double(8,6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `monitor_checks_monitor_id_node_id_response_time_index` (`monitor_id`,`node_id`,`response_time`),
  KEY `monitor_checks_node_id_foreign` (`node_id`)
) ENGINE=InnoDB /* AUTO_INCREMENT=3964257 */ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `monitor_checks` (monitor_id, node_id, response_time)
VALUES
(1,1,0.5),
(1,2,0.3),
(1,1,0.1),
(1,2,0.2),
(1,1,0.4),
(1,2,0.1),
(1,1,0.3),
(1,2,0.2),
(2,1,0.8),
(2,2,0.9),
(2,1,0.3),
(2,2,0.4),
(2,1,0.3),
(2,2,0.7),
(2,1,0.5),
(2,2,0.8);
ALTER TABLE `monitor_checks`
ADD CONSTRAINT `monitor_checks_monitor_id_foreign` FOREIGN KEY (`monitor_id`) REFERENCES `monitors` (`id`),
ADD CONSTRAINT `monitor_checks_node_id_foreign` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`);
ALTER TABLE `monitor_node`
ADD CONSTRAINT `monitor_node_monitor_id_foreign` FOREIGN KEY (`monitor_id`) REFERENCES `monitors` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `monitor_node_node_id_foreign` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`) ON DELETE CASCADE;
select * from nodes;
select * from monitors;
select * from monitor_checks;
select * from monitor_node;


id


1

2




id


1

2




id
monitor_id
node_id
response_time


3
1
1
0.100000

7
1
1
0.300000

5
1
1
0.400000

1
1
1
0.500000

6
1
2
0.100000

4
1
2
0.200000

8
1
2
0.200000

2
1
2
0.300000

11
2
1
0.300000

13
2
1
0.300000

15
2
1
0.500000

9
2
1
0.800000

12
2
2
0.400000

14
2
2
0.700000

16
2
2
0.800000

10
2
2
0.900000




monitor_id
node_id
average_response_time


1
1
0.00

1
2
0.00

2
1
0.00

2
2
0.00


-- referenced calculation
WITH cte AS ( SELECT *, 
                     ROW_NUMBER() OVER (PARTITION BY monitor_id, node_id ORDER BY id DESC) rn
              FROM monitor_checks )
SELECT monitor_id, node_id, AVG(response_time) average_response_time
FROM cte
WHERE rn <= 2
GROUP BY monitor_id, node_id;


monitor_id
node_id
average_response_time


1
1
0.3500000000

1
2
0.1500000000

2
1
0.4000000000

2
2
0.7500000000


WITH cte AS ( SELECT DISTINCT monitor_id, node_id
              FROM monitor_checks )
SELECT DISTINCT
       cte.monitor_id, 
       cte.node_id, 
       AVG(average.response_time) OVER (PARTITION BY cte.monitor_id, cte.node_id) average_response_time
FROM cte,
LATERAL ( SELECT mc.response_time 
          FROM monitor_checks mc
          WHERE (cte.monitor_id, cte.node_id) = (mc.monitor_id, mc.node_id)
          ORDER BY id DESC LIMIT 2 ) AS average


monitor_id
node_id
average_response_time


1
1
0.3500000000

1
2
0.1500000000

2
1
0.4000000000

2
2
0.7500000000


/*
UPDATE monitor_node
NATURAL JOIN ( SELECT monitor_id, node_id, AVG(response_time) avg_response_time
               FROM monitor_checks
               GROUP BY monitor_id, node_id ) check_result
SET average_response_time = avg_response_time;
SELECT * FROM monitor_node;
*/
id	monitor_id	node_id	response_time
3	1	1	0.100000
7	1	1	0.300000
5	1	1	0.400000
1	1	1	0.500000
6	1	2	0.100000
4	1	2	0.200000
8	1	2	0.200000
2	1	2	0.300000
11	2	1	0.300000
13	2	1	0.300000
15	2	1	0.500000
9	2	1	0.800000
12	2	2	0.400000
14	2	2	0.700000
16	2	2	0.800000
10	2	2	0.900000