SQL是可以具備一定程度的資料清洗與整理功能,例如找出特定資料、剃除重複等等,而通常進行分析統計的時候,最常使用的是Group By功能,而有種情況需要對時間欄位進行分組,就是一定時間內,例如30秒內的當作同一組,這樣的SQL要怎麼下呢?
原理很簡單,就是透過取餘數的方式,將時間轉換為數字,然後減掉與30的相除的餘數,那只要在30的範圍內,因為數字相同就會被歸為同一組,以下用MySQL示範,時間轉換為數字,透過UNIX_TIMESTAMP函式(單位秒)。
分組SQL(以下以30秒內的資料為一組)
SELECT `Datetime` ,`Column1`,`Column2`,`Column3`, `Column4` FROM `table` GROUP BY UNIX_TIMESTAMP(`Datetime`)-UNIX_TIMESTAMP(`Datetime`)%30,`Column1`,`Column1`,`Column1`, `Column1`
那看看這個Group By內的欄位資料長怎樣
SELECT `Datetime`,UNIX_TIMESTAMP(`Datetime`)-UNIX_TIMESTAMP(`Datetime`)%(0.5*60),UNIX_TIMESTAMP(`Datetime`),UNIX_TIMESTAMP(`Datetime`)%30 FROM `apr_missed` WHERE 1
Datetime | UNIX_TIMESTAMP(Datetime)-UNIX_TIMESTAMP(Datetime)%30 | UNIX_TIMESTAMP(Datetime) | UNIX_TIMESTAMP(Datetime)%30 |
2022-07-21 19:07:52 | 1658401650.0 | 1658401672 | 22 |
2022-07-21 19:07:54 | 1658401650.0 | 1658401674 | 24 |
2022-07-21 20:22:48 | 1658406150.0 | 1658406168 | 18 |
2022-07-21 20:22:49 | 1658406150.0 | 1658406169 | 19 |
2022-07-21 21:07:49 | 1658408850.0 | 1658408869 | 19 |
2022-07-21 21:07:50 | 1658408850.0 | 1658408870 | 20 |