MySQL按时间统计数据的方法总结

在做数据库的统计时,经常会需要根据年,月,日来统计数据,然后配合echarts来制作可视化效果。

数据库:MySQL

<>强思路

按照时间维度进行统计的前提是需要数据库中有保留时间信息,建议是使用MySQL自带的datetime类型来记录时间。

　　

“时间戳”datetime默认为空,

　　 　　

在MySQL中对于时间日期的处理的函数主要是DATE_FORMAT(日期格式)。可用的参数如下

　　

　　 　　 　　 　　格式 　　描述 　　 　　 　　 　　 　　%的 　　缩写星期名 　　 　　 　　% b 　　缩写月名 　　 　　 　　% c 　　月,数值 　　 　　 　　% D 　　带有英文前缀的月中的天 　　 　　 　　% d 　　月的天,数值(00-31) 　　 　　 　　% e 　　月的天,数值(0-31) 　　 　　 　　% f 　　微秒 　　 　　 　　% H 　　小时(00-23) 　　 　　 　　% h 　　小时(01-12) 　　 　　 　　%我 　　小时(01-12) 　　 　　 　　%我 　　分钟,数值(00-59) 　　 　　 　　% j 　　年的天(001 - 366) 　　 　　 　　% k 　　小时(0-23) 　　 　　 　　% l 　　小时(1 - 12) 　　 　　 　　% M 　　月名 　　 　　 　　% m 　　月,数值(00-12) 　　 　　 　　% p 　　上午或下午 　　 　　 　　r % 　　时间,12 -小时(hh: mm: ss点或点) 　　 　　 　　% S 　　秒(00-59) 　　 　　 　　% s 　　秒(00-59) 　　 　　 　　% T 　　时间,24 -小时(hh: mm: ss) 　　 　　 　　% U 　　周(00-53)星期日是一周的第一天 　　 　　 　　% u 　　周(00-53)星期一是一周的第一天 　　 　　 　　% V 　　周(01-53)星期日是一周的第一天,与% X使用 　　 　　 　　% v 　　周(01-53)星期一是一周的第一天,与% x使用 　　 　　 　　% W 　　星期名 　　 　　 　　% w 　　周的天(0=星期日,6=星期六) 　　 　　 　　% X 　　年,其中的星期日是周的第一天,4位,与% V使用 　　 　　 　　% x 　　年,其中的星期一是周的第一天,4位,与% v使用 　　 　　 　　Y % 　　年,4位 　　 　　 　　y % 　　年,2位 　　 　　 　　 　　

　　

注:当涉及到按日统计是,需要使用% j,而如果使用% d, e %, % w的话,那么不同月份/周里的相同值会统计在一起。

　　

涉及到获取当前时间,则可以通过现在()或者sysdate()来获取。

　　 　　 　　从双重选择SYSDATE (); 　　 　　从双重选择现在(); 　　 　　

按照实际需求使用group by查询即可。

　　

<强>结论
　　需统计的表结构如下:

　　 　　 　　创建表“apilog”( 　　 　　“id”int(11)不是零AUTO_INCREMENT, 　　 　　“用户名”varchar(64)默认为空, 　　 　　“行动”varchar(64)默认为空, 　　 　　“参数”文本, 　　 　　“结果”的文本, 　　 　　“时间戳”datetime默认为空, 　　 　　主键(“id”) 　　 　　) 　　 　　

<强>统计时间范围内不同分类动作的数量

　　 　　 　　#当日 　　 　　选择行动,计数(id)计数从apilog DATE_FORMAT(“时间戳”,' % j ')=DATE_FORMAT(现在()% j)按计数desc; 　　 　　#当周 　　 　　选择行动,计数(id)计数从apilog DATE_FORMAT(“时间戳”,' % u ')=DATE_FORMAT(现在()% u)按计数desc; 　　 　　#当月 　　 　　选择行动,计数(id)计数从apilog DATE_FORMAT(“时间戳”,' % m ')=DATE_FORMAT(现在()% m)按计数desc; 　　 　　#当年 　　 　　选择行动,计数(id)计数从apilog DATE_FORMAT(“时间戳”,Y ' % ')=DATE_FORMAT(现在(),Y ' % ')按计数desc; 　　 　　

<强>统计某分类行动的时间维度数量

　　 　　 　　#按日 　　 　　选择行动,DATE_FORMAT(“时间戳”,“% j”),计数(id)计数从apilog GROUP BY DATE_FORMAT action=皒xx”(“时间戳”,“% j”) 　　 　　#按周 　　 　　选择行动,DATE_FORMAT(“时间戳”,“% u”),计数(id)计数从apilog GROUP BY DATE_FORMAT action=皒xx”(“时间戳”、“% u”) 　　 　　#按月 　　 　　选择行动,DATE_FORMAT(“时间戳”,“% m”),计数(id)计数从apilog GROUP BY DATE_FORMAT action=皒xx”(“时间戳”,“% m”) 　　 　　#按年 　　 　　选择行动,DATE_FORMAT(“时间戳”,Y ' % '),计数(id)计数从apilog GROUP BY DATE_FORMAT action=皒xx”(“时间戳”,Y ' % ') 　　 　　

MySQL按时间统计数据的方法总结