php - 总结所有时间段

Question

我有一个表，其中有两列包含特定事件的 time_from 和 time_to。两列的类型都是 TINYINT(2)。例如

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

有些时期重叠。我需要总结所有时间并确保我不会重复计算重叠时间。

不幸的是，我无法更改列类型，必须使用已有的内容。我该怎么做？

预期的结果是这样的:

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

重叠是 2 小时 (12 - 14)

总计:6 + 5 + 4 - 2 = 13

Answer 1

我假设您的 time_from 和 time_to 列代表 1 到 24 之间的小时数。

编辑。正如您所阐明的，我假设 20、24 涵盖四个小时，即 20、21、22、23。每个范围不包括提到的最后一个小时:[20,24)。

你可以用序列表来解决这个问题。就是这个。 ( 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

在 MariaDB 中，它是内置的:表 seq_1_to_24 就是它。

像这样将它与您的其他表连接起来，您在其他表的每一行中每小时得到一行。 ( 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

最后，使用 COUNT(DISTINCT seq) hours 总结一下，您将获得原始表中一个或多个时间间隔中出现的小时数。 ( 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

这是它在 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