Shulou(Shulou.com)06/01 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.