尋求 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) 的索引將會改善。