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，有人有什么想法吗？

- Yottagray

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

- Tim Sylvester

---

是的，这个方法很有效。如果我能继续得到像这样的好建议，我最终会弄懂SQL的！非常感谢。 - Yottagray