我有一个包含工人、工作站和会话的数据库。会话描述哪个工人在哪个时间上了哪个工作站。我设法构建了一个查询,为我提供了每个会话重叠的持续时间。
SELECT
sA.station_id,
sA.worker_id AS worker1,
sB.worker_id AS worker2,
SEC_TO_TIME(
TIME_TO_SEC(LEAST(sA.end,sB.end)) - TIME_TO_SEC(GREATEST(sA.start,sB.start))
) AS overlap
FROM
`sessions` AS sA,
`sessions` AS sB
WHERE
sA.station_id = sb.station_id
AND
sA.station_id = 6
AND (
sA.start BETWEEN sB.start AND sB.end
OR
sA.end BETWEEN sB.start AND sB.end
)
通过这个查询,我得到了这样的结果
station_id worker1 worker2 overlap
6 1 1 09:00:00
6 2 1 02:30:00
6 5 1 00:00:00
6 1 1 09:00:00
6 2 1 01:30:00
6 3 1 09:00:00
...
6 12 3 02:00:00
6 14 3 01:00:00
6 17 3 02:00:00
...
我现在想要的是对worker1和worker2的每个组合的重叠进行总结,以获得整体重叠持续时间。
我尝试了使用SUM((和GROUP BY的不同方法,但始终没有得到想要的结果。
SELECT
...
SEC_TO_TIME(
**SUM**(TIME_TO_SEC(LEAST(sA.end,sB.end)) - TIME_TO_SEC(GREATEST(sA.start,sB.start)))
) AS overlap
...
#has as result
station_id worker1 worker2 overlap
6 1 1 838:59:59
#in combination with
GROUP BY
worker1
#i get
station_id worker1 worker2 overlap
6 1 1 532:30:00
6 2 1 -33:00:00
6 3 1 270:30:00
6 5 1 598:30:00
6 6 1 542:00:00
6 7 1 508:00:00
6 8 5 53:00:00
6 9 1 54:30:00
6 10 1 310:00:00
6 11 1 -108:00:00
6 12 1 593:30:00
6 14 1 97:30:00
6 15 1 -53:30:00
6 17 1 293:30:00
最后的结果很接近,但我仍然错过了很多组合。我也不明白为什么显示组合8-5。
感谢您的帮助(以及阅读时间(
aaargh,很抱歉我的愚蠢,解决方案相当简单
....
SUM(((UNIX_TIMESTAMP(LEAST(sA.end,sB.end))-UNIX_TIMESTAMP(GREATEST(sA.start,sB.start)))/3600))
...
GROUP BY station_id, worker1, worker2
ORDER BY worker1, worker2
我改为使用时间戳,并在/3600之前将其转换为小时,因为我以前使用的TIME_to_SEC和SEC_to_TIME方法只使用了DATETIME字段的TIME部分,因此产生了一些错误的数字。使用MySQL 5.5,我可以使用TO_SECONDS,但不幸的是,我的服务器仍在运行5.1。