无重叠时间差之和

eqqqjvef 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(385)

我有一个像这样的mysql表：

ID  | activity        | start               | end
372 | Machine Running | 2018-01-30 21:19:42 | 2018-01-30 21:52:41
373 | Order Active    | 2018-01-30 21:19:42 | 2018-01-30 21:45:02
374 | Operator Active | 2018-01-30 21:19:42 | 2018-01-30 21:52:41
375 | Reporting Active| 2018-01-30 21:19:48 | 2018-01-30 21:20:06
376 | Admin Active    | 2018-01-30 21:20:09 | 2018-01-30 21:52:41
378 | Order Active    | 2018-01-30 21:49:23 | 2018-01-30 21:52:41
982 | Machine Running | 2018-02-20 15:01:55 | 2018-02-20 15:01:55
983 | Order Active    | 2018-02-20 15:01:55 | 2018-02-20 15:01:55
984 | Operator Active | 2018-02-20 15:01:55 | 2018-02-20 15:01:55
986 | Machine Running | 2018-02-20 15:02:30 | 2018-02-20 15:02:30
987 | Order Active    | 2018-02-20 15:02:30 | 2018-02-20 15:02:30
988 | Operator Active | 2018-02-20 15:02:30 | 2018-02-20 15:02:30

我必须找出机器运行的时间和什么都不做的时间。所以机器运行的时间很容易。它只是开始和结束时间的时间差。
但是对于表中运行的每台机器，我需要一行“机器什么也不做”。在我看来，我必须从机器没有重叠的总运行时间中减去属于一台机器运行的所有活动的时间差，但我不知道怎么做。
所以对于上面的表，我需要这样的输出

start      | activity        | seconds
2018-07-02 | Machine Running | xx 
2018-07-02 | Doing nothing   | xx
2018-06-27 | Machine Running | xx
2018-06-27 | Doing nothing   | xx

mysql

来源：https://stackoverflow.com/questions/51473612/sum-of-timediffs-without-overlaps-mysql

1条答案

按热度按时间

dtcbnfnu1#

对于每种类型的间隔 Machine Running 您将需要找到所有类型的重叠间隔 Operator Active -我的意思是 Machine Running 以及 Operator Active . 然后你要减去每个的重叠部分 Operator Active 从相应的 Machine Running . 最终结果将是 Doing nothing .
为此，您需要一个公式来计算两个时间间隔之间的重叠

GREATEST(0,interval_1_end - interval_1_start - GREATEST(0,interval_1_end - interval_2_end) - GREATEST(0,interval_2_start - interval_1_start))

间隔必须表示为unix时间戳。
然后像这样

SELECT t1.start, UNIX_TIMESTAMP(t1.end) - UNIX_TIMESTAMP(t1.start) AS total_time, SUM(
GREATEST(0,UNIX_TIMESTAMP(t1.end) - UNIX_TIMESTAMP(t1.start)
  - GREATEST(0,UNIX_TIMESTAMP(t1.end) - UNIX_TIMESTAMP(t2.end)) 
  - GREATEST(0,UNIX_TIMESTAMP(t2.start) - UNIX_TIMESTAMP(t1.start)))
) AS do_nothing /* duration in seconds */
FROM tbl AS t1
LEFT JOIN tbl AS t2 ON (t2.start BETWEEN t1.start AND t1.end)
  OR (t2.end BETWEEN t1.start AND t1.end)
WHERE t1.activity = 'Machine Running' AND t2.activity = 'Operator Active'
GROUP BY t1.start

赞(0）回复(0）举报 2021-06-21

我来回答

无重叠时间差之和

1条答案

相关问题

热门标签

最新问答