Vous cherchez de l'aide avec une requête MySQL - Calculer le nombre d'horodatages dans une plage groupée

Question

Certaines personnes penseraient que c'est facile, mais cela a pris une nuit entière de travail acharné. J'ai un journal d'alerte de capteur et j'aimerais générer un tableau avec un simple décompte des événements sur 24 heures, 168 heures (1 semaine) et 336 heures (2 semaines) pour chaque capteur différent. Je suis relativement nouveau sur MySQL (fonctionnant sur GoDaddy) et autodidacte, et j'ai essayé toutes les variantes de Count, Unique, Case, etc. que je peux trouver sur le Web. Aide-moi Obi-Wan...

Exemples de données :

Alert_timestamp	ID du capteur
2022-05-18 21:41:59	Capteur 1
2022-05-21 21:52:09	Capteur 1
2022-05-24 05:00:39	Capteur 2
2022-05-24 05:02:26	Capteur 1
2022-05-24 18:37:34	Capteur 4
2022-05-24 20:48:40	Capteur 1
2022-05-26 21:20:54	Capteur 2
2022-05-27 14:53:02	Capteur 1
2022-06-01 19:06:14	Capteur 4
2022-06-02 19:22:27	Capteur 1
...	...

Sortie souhaitée (remarque : les décomptes ci-dessous ne correspondent pas au tableau ci-dessus). Je veux les voir même si le capteur n'a aucune alerte (par exemple, le capteur est présent dans le tableau mais il n'y a aucun événement dans la plage de dates).

count of events that fall within these ranges

Capteur	<24h	24 à 168 heures	168 à 336 heures
Capteur 1	1	1	0
Capteur 2	0	2	5
Capteur 3	0	0	0
Capteur 4	6	2	3

Merci d'avance !

Answer 1

Utilisez l'agrégation conditionnelle.

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

Si le tableau contient des lignes pour "futur", ajoutez la condition correspondante au premier agrégat et OÙ.

L’indexation de

(SensorID, Alert_timestamp) sera améliorée.