Consider Volumes during Miovision Alerts

[gabrielwol]

There are volumes during many of these alerts! We may need to use these similarly to anomalous_ranges.

SELECT a.intersection_uid, alert, start_time, end_time, SUM(volume)
FROM miovision_api.alerts AS a
LEFT JOIN miovision_api.volumes_2024 AS v
    ON a.intersection_uid = v.intersection_uid
    AND v.datetime_bin >= a.start_time
    AND v.datetime_bin < a.end_time
WHERE a.intersection_uid IS NOT NULL
GROUP BY a.intersection_uid, alert, start_time, end_time

"intersection_uid"	"alert"	"start_time"	"end_time"	"sum"
7	"PERIPHERAL_UNAVAILABLE"	"2024-02-14 01:05:00"	"2024-02-14 06:00:00"	2247
24	"PERIPHERAL_UNAVAILABLE"	"2024-02-12 16:55:00"	"2024-02-12 17:20:00"	1370
67	"TELEMETRY_UNAVAILABLE"	"2024-01-01 00:00:00"	"2024-04-18 00:00:00"	5936558
24	"PERIPHERAL_UNAVAILABLE"	"2024-02-11 02:00:00"	"2024-02-11 02:55:00"	725
18	"POWER_OFF"	"2024-04-10 02:10:00"	"2024-04-10 02:10:00"
59	"PERIPHERAL_UNAVAILABLE"	"2024-03-24 01:45:00"	"2024-03-24 03:25:00"	341
55	"POWER_OFF"	"2024-03-19 14:45:00"	"2024-03-19 14:50:00"
24	"PERIPHERAL_UNAVAILABLE"	"2024-02-10 01:40:00"	"2024-02-10 02:35:00"	823
28	"POWER_OFF"	"2024-03-13 10:00:00"	"2024-03-13 10:50:00"
7	"IP_CONFLICT"	"2024-02-06 13:30:00"	"2024-02-06 13:40:00"	497

Originally posted by @gabrielwol in https://github.com/CityofToronto/bdit_data-sources/pull/722#discussion_r1571177097