MySQL 8.0

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

CREATE TABLE data (
  name VARCHAR(10),
  speed TINYINT UNSIGNED,
  `timestamp` INT
);
INSERT INTO data VALUES
  ('yellow', 100, 1707532681),
  ('yellow', 110, 1707532661),
  ('yellow', 120, 1707532621),
  ('yellow', 110, 1707532631),
  ('yellow', 140, 1707532681),
  ('red', 100, 1707432681),
  ('red', 120, 1707332681),
  ('red', 150, 1707232681),
  ('red', 170, 1707532681);

Records: 9  Duplicates: 0  Warnings: 0


SELECT
  name,
  (SELECT AVG(speed) FROM data WHERE name = d.name AND timestamp >= (MAX(d.timestamp) - 86400)) AS avg_speed_24h
FROM data d
GROUP BY name;


name
avg_speed_24h


yellow
116.0000

red
170.0000


WITH processed AS (
  SELECT name, speed, FROM_UNIXTIME(`timestamp`) AS report_timestamp
  FROM data
)
SELECT 
 *,
 AVG(speed) OVER(
    PARTITION BY name
    ORDER BY report_timestamp ASC
    RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW
   ) AS avg_speed_24h
FROM processed;


name
speed
report_timestamp
avg_speed_24h


red
150
2024-02-06 15:18:01
150.0000

red
120
2024-02-07 19:04:41
120.0000

red
100
2024-02-08 22:51:21
100.0000

red
170
2024-02-10 02:38:01
170.0000

yellow
120
2024-02-10 02:37:01
120.0000

yellow
110
2024-02-10 02:37:11
115.0000

yellow
110
2024-02-10 02:37:41
113.3333

yellow
100
2024-02-10 02:38:01
116.0000

yellow
140
2024-02-10 02:38:01
116.0000


-- no need to convert the timestamps to DATETIME
SELECT
  name,
  AVG(speed) OVER (
    PARTITION BY name
    ORDER BY `timestamp`
    RANGE BETWEEN 86400 PRECEDING AND CURRENT ROW
  ) AS avg_speed_24h
FROM data;


name
avg_speed_24h


red
150.0000

red
120.0000

red
100.0000

red
170.0000

yellow
120.0000

yellow
115.0000

yellow
113.3333

yellow
116.0000

yellow
116.0000

name	speed	report_timestamp	avg_speed_24h
red	150	2024-02-06 15:18:01	150.0000
red	120	2024-02-07 19:04:41	120.0000
red	100	2024-02-08 22:51:21	100.0000
red	170	2024-02-10 02:38:01	170.0000
yellow	120	2024-02-10 02:37:01	120.0000
yellow	110	2024-02-10 02:37:11	115.0000
yellow	110	2024-02-10 02:37:41	113.3333
yellow	100	2024-02-10 02:38:01	116.0000
yellow	140	2024-02-10 02:38:01	116.0000