How to calculate the average value of time period by mysql

This article mainly introduces how to calculate the time period average of mysql, which has a certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

The method of mysql to calculate the average time period: first, concatenate the sql statement in the stored procedure; then add a period of time to the while loop according to the start time and end time; finally, you can average the time period.

Mysql's method of calculating the average value of the time period:

Idea: concatenate sql statements in stored procedures. Depending on the start time and end time, the while loop adds a period of time at a time.

DROP PROCEDURE IF EXISTS `DELIMITER; CREATE DEFINER= `root` @ `% `PROCEDURE `get_ avg` (in iStartTime datetime, in iEndTime datetime) BEGINdeclare vSql varchar (10240) default'; declare vNextTime datetime;while (iStartTime < iEndTime) do-- one hour at a time set vNextTime = date_add (iStartTime,interval 3600 second) -- single quotation marks are special characters. To indicate single quotation marks, use''to escape set vSql = concat (vSql,'union select 100,avg (`value`) from T1 where time between'', iStartTime,''' and'', vNextTime,''''); set iStartTime = vNextTime;end while;set vSql = substring (vSql,7);-- check whether the concatenated string is correct-- select vSql;set @ vSql = vSql;prepare stmt from @ vSql;execute stmt;deallocate prepare stmt;END DELIMITER: thank you for reading this article carefully. I hope it will be helpful for everyone to share how to find the average content of mysql. At the same time, I also hope that you will support it, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!