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

poctopus 2021-12-24 AM 1979℃ 0条

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

数据库：MySQL

思路

按照时间维度进行统计的前提是需要数据库中有保留时间信息，建议是使用MySQL自带的datetime类型来记录时间。
```csharp
    `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)|
|%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()来获取。

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

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

结论
需统计的表结构如下：

```csharp
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的数量
```csharp
# 当日
 
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的时间维度数量
```csharp

# 按日
 
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和时间维度统计
```csharp

# 按日
 
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')
```

以上就是比较常用的时间统计了，更多的时间维度，可以参考上面的参数表类似处理即可。

```csharp
select DATE_FORMAT(acctstarttime,'%Y-%m-%d') days,username,sum(acctinputoctets+acctoutputoctets)/1048576 count from radacct
where username='username'
 group by username,days;

```

标签: MYSQL, TIMESTAMP, DATE

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://aladown.com/archives/MySQL%E6%8C%89%E6%97%B6%E9%97%B4%E7%BB%9F%E8%AE%A1%E6%95%B0%E6%8D%AE%E7%9A%84%E6%96%B9%E6%B3%95%E6%80%BB%E7%BB%93.html

上一篇宝塔真正开心教程

下一篇 PowerShell从数据库导出CSV并转换为Excel文件后邮件发送

评论已关闭

一	二	三	四	五	六	日
◄ ►
				1 1	2	3
4	5	6 1	7	8 1	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

HEIC 皮革奶 inpanel 淘宝 acme.sh TECHWEB AUTO 酸酸乳文本文件 Thunder 编码 4500 ROBCOPY 紧急启动鼠标贝康安 scan nginx C++Second SubFolder liba.com RECORDER PowerShell bzip2 轻量应用服务器 error 字魂字体 hostname STUDIO V2FLY CSV KDS Subfolder SSAE17 DEV_2019 win2008 二级目录质量腾讯云 Send-MailMessage Life's GNC SOC3 PR tbz 启动盘下载 MIPS 一键网络包测试显卡加速 0DAY WINDOWS ohook cloudflare SCHEDULER MP4 Imageglass SEARCH SSR 登录美国邮政网站 SMTP 紫草膏另存为 CDPUserSvc name.com 奶粉 MAC OS USPS WINDOWS STORE WINDOWS 11 update docker-compose 谷歌浏览器 TIMESTAMPS Ubuntu onedrive SENDMAIL RLOGIN 国家电网新网内容加速安装失败 SQLSERVER TO CSV VIDEO SMARTPROXY TCP SQL SERVER 优惠链接 TYPE 2 IOS 14.2 电信资费婴儿奶粉屏幕共享 IT168 HWID 美国百度地图

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

评论已关闭

日历

栏目分类

标签云

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

评论已关闭

日历

 栏目分类

标签云

日历

栏目分类

标签云