- 很多时候想获取数据库中某个时间段得数据数量,这时候就需要对数据进行按时间分组,通常有按分钟分组,按小时分组,按天分组,按月分组
- 按分钟分组
select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-%d %H:%i') time, COUNT(1) count
from table
GROUP BY time
ORDER BY time desc
2.按小时分组
select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-%d %H:00:00') time, COUNT(1) count
from table
GROUP BY time
ORDER BY time desc
3.按天分组
select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-%d 00:00:00') time, COUNT(1) count
from table
GROUP BY time
ORDER BY time desc
4.按月分组
select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-01 00:00:00') time, COUNT(1) count
from table
GROUP BY time
ORDER BY time desc
这些都有一个规律,只要在DATE_FORMAT函数中将相应的日期格式设置一下,比如按年分组可以这样%Y-01-01 00:00:00,
当然有的时候需要任意时间段的分组,还有需要在没有数据的时间段设置0,mysql也有对应的方法,我是用后端语言php处理的,这里举个按五分钟分组数据的方法
先是按1分钟获取数据
public static function datasDivideByFiveMinute($count = 12)
{
$datas = DB::select("select DATE_FORMAT(FLOOR(rds.updated_at),'%Y-%m-%d %H:%i') time, COUNT(1) count
from registered_device_state
limit ?", [5 * $count]);
$time = strtotime(date('H:i:00',time()));
$newDatas = self::datasChange($datas, $count, 5, 'Y-m-d H:i:00',$time);
return $newDatas;
}
然后是数据处理
public static function datasChange($datas, $count, $stramp, $format,$time)
{
foreach ($datas as $data) {
$data->time = strtotime($data->time);
}
$newDatas = [];
$newDatas['type'] = $type;
$newDatas['items'] = [];
$ptr = 0;
for ($i = 0; $i < $count; $i++) {
$temp = $time - $stramp * 60;
$c = 0;
for (; $ptr < count($datas); $ptr++) {
if ($datas[$ptr]->time >= $temp && $datas[$ptr]->time < $time) {
$c += $datas[$ptr]->count;
}
if ($datas[$ptr]->time < $temp) break;
}
array_push($newDatas['items'], [
'time' => date($format, $time) . '--' . date($format, $temp),
'count' => $c
]);
$time -= $stramp * 60;
}
return $newDatas;
}
{
"time": "2018-09-14 11:44:00--2018-09-14 11:39:00",
"count": 2
},
{
"time": "2018-09-14 11:39:00--2018-09-14 11:34:00",
"count": 4
},
{
"time": "2018-09-14 11:34:00--2018-09-14 11:29:00",
"count": 9
},