业务场景

MySQL按小时分组统计日志记录数量。最近需要统计一些日志流水，统计出打卡的高峰期，所以需要对日志流水按小时进行分组统计，统计出每半小时或者每小时内的打卡次数

按小时统计

这里使用DATE_FORMAT函数，然后再根据createTime进行分组，就可以统计每个小时内的打卡次数

SELECTdevice_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:00:00') AS createTime,count(*) AS num
FROMt_user_atten_record
WHEREcom_id = 1111699
GROUP BYdevice_id, createTime
ORDER BYdevice_id, createTime;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

每小时分组统计结果：

MySQL按小时分组统计日志记录数量_adb

每半小时统计

要统计每半小时内的打卡次数，可以使用，MINUTE先获取对应日期的分钟，半小时就是30分钟，floor(MINUTE(create_time) / 30)是进行四舍五入计算，再乘以30，floor(MINUTE(create_time) / 30) * 30就是按30分钟归类为一组，同理再进行group by分组统计

SELECTdevice_id,DATE_FORMAT(concat(date(create_time),' ',HOUR (create_time),':',floor(MINUTE(create_time) / 30) * 30),'%Y-%m-%d %H:%i') AS createTime,count(*) AS num
FROMt_user_atten_record
WHEREcom_id = 1111699GROUP BYdevice_id, createTime 
ORDER BYdevice_id, createTime;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.

每半小时分组统计结果：

MySQL按小时分组统计日志记录数量_MySQL_02

基于此，还可以继续拓展，按每N分钟、每分钟、每天进行分组统计

每N分钟统计

前面是按照半小时(30分钟)，依此类推，可以按n分钟进行分组统计，统计n分钟内的打卡次数，比如统计每10分钟内的打卡次数

SELECTdevice_id,DATE_FORMAT(concat(date(create_time),' ',HOUR (create_time),':',floor(MINUTE(create_time) / 10) * 10),'%Y-%m-%d %H:%i') AS createTime,count(*) AS num
FROMt_user_atten_record
WHEREcom_id = 1111699GROUP BYdevice_id, createTime 
ORDER BYdevice_id, createTime;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.

统计每10分钟内的打卡次数

MySQL按小时分组统计日志记录数量_四舍五入_03

按分钟统计

如果要按分钟进行分组，统计每分钟内的打卡次数

SELECTdevice_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:00') AS createTime,count(*) AS num
FROMt_user_atten_record
WHEREcom_id = 1111699
GROUP BYdevice_id, createTime
ORDER BYdevice_id, createTime;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

按分钟进行分组统计：

MySQL按小时分组统计日志记录数量_四舍五入_04

按日期统计

按照日期进行分组，统计每天的打卡次数：

SELECTdevice_id,DATE(create_time) AS createTime,count(*) AS num
FROMt_user_atten_record
WHEREcom_id = 1111699
GROUP BYdevice_id, createTime
ORDER BYdevice_id, createTime;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.

按天分组统计

MySQL按小时分组统计日志记录数量_数据库_05