Mencari bantuan dengan pertanyaan MySQL - Kira kiraan cap masa dalam julat berkumpulan

Question

Sesetengah orang akan menganggap ini mudah, tetapi ia memerlukan kerja keras sepanjang malam. Saya mempunyai log amaran penderia dan ingin menjana jadual dengan kiraan ringkas peristiwa sepanjang 24 jam, 168 jam (1 minggu) dan 336 jam (2 minggu) untuk setiap penderia yang berbeza. Saya agak baru menggunakan MySQL (berjalan di GoDaddy) dan belajar sendiri, dan telah mencuba setiap variasi Count, Unique, Case, dll. Saya boleh temui di web. Tolong saya Obi-Wan...

Sampel data:

cap_masa makluman	ID Sensor
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
...	...

Keluaran yang diingini (nota: kiraan di bawah tidak sepadan dengan jadual di atas). Saya mahu melihat ini walaupun sensor mempunyai amaran sifar (cth. sensor hadir dalam jadual tetapi tiada acara dalam julat tarikh).

count of events that fall within these ranges

Sensor	<24jam	24 hingga 168 jam	168 hingga 336 jam
Sensor 1	1	1	0
Sensor 2	0	2	5
Sensor 3	0	0	0
Sensor 4	6	2	3

Terima kasih terlebih dahulu!

Answer 1

Gunakan pengagregatan bersyarat.

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

Jika jadual mengandungi baris untuk "masa hadapan", tambahkan keadaan yang sepadan pada agregat pertama dan WHERE.

Pengindeksan

(SensorID, Alert_timestamp) akan dipertingkatkan.