Ich suche Hilfe bei der MySQL-Abfrage – Berechnen Sie die Zeitstempelanzahl innerhalb des gruppierten Bereichs

Question

Manche Leute würden denken, dass das einfach ist, aber es hat eine ganze Nacht harter Arbeit gekostet. Ich habe ein Sensoralarmprotokoll und möchte eine Tabelle mit einer einfachen Zählung der Ereignisse über 24 Stunden, 168 Stunden (1 Woche) und 336 Stunden (2 Wochen) für jeden einzelnen Sensor erstellen. Ich bin relativ neu in MySQL (läuft auf GoDaddy) und Autodidakt. Ich habe alle Variationen von Count, Unique, Case usw. ausprobiert, die ich im Internet finden kann. Hilf mir, Obi-Wan...

Beispieldaten:

Alert_timestamp	Sensor-ID
2022-05-18 21:41:59	Sensor 1
21.05.2022 21:52:09	Sensor 1
24.05.2022 05:00:39	Sensor 2
24.05.2022 05:02:26	Sensor 1
24.05.2022 18:37:34	Sensor 4
24.05.2022 20:48:40	Sensor 1
26.05.2022 21:20:54	Sensor 2
27.05.2022 14:53:02	Sensor 1
2022-06-01 19:06:14	Sensor 4
2022-06-02 19:22:27	Sensor 1
...	...

Gewünschte Ausgabe (Hinweis: Die unten aufgeführten Zahlen entsprechen nicht der Tabelle oben). Ich möchte diese auch dann sehen, wenn der Sensor keine Alarme hat (z. B. der Sensor ist in der Tabelle vorhanden, aber es gibt keine Ereignisse im Datumsbereich).

count of events that fall within these ranges

Sensor	<24 Stunden	24 bis 168 Stunden	168 bis 336 Stunden
Sensor 1	1	1	0
Sensor 2	0	2	5
Sensor 3	0	0	0
Sensor 4	6	2	3

Vielen Dank im Voraus!

Answer 1

使用条件聚合。

SELECT SensorID, 
       SUM(    Alert_timestamp >  CURRENT_TIMESTAMP - INTERVAL  24 HOUR)  `<24hrs`,
       SUM(    Alert_timestamp <= CURRENT_TIMESTAMP - INTERVAL  24 HOUR
           AND Alert_timestamp >  CURRENT_TIMESTAMP - INTERVAL 168 HOUR)  `24 to 168hrs`,
       SUM(    Alert_timestamp <= CURRENT_TIMESTAMP - INTERVAL 168 HOUR
           AND Alert_timestamp >  CURRENT_TIMESTAMP - INTERVAL 336 HOUR)  `168 to 336hrs`       
FROM table 
WHERE Alert_timestamp > CURRENT_TIMESTAMP - INTERVAL 336 HOUR
GROUP BY SensorID

如果表包含“将来”的行，则将相应的条件添加到第一个聚合和 WHERE 中。

(SensorID, Alert_timestamp) 的索引将会改善。