寻求 MySQL 查询帮助 - 计算分组范围内的时间戳计数

Question

有人会认为这很简单，但一整晚都在拼命努力。我有一个传感器警报日志，想要生成一个表格，其中包含每个不同传感器 24 小时、168 小时（1 周）和 336 小时（2 周）内的事件的简单计数。我对 MySQL（在 GoDaddy 上运行）相对较新并且自学成才，并且尝试了我在网络上找到的 Count、Unique、Case 等的每种变体。帮助我欧比旺...

示例数据：

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

所需输出（注意：下面的计数与上表不对应）。即使传感器的警报为零（例如，表中存在传感器，但日期范围内没有事件），我也想看到这些。

count of events that fall within these ranges

传感器	<24hrs	24 至 168 小时	168 至 336 小时
传感器 1	1	1	0
传感器 2	0	2	5
传感器 3	0	0	0
传感器 4	6	2	3

提前谢谢！

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) 的索引将会改善。