dst2017
dst2017
2017-09-26 20:33
浏览 23
已采纳

总结所有时间段

I have a table where there are two columns that contain time_from and time_to of a particular event. Both columns' type is TINYINT(2). For eaxmple

id    time_from    time_to
__________________________
11       8            14
18       12           17    
44       20           24

Some periods overlap. I need to sum-up all time and make sure I do not double-count overlapped time.

Unfortunately I cannot change the column type and must work with what I've got. How can I do this?

The expected result is something like this:

14 - 8 = 6
17 - 12 = 5
24-20 = 4

Overlap is 2 hrs (12 - 14)

Total: 6 + 5 + 4 - 2 = 13

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doucheng5705
    doucheng5705 2017-09-26 21:09
    已采纳

    I'm assuming your time_from and time_to columns represent hours in the range 1 to 24.

    Edit. As you clarified, I'm assuming 20, 24 covers four hours, that is, 20, 21, 22, 23. Each range excludes the final hour mentioned: [20,24).

    You can solve this problem with a sequence table. This is it. (http://sqlfiddle.com/#!9/57cf7f/4/0)

          SELECT 1 seq 
            UNION ALL SELECT 2  UNION ALL SELECT 3  UNION ALL SELECT 4  UNION ALL SELECT 5
            UNION ALL SELECT 6  UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9
            UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
            UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
            UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
            UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
    

    In MariaDB, it's built in: the table seq_1_to_24 is it.

    Join it with your other table like this, and you get one row for each hour in each row of your other table. (http://sqlfiddle.com/#!9/57cf7f/9/0)

    SELECT seq.seq, t.*
      FROM (
              SELECT 1 seq UNION ALL SELECT 2  UNION ALL SELECT 3  UNION ALL SELECT 4  UNION ALL SELECT 5
                           UNION ALL SELECT 6  UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9
                           UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
                           UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
                           UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
                           UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
            ) seq
       JOIN times t ON seq.seq >= t.time_from 
                   AND seq.seq <  t.time_to
    

    Finally, summarize that with COUNT(DISTINCT seq) hours and you get the number of hours that appear in one or more of the time intervals in your original table. (http://sqlfiddle.com/#!9/57cf7f/10/0)

    SELECT COUNT(DISTINCT seq) hours
    FROM (
    SELECT seq.seq, t.*
      FROM (
              SELECT 1 seq UNION ALL SELECT 2  UNION ALL SELECT 3  UNION ALL SELECT 4  UNION ALL SELECT 5
                           UNION ALL SELECT 6  UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9
                           UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
                           UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
                           UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
                           UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
            ) seq
       JOIN times t ON seq.seq >= t.time_from 
                   AND seq.seq <  t.time_to
     ) a
    

    Here's what it looks like in MariaDB.

    SELECT COUNT(DISTINCT seq) hours
    FROM (
    SELECT seq.seq
      FROM seq_1_to_24 seq
       JOIN times t ON seq.seq >= t.time_from 
                   AND seq.seq <  t.time_to
     ) a
    
    点赞 评论
  • douqianke7467
    douqianke7467 2017-09-26 23:54

    The following solution assumes that the time_from and time_to are both sorted ascending and that the overlaps occur only between adjacent rows:

    # Without touching the `id` column, I create a sequential column `my_id`, assuming that the name of the  table is `times`:
    
    alter table times add my_id int(3) not null after id;
    SET @count = 0;
    UPDATE times SET my_id = @count:= @count + 1;
    
    # I use a temporary table `times2` that starts with the second row of the `times_from` column; Here, 9999 can be any large enough number:
    
    create temporary table times2 as select my_id, time_from from times limit 1, 9999;
    
    # Now I add a new column `time_end` to the original table. I update it with data from `time_from` column but 'shifted' by one row upwards:
    
    alter table times add time_end int(2) not null;
    update times a join times2 b on a.my_id = (b.my_id - 1) set a.time_end = b.time_from;
    
    # Ensure that the value of `time_end` in the last row is not zero:
    
    update times set time_end = time_to order by my_id desc limit 1;
    
    # The required value is taken by using the minimum of columns `time_from` and `time_end`:
    
    select sum(LEAST(time_to,time_end) - time_from) from times;
    
    # Uncomment below to clean up just after getting the results:
        # alter table times drop column my_id;
        # alter table times drop column time_end;  
    
    点赞 评论

相关推荐