MySQL:对日期时间求和，但不计算重叠时间两次

问题描述：

我需要基于一个表来计算花费的总时间:

I need to count the total amount of time spent based on a table like:

id | start_time | end_time |

期间可以重叠.我只需要计算一次换胎周期.

where periods can overlap. I need to count ovelpapping periods only once.

例如如果我有这样的时期:

E.g. if I have periods like these:

*----A----*              *------C-----* *----------D----------*
                  *-----B-----*              *---E---*

总和为:(A.end-A.start) + (C.end - B.start) + (D.end - D.start)

我对应该使用哪种方法编写此查询感到有些困惑，将不胜感激.

I'm a bit confused with the approach I should use to write this query and will be grateful for help.

答

好吧，我坚持要在生产中使用它之前，以各种方式对它进行测试. 尤其要测试在1个时间段内有多个重叠会发生什么情况.

Ok, I seriously insist you test this in all manners before using it in production. Especially test what happens if there are MULTIPLE overlaps for 1 time span.

此查询的作用是计算每个时间跨度的持续时间，以及与ID较高的其他时间跨度存在多少重叠.

What this query does is calculate the duration of each time span, and how much overlap exists with other time spans that have a higher id.

select
    t1.id,
    t1.start_time,
    t1.end_time,
    t1.end_time - t1.start_time as duration,
    sum(
          if(t2.start_time <  t1.start_time and t2.end_time >  t1.end_time  , t1.end_time - t1.start_time, 0) -- t2 completely around t1
        + if(t2.start_time >= t1.start_time and t2.end_time <= t1.end_time  , t2.end_time - t2.start_time, 0) -- t2 completely within t1
        + if(t2.start_time <  t1.start_time and t2.end_time >  t1.start_time and t2.end_time   < t1.end_time  , t2.end_time - t1.start_time, 0) -- t2 starts before t1 starts and overlaps partially
        + if(t2.start_time <  t1.end_time   and t2.end_time >  t1.end_time   and t2.start_time > t1.start_time, t1.end_time - t2.start_time, 0) -- t2 starts before t1 ends and overlaps partially
    ) as overlap
from
    times t1
    left join times t2 on
        t2.id > t1.id --  t2.id is greater than t1.id
        and (
               (t2.start_time <  t1.start_time and t2.end_time >  t1.end_time  ) -- t2 completely around t1
            or (t2.start_time >= t1.start_time and t2.end_time <= t1.end_time  ) -- t2 completely within t1
            or (t2.start_time <  t1.start_time and t2.end_time >  t1.start_time) -- t2 starts before t1 starts and overlaps
            or (t2.start_time <  t1.end_time   and t2.end_time >  t1.end_time  ) -- t2 starts before t1 ends and overlaps
        )
group by
    t1.id

因此，您最终想要得到的是这样:

So what you want to have in the end is this:

select
    sum(t.duration) - sum(t.overlap) as filtered_duration
from
    (
        OTHER QUERY HERE
    ) as t

所以最后您将得到以下查询:

So in the end you have this query:

select
    sum(t.duration) - sum(t.overlap) as filtered_duration
from
    (
        select
            t1.id,
            t1.start_time,
            t1.end_time,
            t1.end_time - t1.start_time as duration,
            sum(
                  if(t2.start_time <  t1.start_time and t2.end_time >  t1.end_time  , t1.end_time - t1.start_time, 0) -- t2 completely around t1
                + if(t2.start_time >= t1.start_time and t2.end_time <= t1.end_time  , t2.end_time - t2.start_time, 0) -- t2 completely within t1
                + if(t2.start_time <  t1.start_time and t2.end_time >  t1.start_time and t2.end_time   < t1.end_time  , t2.end_time - t1.start_time, 0) -- t2 starts before t1 starts and overlaps partially
                + if(t2.start_time <  t1.end_time   and t2.end_time >  t1.end_time   and t2.start_time > t1.start_time, t1.end_time - t2.start_time, 0) -- t2 starts before t1 ends and overlaps partially
            ) as overlap
        from
            times t1
            left join times t2 on
                t2.id > t1.id --  t2.id is greater than t1.id
                and (
                       (t2.start_time <  t1.start_time and t2.end_time >  t1.end_time  ) -- t2 completely around t1
                    or (t2.start_time >= t1.start_time and t2.end_time <= t1.end_time  ) -- t2 completely within t1
                    or (t2.start_time <  t1.start_time and t2.end_time >  t1.start_time) -- t2 starts before t1 starts and overlaps
                    or (t2.start_time <  t1.end_time   and t2.end_time >  t1.end_time  ) -- t2 starts before t1 ends and overlaps
                )
        group by
            t1.id
    ) as t

MySQL:对日期时间求和，但不计算重叠时间两次

相关推荐