MYSQL group by hour and sum diff 但按小时分隔结果

Question

我有以下数据集

start_time              end_time
2017-09-12 12:08:55     2017-09-12 12:10:57
2017-09-12 12:15:10     2017-09-12 12:19:27
2017-09-12 12:24:54     2017-09-12 12:25:35
2017-09-12 12:27:32     2017-09-12 13:20:25
2017-09-12 13:20:25     2017-09-12 13:22:23
2017-09-12 13:32:01     2017-09-12 13:33:57
2017-09-12 13:45:15     2017-09-12 13:46:38
2017-09-12 13:52:52     2017-09-12 13:54:20
2017-09-12 13:59:28     2017-09-12 14:02:32
2017-09-12 14:04:37     2017-09-12 14:07:34
2017-09-12 14:12:49     2017-09-12 14:13:03
2017-09-12 14:34:09     2017-09-12 14:35:47
2017-09-12 14:40:52     2017-09-12 14:46:46
2017-09-12 14:51:57     2017-09-12 14:54:28

我正在尝试查找按小时分组的时差总和。由于有些 block 在一个小时内开始但在下一个小时内结束，所以这个总和的计算方式不是我想要的。例如，如果我们将 2017-09-12 12:27:32 - 2017-09-12 13:20:25 的总差异添加到第 12 小时，并从第 13 小时开始忽略。

我正在使用以下查询进行分组和求和

SELECT 
DATE_FORMAT(start_time, "%H") as h, 
SEC_TO_TIME(SUM(TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))) AS timediff 
FROM history 
WHERE start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
GROUP BY hour(start_time)

得到结果

h   timediff
12  00:59:53
13  00:09:49
14  00:13:14

有没有办法打破像 2017-09-12 12:27:32 - 2017-09-12 13:20:25 这样的 block ，并将差异分别添加到 mysql 中的各个小时 block ？

首选输出

h   timediff
12  00:39:28
13  00:27:42
14  00:15:46

(从2017-09-12 12:27:32 - 2017-09-12 13:20:25，32m 28s应该去12小时 block ，20m 25s应该去13h block )

Answer 1

为此使用 mysql 子查询。先将数据按小时划分，然后尝试添加时间段。

SELECT SEC_TO_TIME(SUM(t.h)) timediff, t.hc FROM 
(
SELECT start_time, end_time,
(  UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP( start_time )  ) h, HOUR( start_time ) hc from history 
WHERE HOUR(end_time) = HOUR(start_time) AND start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
UNION
SELECT start_time, end_time,
(  UNIX_TIMESTAMP( end_time ) - UNIX_TIMESTAMP(DATE_FORMAT(end_time, '%Y-%m-%d %H:00:00'))) call_time, HOUR( end_time ) hc from history 
WHERE HOUR(end_time) > HOUR(start_time) AND start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
UNION
SELECT start_time, end_time,
(  UNIX_TIMESTAMP(DATE_FORMAT(end_time, '%Y-%m-%d %H:00:00')) - UNIX_TIMESTAMP( start_time )  ) call_time, HOUR( start_time ) hc from history 
WHERE HOUR(end_time) > HOUR(start_time) AND start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
) t GROUP BY t.hc