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

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

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

1. 创建时间间隔表

create table time_intervals (
  grouped_time time default null
);

1. 插入时间间隔数据

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 ;

1. 按每 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;

1. 使用 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;

1. 分组汇总结果

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 分钟间隔汇总的当天数据量，即使没有数据也会补零。

以上就是如何使用MySQL按每5分钟间隔汇总一天的数据量？的详细内容，更多请关注慧达安全导航其它相关文章！