PHP Mysql计算日期时间之间的平均值并忽略平均值中的负结果

Question

问题:如何执行下面的查询并让它忽略结果中可能为负的任何时间跨度，而不排除其他时间跨度的相关数据？ (换句话说，我不能将条件放在 WHERE 子句中，因为也许 Timespan1 和 2 是好的，但 timespan3 是负数，但我想保留它们。

我是否在 AVG 函数中放置了 case 语句？有没有更有效的方法来运行它？

    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)

我使用重复的 Alex 找到了它，其原理如下:

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

Answer 1

我相信您希望使用 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;