SQL：几天内、两次之间的平均每小时

Question

我有一个包含 START_DATE 和 END_DATE 列的表。

我想检索这些时间戳之间每小时的平均时间差，但仅限于过去几天的上午 9 点到下午 6 点之间。我希望输出看起来像这样：

elapsed  hour
-------------
2.5      11 <--today at 11AM
1.7      10
2.4      9
1.9      18 <--this is yesterday
2.4      17
4.0      16

我相信我已经非常接近了，但我就是无法让代码工作。这就是我所拥有的：

SELECT 
    TRUNCATE(AVG(TIME_TO_SEC(TIMEDIFF(END_DATE, START_DATE))/60), 2) as elapsed,
    EXTRACT(HOUR FROM END_DATE) as hour
FROM 
    TIME_INFO
WHERE
    EXTRACT(HOUR FROM END_DATE) BETWEEN 9 AND 18 AND
    DATE(END_DATE) > CURDATE() - INTERVAL 3 DAY
GROUP BY 
    EXTRACT(HOUR FROM END_DATE)
ORDER BY 
    END_DATE DESC

它很接近，但只返回三天前我想要的内容，而不是像我想要的那样跨天返回。我正在使用 mySQL 5.0，有人有什么想法吗？

Answer 1

如果您想要多天，则需要在 GROUP BY 子句中提供日期和小时。目前，它正在对多天同一小时的所有值进行平均。 （今天 11 点、昨天 11 点等的值一起平均）

我没有简单的方法来测试它，但是类似：

SELECT 
    TRUNCATE(AVG(TIME_TO_SEC(TIMEDIFF(END_DATE, START_DATE))/60), 2) as elapsed,
    EXTRACT(DAY FROM END_DATE) as day,
    EXTRACT(HOUR FROM END_DATE) as hour
FROM 
    TIME_INFO
WHERE
    EXTRACT(HOUR FROM END_DATE) BETWEEN 9 AND 18 AND
    DATE(END_DATE) > CURDATE() - INTERVAL 3 DAY
GROUP BY 
    EXTRACT(DAY FROM END_DATE),
    EXTRACT(HOUR FROM END_DATE)
ORDER BY 
    END_DATE DESC

如果您希望它跨月/年边界工作，您可能需要 DATE(END_DATE) 而不是 EXTRACT(DAY FROM END_DATE)，在这种情况下，输出可能类似于：

elapsed  date        hour
------------------------- 
2.5      2011/03/25  11 <--today at 11AM
1.7      2011/03/25  10
2.4      2011/03/25  9
1.9      2011/03/24  18 <--this is yesterday
2.4      2011/03/24  17
4.0      2011/03/24  16