Geek asks and answers

MySQL: sum of data excluding repeated overlap periods

I need to calculate the total amount of time spent on a table, for example:

id | start_time | end_time |

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

eg. if I have periods like this:

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

amount will be: (A.end-A.start) + (C.end - B.start) + (D.end - D.start)

I am a little confused by the approach that I should use to write this request, and we will be grateful for the help.

+5
source share
3 answers

, , , . , , .

, , .

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

, , :

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

, :

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
+2

, , . , MySQL .

- "F":

1         3              7           12 13    (15 16)        20
|----A----|              |------C-----| |----------D----------|
                  |-----B-----|              |---E---|
                  5           9              14     17
                                                |F|

  • , , ""

    SELECT 1 as onoff, start_time as time FROM table
UNION
SELECT -1 as onoff, end_time as time FROM table
ORDER BY time

  • (?) , 1 / 1 /

    script tmp.start=<time>, 0 1 tmp.end=<time> temp. , 1 0.

    script , :

    QUERY                       TMP TABLE
onoff | time  | ctr         ID | start | end
1     | 01:00 | 1           1  | 01:00 |            (record 1 added,   ctr 0->1)
-1    | 03:00 | 0           1  | 01:00 | 03:00      (record 1 updated, ctr 1->0)
1     | 05:00 | 1           2  | 05:00 |            (record 2 added,   ctr 0->1)
1     | 07:00 | 2                                   (nothing to do)
-1    | 09:00 | 1                                   (nothing to do)
-1    | 12:00 | 0           2  | 05:00 | 12:00      (record 2 updated, ctr 1->0)
1     | 13:00 | 1           3  | 13:00 |            (record 3 added,   ctr 0->1)
1     | 14:00 | 2                                   (nothing to do)
1     | 15:00 | 3                                   (nothing to do)
-1    | 16:00 | 2                                   (nothing to do)
-1    | 17:00 | 1                                   (nothing to do)
-1    | 20:00 | 0           3  | 13:00 | 20:00      (record 3 updated, ctr 1->0)

  • : timestampdiff() start end , / .

    :

    SELECT ID, start, end, timestampdiff(MINUTE, start, end) FROM tmp

    , : /,

    SELECT user_id, SUM(timestampdiff(MINUTE, start, end)) FROM tmp GROUP BY user_id

, , - , MySQL? .

PS: script "" , > 1 , < 0

+2

, , , - .

SELECT SUM(a.end_time - a.start_time) total_duration
  FROM (
    SELECT MIN(g.start_time) start_time, MAX(g.end_time) end_time 
      FROM (
        SELECT @group_id := @group_id + (@end_time IS NULL OR o.start_time > @end_time) group_id,
               start_time,
               @end_time := CAST(CASE 
                 WHEN (@end_time IS NULL OR o.start_time > @end_time) THEN o.end_time
                 ELSE GREATEST(o.end_time, @end_time)
               END AS DATETIME) end_time  
          FROM times o
          JOIN (SELECT @group_id := 0, @end_time := NULL) init
      ORDER BY o.start_time ASC  
            ) g
  GROUP BY  g.group_id  
        ) a

, , . End_time , , .

.

.

+1

More articles:


All Articles