如何使用MySQL按每5分钟间隔汇总一天的数据量？-小浪学习网

如何使用MySQL按每5分钟间隔汇总一天的数据量？

mysql 按每 5 分钟间隔汇总一天的数据量

为了按每 5 分钟间隔汇总一天的数据量，我们可以采取以下步骤：

创建时间间隔表

create table time_intervals (   grouped_time time default null );

插入时间间隔数据

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 ;

按每 5 分钟查询真实数据

select      date_format(create_time, '%y-%m-%d %h:%i') - interval (minute(create_time) mod 5) minute as grouped_time,     count(*) as couns from      interface_Access_frequency  where         date(create_time) = '2024-04-27' group by      grouped_time;

使用 union 链接补零数据

(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 couns from      interface_access_frequency  where         date(create_time) = '2024-04-27' group by      grouped_time order by create_time desc ) union select  date_format(grouped_time, '%h:%i') as grouped_time,0 as counts from time_intervals;

分组汇总结果

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) a  GROUP BY  grouped_time ORDER BY grouped_time ASC;

最终，我们将得到按每 5 分钟间隔汇总的当天数据量，即使没有数据也会补零。

文章版权归作者所有，未经允许请勿转载。

THE END