One would think this is simple, but has been kicking by butt all night. I have a log of sensor alerts and want to generate a table with simple counts of events that fall within 24hrs, 168hrs (1 wk), and 336hrs (2 wks) for each distinct sensor. I'm relatively new and self-taught at MySQL (running on GoDaddy) and have tried every variation of Count, Unique, Case, etc. that I found on the web. Help me Obi Wan...
Sample Data:
| Alert_timestamp | SensorID |
|---|---|
| 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: counts below don't correspond to table above). Even if a sensor has zero alerts (e.g. sensor exists in the table, but no events inside the date rage), i want to see those too.
count of events that fall within these ranges
| Sensor | <24hrs | 24 to 168hrs | 168 to 336hrs |
|---|---|---|---|
| Sensor 1 | 1 | 1 | 0 |
| Sensor 2 | 0 | 2 | 5 |
| Sensor 3 | 0 | 0 | 0 |
| Sensor 4 | 6 | 2 | 3 |
Thanks ahead of time!
CodePudding user response：
Use conditional aggregation.
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
If the table contains the rows "in future" then add according conditions to the first aggregation and to WHERE.
The index by (SensorID, Alert_timestamp) will improve.