MySQL如何实现按时间统计数据

35次阅读

没有评论

共计 2574 个字符，预计需要花费 7 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍了 MySQL 如何实现按时间统计数据，具有一定借鉴价值，需要的朋友可以参考下。希望大家阅读完这篇文章后大有收获。下面让丸趣 TV 小编带着大家一起了解一下。

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

数据库：MySQL

思路

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

`timestamp` datetime DEFAULT NULL,

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

格式描述 %a 缩写星期名 %b 缩写月名 %c 月，数值 %D 带有英文前缀的月中的天 %d 月的天，数值 (00-31)%e 月的天，数值 (0-31)%f 微秒 %H 小时 (00-23)%h 小时 (01-12)%I 小时 (01-12)%i 分钟，数值 (00-59)%j 年的天 (001-366)%k 小时 (0-23)%l 小时 (1-12)%M 月名 %m 月，数值 (00-12)%pAM 或 PM%r 时间，12- 小时（hh:mm:ss AM 或 PM）%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 的话，那么不同月份 / 周里的相同值会统计在一起。

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

SELECT SYSDATE() FROM DUAL;
SELECT NOW() FROM DUAL;

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

结论

需统计的表结构如下：

CREATE TABLE `apilog` (`id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(64) DEFAULT NULL,
 `action` varchar(64) DEFAULT NULL,
 `params` text,
 `result` text,
 `timestamp` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
)

统计时间范围内不同分类 action 的数量

# 当日
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %j) = DATE_FORMAT(now(), %j ) ORDER BY count desc;
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %u) = DATE_FORMAT(now(), %u ) ORDER BY count desc;
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %m) = DATE_FORMAT(now(), %m ) ORDER BY count desc;
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %Y) = DATE_FORMAT(now(), %Y ) ORDER BY count desc;

统计某分类 action 的时间维度数量

# 按日
SELECT action, DATE_FORMAT(`timestamp`, %j), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %j)
SELECT action, DATE_FORMAT(`timestamp`, %u), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %u)
SELECT action, DATE_FORMAT(`timestamp`, %m), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %m)
SELECT action, DATE_FORMAT(`timestamp`, %Y), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %Y)

同时按 action 和时间维度统计

# 按日
SELECT action, DATE_FORMAT(`timestamp`, %j), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %j)
SELECT action, DATE_FORMAT(`timestamp`, %u), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %u)
SELECT action, DATE_FORMAT(`timestamp`, %m), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %m)
SELECT action, DATE_FORMAT(`timestamp`, %Y), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %Y)

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享 MySQL 如何实现按时间统计数据内容对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，遇到问题就找丸趣 TV，详细的解决方法等着你来学习!

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！

正文完