mysql - 如何优化这个mysql程序代码

Question

我有这个工作mysql程序有没有办法用for循环来检查时间以最小化代码这是我想要平均一天 2 小时周期的过程代码，该天由参数 daydate 定义以及从“00:00:00”到“24:00:00”的时间

CREATE DEFINER=`user`@`localhost` PROCEDURE `proceduregetavgperday`(IN daydate date)
BEGIN
(select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        "00:00:00" as "Hour"
        from viewemotionavgbydateandtime where `Time` >= '00:00:00' and `Time` < '02:00:00' and `Date`=daydate) 
        union
(select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        "02:00:00" as "Hour"
        from viewemotionavgbydateandtime where `Time` >= '02:00:00' and `Time` < '04:00:00' and `Date`=daydate)
union
(select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        "04:00:00" as "Hour"
        from viewemotionavgbydateandtime where `Time` >= '04:00:00' and `Time` < '06:00:00' and `Date`=daydate)
union
(select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        "06:00:00" as "Hour"
        from viewemotionavgbydateandtime where `Time` >= '06:00:00' and `Time` < '08:00:00' and `Date`=daydate)
union
(select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        "08:00:00" as "Hour"
        from viewemotionavgbydateandtime where `Time` >= '08:00:00' and `Time` < '10:00:00' and `Date`=daydate)
union
(select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        "10:00:00" as "Hour"
        from viewemotionavgbydateandtime where `Time` >= '10:00:00' and `Time` < '12:00:00' and `Date`=daydate)
union
(select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        "12:00:00" as "Hour"
        from viewemotionavgbydateandtime where `Time` >= '12:00:00' and `Time` < '14:00:00' and `Date`=daydate)

 ;
END

Answer 1

所以我找到了一个使用 for 的解决方案

CREATE DEFINER=`root`@`localhost` PROCEDURE `proceduregetavgperdayfor`(IN daydate date)
BEGIN

declare TimeVar Time;
declare Step Time;
DROP TABLE if exists ResultTable;
CREATE TEMPORARY table ResultTable (`Anger` float,`Contempt` float,`Disgust` float,`Fear` float,`Happiness` float,`Neutral` float,`Sadness` float,`Surprise` float,`Houre` varchar(50));

SET TimeVar='00:00:00';
SET Step='02:00:00';

loop1: LOOP
insert  into ResultTable
select IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Happiness`), 2),0) AS `Happiness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Anger`), 2),0) AS `Anger`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Contempt`), 2),0) AS `Contempt`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Disgust`), 2),0) AS `Disgust`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Fear`), 2),0) AS `Fear`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Neutral`), 2),0) AS `Neutral`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Sadness`), 2),0) AS `Sadness`,
        IFNULL(ROUND(AVG(`viewemotionavgbydateandtime`.`Surprise`), 2),0) AS `Surprise`,
        TimeVar as "Hour"
        from viewemotionavgbydateandtime where `Time` >= TimeVar and `Time` < TimeVar+Step and `Date`=daydate;
    SET TimeVar=TimeVar+Step;
    IF TimeVar>'24:00:00' THEN 
    LEAVE loop1;
    END IF;
    ITERATE loop1;
    END Loop loop1;
    select * from ResultTable;
END