Some people would think this is easy, but it was a struggle all night long. I have a sensor alert log and would like to generate a table with a simple count of events over 24 hours, 168 hours (1 week) and 336 hours (2 weeks) for each different sensor. I'm relatively new to MySQL (running on GoDaddy) and self-taught, and have tried every variation of Count, Unique, Case, etc. I can find on the web. Help me Obi-Wan...
Sample data:
| Alert_timestamp | Sensor ID |
|---|---|
| 2022-05-18 21:41:59 | Sensor 1 |
| 2022-05-21 21:52:09 | Sensor 1 |
| 2022-05-24 05:00:39 | Sensor 2 |
| 2022-05-24 05:02:26 | Sensor 1 |
| 2022-05-24 18:37:34 | Sensor 4 |
| 2022-05-24 20:48:40 | Sensor 1 |
| 2022-05-26 21:20:54 | Sensor 2 |
| 2022-05-27 14:53:02 | Sensor 1 |
| 2022-06-01 19:06:14 | Sensor 4 |
| 2022-06-02 19:22:27 | Sensor 1 |
| ... | ... |
Desired output (note: the counts below do not correspond to the table above). I want to see these even if the sensor has zero alerts (e.g. the sensor is present in the table but there are no events in the date range).
count of events that fall within these ranges
| sensor | <24hrs | 24 to 168 hours | 168 to 336 hours |
|---|---|---|---|
| Sensor 1 | 1 | 1 | 0 |
| Sensor 2 | 0 | 2 | 5 |
| Sensor 3 | 0 | 0 | 0 |
| Sensor 4 | 6 | 2 | 3 |
Thank you in advance!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Use conditional aggregation.
SELECT SensorID, SUM( Alert_timestamp > CURRENT_TIMESTAMP - INTERVAL 24 HOUR) ` CURRENT_TIMESTAMP - INTERVAL 168 HOUR) `24 to 168hrs`, SUM( Alert_timestamp CURRENT_TIMESTAMP - INTERVAL 336 HOUR) `168 to 336hrs` FROM table WHERE Alert_timestamp > CURRENT_TIMESTAMP - INTERVAL 336 HOUR GROUP BY SensorIDIf the table contains "future" rows, add the corresponding conditions to the first aggregate and WHERE.
The indexing of(SensorID, Alert_timestamp)will be improved.