PHP Mysql计算日期时间之间的平均值，并忽略平均值中的负结果 - PHP Mysql to calculate average between datetimes and ignore negative results in average

PHP Mysql to calculate average between datetimes and ignore negative results in average

问题：如何在不排除其他时间跨度的相关数据的情况下，接受下面的查询并让它忽略结果中可能为负数的任何时间跨度？（换句话说，我不能把条件放在WHERE子句中，因为时间跨度1和2可能是好的，但时间跨度3是负的，但我想保留它们。

我是否在AVG函数中放入案例陈述？有没有更有效的方法来运行这个？

    SELECT DATE(tbl.time1) AS 'date',
          AVG((time_to_sec(timediff(tbl.time2 , tbl.time1))/3600) ) AS 'avg_time1',
          AVG((time_to_sec(timediff(tbl.time3 , tbl.time2))/3600) ) AS 'avg_time2',
          AVG((time_to_sec(timediff(tbl.time4 , tbl.time3))/3600) ) AS 'avg_time3',
    FROM tbl
    WHERE DATE(tbl.time1) = '$variable'
    GROUP BY DATE(tbl.time1)

我发现它使用了重复亚历克斯提到的原理在这里：

SELECT AVG(`a`), AVG(IF(`b` > -1, `b`, NULL)), AVG(`c`) FROM `t`;

我相信您希望使用HAVING关键字？

SELECT DATE(tbl.time1) AS 'date',
          AVG((time_to_sec(timediff(tbl.time2 , tbl.time1))/3600) ) AS 'avg_time1',
          AVG((time_to_sec(timediff(tbl.time3 , tbl.time2))/3600) ) AS 'avg_time2',
          AVG((time_to_sec(timediff(tbl.time4 , tbl.time3))/3600) ) AS 'avg_time3',
    FROM tbl
    WHERE 
        AND DATE(tbl.time1) = '$variable'
    GROUP BY DATE(tbl.time1) 
    HAVING avg_time1 > 0 AND avg_time2 > 0 and avg_time3 > 0;

您可以使用case语句：

SELECT DATE(tbl.time1) AS "date",
       (CASE WHEN AVG(time_to_sec(timediff(tbl.time2 , tbl.time1))/3600) >= 0
             THEN AVG(time_to_sec(timediff(tbl.time2 , tbl.time1))/3600)
        END) as avg_time1,
       (CASE WHEN AVG(time_to_sec(timediff(tbl.time3 , tbl.time2))/3600) >= 0
             THEN AVG(time_to_sec(timediff(tbl.time3 , tbl.time2))/3600) 
        END) as avg_time2,
       (CASE WHEN AVG(time_to_sec(timediff(tbl.time4 , tbl.time3))/3600) >= 0
             THEN AVG(time_to_sec(timediff(tbl.time4 , tbl.time3))/3600)
        END) as avg_time3
FROM tbl
WHERE DATE(tbl.time1) = '$variable'
GROUP BY DATE(tbl.time1);

这将返回NULL值，而不是负值，我猜这正是您想要的。

此外，您应该只对日期和字符串文字值使用单引号，而不对标识符（如列名）使用单引号。尽管MySQL允许这种语法，但使用它通常会导致混乱。