tries to build a query that will return a moving average based on the previous 48 hours
background: I have a table that saves all requests made to my API (one request = one row), this line also contains the date. I want to build a query that will return me the vaules that will be printed on the chart - average calls per hour based on previous 24H
Datasoruce
| id | state | date |
|---|---|---|
| 1 | success | 2022-09-05 06:10:00 |
| 2 | success | 2022-09-05 07:15:00 |
| 3 | success | 2022-09-05 08:40:00 |
| 4 | success | 2022-09-05 11:13:00 |
| 5 | success | 2022-09-05 14:20:00 |
| 6 | success | 2022-09-05 15:15:00 |
| 7 | success | 2022-09-05 15:17:00 |
| 8 | success | 2022-09-05 18:30:00 |
| 9 | success | 2022-09-05 20:48:00 |
| 10 | success | 2022-09-05 23:50:00 |
| 12 | success | 2022-09-06 00:15:00 |
| 13 | success | 2022-09-06 02:03:00 |
| 14 | success | 2022-09-06 03:15:00 |
| 15 | success | 2022-09-06 04:11:00 |
| 16 | success | 2022-09-06 04:21:00 |
| 17 | success | 2022-09-06 07:19:00 |
| 18 | success | 2022-09-06 11:13:00 |
| 19 | success | 2022-09-06 15:28:00 |
| 20 | success | 2022-09-06 15:51:00 |
| 21 | success | 2022-09-06 18:21:00 |
| 22 | success | 2022-09-06 20:41:00 |
| 23 | success | 2022-09-06 22:01:00 |
| 24 | success | 2022-09-06 23:14:00 |
| 25 | success | 2022-09-07 01:55:00 |
| 26 | success | 2022-09-07 03:29:00 |
| 27 | success | 2022-09-07 05:17:00 |
| 28 | success | 2022-09-07 08:03:00 |
| 29 | success | 2022-09-07 08:04:00 |
| 30 | success | 2022-09-07 11:35:00 |
| 31 | success | 2022-09-07 12:22:00 |
| 32 | success | 2022-09-07 15:11:00 |
| 32 | success | 2022-09-07 15:13:00 |
| 33 | success | 2022-09-07 15:40:00 |
| 34 | success | 2022-09-07 15:45:00 |
| 35 | success | 2022-09-07 16:28:00 |
| 36 | success | 2022-09-07 16:30:00 |
| 37 | success | 2022-09-07 16:39:00 |
| 38 | success | 2022-09-07 19:41:00 |
| 39 | success | 2022-09-07 21:27:00 |
Expected Result if i execute query at 2022-09-07 21:30:00
| x | value | (it just description how i calculate) |
|---|---|---|
| 2022-09-06 22:00:00 | 0.500 | (SUM rows between 2022-09-05 21:00:00-2022-09-06 22:00:00)/24 (12/24) |
| 2022-09-06 23:00:00 | 0.500 | (SUM rows between 2022-09-05 22:00:00-2022-09-06 23:00:00)/24 (13/24) |
| 2022-09-07 00:00:00 | 0.541 | (SUM rows between 2022-09-05 23:00:00-2022-09-07 00:00:00)/24 (14/24) |
| 2022-09-07 01:00:00 | 0.541 | (SUM rows between 2022-09-06 00:00:00-2022-09-07 01:00:00)/24 (13/24) |
| 2022-09-07 02:00:00 | 0.541 | (SUM rows between 2022-09-06 01:00:00-2022-09-07 02:00:00)/24 (13/24) |
| 2022-09-07 03:00:00 | 0.541 | (SUM rows between 2022-09-06 02:00:00-2022-09-07 03:00:00)/24 (13/24) |
| 2022-09-07 04:00:00 | 0.541 | (SUM rows between 2022-09-06 03:00:00-2022-09-07 04:00:00)/24 (13/24) |
| 2022-09-07 05:00:00 | 0.500 | (SUM rows between 2022-09-06 04:00:00-2022-09-07 05:00:00)/24 (12/24) |
| 2022-09-07 06:00:00 | 0.458 | (SUM rows between 2022-09-06 05:00:00-2022-09-07 06:00:00)/24 (11/24) |
| 2022-09-07 07:00:00 | 0.458 | (SUM rows between 2022-09-06 06:00:00-2022-09-07 07:00:00)/24 (11/24) |
| 2022-09-07 08:00:00 | 0.458 | (SUM rows between 2022-09-06 07:00:00-2022-09-07 08:00:00)/24 (11/24) |
| 2022-09-07 09:00:00 | 0.500 | (SUM rows between 2022-09-06 08:00:00-2022-09-07 09:00:00)/24 (12/24) |
| 2022-09-07 10:00:00 | 0.500 | (SUM rows between 2022-09-06 09:00:00-2022-09-07 10:00:00)/24 (12/24) |
| 2022-09-07 11:00:00 | 0.500 | (SUM rows between 2022-09-06 10:00:00-2022-09-07 11:00:00)/24 (12/24) |
| 2022-09-07 12:00:00 | 0.541 | (SUM rows between 2022-09-06 11:00:00-2022-09-07 12:00:00)/24 (13/24) |
| 2022-09-07 13:00:00 | 0.541 | (SUM rows between 2022-09-06 12:00:00-2022-09-07 13:00:00)/24 (13/24) |
| 2022-09-07 14:00:00 | 0.541 | (SUM rows between 2022-09-06 13:00:00-2022-09-07 14:00:00)/24 (13/24) |
| 2022-09-07 15:00:00 | 0.541 | (SUM rows between 2022-09-06 14:00:00-2022-09-07 15:00:00)/24 (13/24) |
| 2022-09-07 16:00:00 | 0.708 | (SUM rows between 2022-09-06 15:00:00-2022-09-07 16:00:00)/24 (17/24) |
| 2022-09-07 17:00:00 | 0.750 | (SUM rows between 2022-09-06 16:00:00-2022-09-07 17:00:00)/24 (18/24) |
| 2022-09-07 18:00:00 | 0.750 | (SUM rows between 2022-09-06 17:00:00-2022-09-07 18:00:00)/24 (18/24) |
| 2022-09-07 19:00:00 | 0.750 | (SUM rows between 2022-09-06 18:00:00-2022-09-07 19:00:00)/24 (18/24) |
| 2022-09-07 20:00:00 | 0.750 | (SUM rows between 2022-09-06 19:00:00-2022-09-07 20:00:00)/24 (18/24) |
| 2022-09-07 21:00:00 | 0.750 | (SUM rows between 2022-09-06 20:00:00-2022-09-07 21:00:00)/24 (18/24) |
until now I was able to build only a query that counts requests per hour but i dont know how i can rewrite this to calculate average from previous 24 h
SELECT aux.dh AS x, Coalesce(Count(*), 0) AS value
FROM apitracker AS ets
RIGHT JOIN(SELECT Date_format(Now(), '%Y-%m-%d %H:00:00') AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 1 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 2 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 3 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 4 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 5 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 6 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 7 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 8 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 9 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 10 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 11 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 12 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 13 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 14 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 15 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 16 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 17 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 18 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 19 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 20 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 21 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 22 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 23 hour) AS dh
UNION SELECT Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 24 hour) AS dh
) AS aux
ON Date_format(ets.date, '%Y-%m-%d %H:00:00') = aux.dh AND ets.date > Date_sub(Date_format(Now(), '%Y-%m-%d %H:00:00'), INTERVAL 24 hour)
GROUP BY aux.dh
ORDER BY aux.dh ASC;