How to find the average value of a time period in mysql

Mysql method to find the average value of a time period: first, splice the sql statement in the storage process; then according to the start time and end time, the while loop adds a period of time each time; finally, average the time period.

The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22, Dell G3 computer.

Mysql method to find the average value of a time period:

Idea: In the stored procedure, splice sql statements. According to the start time and end time, the while loop adds a period of time each time.

DROP PROCEDURE IF EXISTS `get_avg`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `get_avg`(in iStartTime datetime, in iEndTime datetime)
BEGIN
declare vSql varchar(10240) default '';
declare vNextTime datetime;
while(iStartTime < iEndTime) do
-- 每次加一个小时
set vNextTime = date_add(iStartTime,interval 3600 second);
-- 单引号是特殊字符，要表示单引号，使用 &#39;&#39; 进行转义
set vSql = concat(vSql,&#39;union select 100, avg(`value`) from t1 where time between &#39;&#39;&#39;,iStartTime,&#39;&#39;&#39; and &#39;&#39;&#39;, vNextTime,&#39;&#39;&#39; &#39;);
set iStartTime = vNextTime;
end while;
set vSql = substring(vSql,7);
-- 看看拼接的字符串是否正确
-- select vSql;
set @vSql = vSql;
prepare stmt from @vSql;
execute stmt;
deallocate prepare stmt;
END
;;
DELIMITER ;

Related free recommendations: Programming video courses

More related free learning recommendations: mysql tutorial (video)

The above is the detailed content of How to find the average value of a time period in mysql. For more information, please follow other related articles on the PHP Chinese website!