Hello! 欢迎来到小浪云!


如何用 MySQL 统计一天数据量,并将其划分为 5 分钟一个区间?


avatar
小浪云 2024-11-10 36

如何用 MySQL 统计一天数据量,并将其划分为 5 分钟一个区间?

如何高效统计一天数据量,分5分钟为一个区间

mysql中,我们经常需要按时间段统计数据量。本文将详细介绍一种高效的方法,将一天划分为5分钟一个区间,统计每个区间内的数据量。

首先,创建一张辅助表time_intervals,用于存储时间段:

create table `time_intervals` (`grouped_time` time default null)
登录后复制

接着,使用存储过程向time_intervals表中插入时间段:

delimiter // create procedure inserttimeintervals() begin   declare currenttime time default '00:00:00';   declare endtime time default '23:55:00';   truncate table time_intervals;   while currenttime <= endtime do     insert into time_intervals (grouped_time) values (currenttime);     set currenttime = addtime(currenttime, '00:05:00');   end while; end // delimiter ;
登录后复制

执行存储过程call inserttimeintervals();,生成288条时间段用于统计。

接下来,查询实际数据并补0:

(select   date_format(date_format(create_time, '%y-%m-%d %h:%i') - interval (minute(create_time) mod 5) minute, '%h:%i') as grouped_time,   count(*) as counts from   interface_access_frequency where   date(create_time) = '2024-04-27' group by   grouped_time) union select   date_format(grouped_time, '%h:%i') as grouped_time,   0 as counts from   time_intervals;
登录后复制

最后,groupby分组,即可获得每个5分钟区间的统计结果:

SELECT grouped_time, MAX(counts) AS counts FROM   ((SELECT     DATE_FORMAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') - INTERVAL (MINUTE(create_time) MOD 5) MINUTE, '%H:%i') AS grouped_time,     COUNT(*) AS counts   FROM     interface_access_frequency   WHERE     DATE(create_time) = '2024-04-27'   GROUP BY     grouped_time)   UNION   SELECT     DATE_FORMAT(grouped_time, '%H:%i') AS grouped_time,     0 AS counts   FROM     time_intervals) AS a GROUP BY   grouped_time ORDER BY   grouped_time ASC;
登录后复制

通过此方法,可以高效地按5分钟区间统计一天的数据量,且固定结果为288。修改传参,还可按任意时间区间进行统计。

相关阅读