By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE cpu_util_table (
hostname VARCHAR(50),
cpu_util FLOAT,
[timestamp] datetime2
);
INSERT INTO cpu_util_table (hostname, cpu_util, [timestamp])
VALUES
('host1', 70.0, '2023-05-09 10:00:00'),
('host1', 80.0, '2023-05-09 10:05:00'),
('host1', 85.0, '2023-05-09 10:10:00'),
('host1', 90.0, '2023-05-09 10:15:00'),
('host1', 95.0, '2023-05-09 10:20:00'),
('host1', 90.0, '2023-05-09 10:25:00'),
('host1', 85.0, '2023-05-09 10:30:00'),
('host1', 80.0, '2023-05-09 10:35:00'),
('host1', 75.0, '2023-05-09 10:40:00'),
('host1', 70.0, '2023-05-09 10:45:00'),
('host1', 80.0, '2023-05-09 10:50:00'),
('host1', 85.0, '2023-05-09 10:55:00'),
('host2', 70.0, '2023-05-09 10:00:00'),
('host2', 80.0, '2023-05-09 10:05:00'),
('host2', 85.0, '2023-05-09 10:10:00'),
('host2', 90.0, '2023-05-09 10:15:00'),
('host2', 95.0, '2023-05-09 10:20:00'),
('host2', 90.0, '2023-05-09 10:25:00'),
('host2', 85.0, '2023-05-09 10:30:00'),
('host2', 80.0, '2023-05-09 10:35:00'),
('host2', 75.0, '2023-05-09 10:40:00'),
('host2', 70.0, '2023-05-09 10:45:00'),
('host2', 80.0, '2023-05-09 10:50:00'),
('host2', 85.0, '2023-05-09 10:55:00');
24 rows affected
SELECT *
FROM (
SELECT
hostname
, cpu_util
, [TIMESTAMP]
, SUM(CASE WHEN cpu_util >= 80 THEN 1 ELSE 0 END) OVER (
PARTITION BY hostname ORDER BY TIMESTAMP ROWS BETWEEN 23 PRECEDING AND CURRENT ROW
) AS continuous_samples
FROM cpu_util_table
WHERE [TIMESTAMP] >= '2023-05-01' AND TIMESTAMP < '2023-05-31'
) AS subquery
WHERE continuous_samples >= 5;
| hostname | cpu_util | TIMESTAMP | continuous_samples |
|---|---|---|---|
| host1 | 90 | 2023-05-09 10:25:00.0000000 | 5 |
| host1 | 85 | 2023-05-09 10:30:00.0000000 | 6 |
| host1 | 80 | 2023-05-09 10:35:00.0000000 | 7 |
| host1 | 75 | 2023-05-09 10:40:00.0000000 | 7 |
| host1 | 70 | 2023-05-09 10:45:00.0000000 | 7 |
| host1 | 80 | 2023-05-09 10:50:00.0000000 | 8 |
| host1 | 85 | 2023-05-09 10:55:00.0000000 | 9 |
| host2 | 90 | 2023-05-09 10:25:00.0000000 | 5 |
| host2 | 85 | 2023-05-09 10:30:00.0000000 | 6 |
| host2 | 80 | 2023-05-09 10:35:00.0000000 | 7 |
| host2 | 75 | 2023-05-09 10:40:00.0000000 | 7 |
| host2 | 70 | 2023-05-09 10:45:00.0000000 | 7 |
| host2 | 80 | 2023-05-09 10:50:00.0000000 | 8 |
| host2 | 85 | 2023-05-09 10:55:00.0000000 | 9 |