本文摘自PHP中文网,作者不言,侵删。
本篇文章给大家带来的内容是关于MySQL按时间统计数据的方法介绍(代码示例),有一定的参考价值,有需要的朋友可以参考一下,希望对你有所帮助。在做数据库的统计时,经常会需要根据年、月、日来统计数据,然后配合echarts
来制作可视化效果。
数据库:MySQL(推荐教程:MySQL教程)
思路
按照时间维度进行统计的前提是需要数据库中有保留时间信息,建议是使用
MySQL
自带的datetime
类型来记录时间。
1 |
|
在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) |
%p | AM 或 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()
来获取。
1 2 |
|
按照实际需求使用
group by
查询即可。
结论
需统计的表结构如下:
1 2 3 4 5 6 7 8 9 |
|
统计时间范围内不同分类
action
的数量1
2
3
4
5
6
7
8
# 当日
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
的时间维度数量1
2
3
4
5
6
7
8
# 按日
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
和时间维度统计1
2
3
4
5
6
7
8
# 按日
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'
)
以上就是比较常用的时间统计了,更多的时间维度,可以参考上面的参数表类似处理即可。
以上就是MySQL按时间统计数据的方法介绍(代码示例)的详细内容,更多文章请关注木庄网络博客!
相关阅读 >>
mysql数据库备份及恢复命令 mysqldump,source的用法
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。