php - MYSQL 查询计算每小时耗时

Question

我正在尝试在 MYSQL 中构建一个查询来获取今天的记录，然后每小时计算出有多少条记录以及耗时。

示例数据:

     Day      Time   Elapsed time
2015-01-28    9:00       0:53
2015-01-28    9:51       0:07
2015-01-28    10:32      1:45
2015-01-28    11:53      0:25

查询后的预期结果

Time  Count   Elapsed 
 7      0       0:0
 8      0       0:0
 9      2       1:00
 10     1       1:45
 11     1       0:25
 12     0       0:0
 13     0       0:0  
 14     0       0:0
 15     0       0:0
 16     0       0:0
 17     0       0:0

因此，我们有 7 - 17 之间的每个小时以及有多少条记录以及该小时的耗时。

我目前有这个查询:

SELECT  Date, 
            Count(Date) AS Count, 
            Sum(Elapsed) / 60 / 60 AS Elapsed, 
            avg(Elapsed) / 60 as average 
    FROM 
            telephone 
    WHERE 
            StartExt = 'E227'
            AND EndExt = 'E227'
            AND `Date` = CURDATE()
    Group by Date ORDER BY Date ASC

它获取一整天的总时间和耗时，而不是分割为每个小时，我怎样才能实现这一点？

Answer 1

对于所有有数据的小时，您可以使用 hour() 函数:

SELECT hour(time) as hour, Count(Date) AS Count, 
       Sum(Elapsed) / 60 / 60 AS Elapsed, avg(Elapsed) / 60 as average 
FROM telephone 
WHERE StartExt = 'E227' AND EndExt = 'E227' AND `Date` = CURDATE()
Group by hour(time)
ORDER BY hour ASC

如果您需要在没有电话的情况下工作几个小时，则需要付出额外的努力。您需要一个包含所需所有时间的列表，然后需要一个左连接。像这样的事情:

SELECT h.hh as hour, Count(time) AS Count, 
       Sum(Elapsed) / 60 / 60 AS Elapsed, avg(Elapsed) / 60 as average 
FROM (select 7 as hh union all select 8 union all select 9 union all
      . . .
      select 17
     ) h left join
     telephone t
     on h.hh = hour(t.time) and
        StartExt = 'E227' AND EndExt = 'E227' AND `Date` = CURDATE()
Group by h.hh
ORDER BY h.hh ASC

请注意，WHERE 条件已移至左外连接的 ON 子句。