mysql - 如何使用 SQL 查找不交叉的时间集？

Question

我在表 T 中有 N 条记录。每条记录都有时间间隔 - unix 时间戳格式的开始时间、结束时间。我想生成从最低开始时间到现在的时间间隔列表(开始+结束时间)，当时没有记录有时间间隔。如何用 MySQL 做这样的事情？

拥有类似 this 的数据我们想获得值(value)

(3, 4)
(9, 10)
(13, NOW())

Answer 1

除非我遗漏了什么，否则“间隙”的最大数量等于您拥有的时间间隔数。

考虑到这一点，这是我对您的问题的尝试:

SELECT DISTINCT
  gaps.gapStartTime as gapStartTime,
  coalesce(gaps.gapEndTime, NOW()) as gapEndTime
FROM
  /* Subquery to get the data we need to "build" the gaps*/
  (SELECT
    e1.endTime as gapStartTime,
    /* There is a gap if the endTime is not between another interval */
    CASE WHEN EXISTS
    (SELECT * FROM e AS e2 WHERE
     e1.endTime between e2.startTime and e2.endTime and
     e1.endTime <> e2.endTime and
     e1.id <> e2.id)
    THEN
      "false"
    ELSE "true" END AS isGap,
    (select min(e3.startTime) FROM e as e3 WHERE e3.startTime > e1.endTime) as gapEndTime
  FROM E AS e1) AS gaps
WHERE
  isGap = "true"
ORDER BY 
  gaps.gapStartTime

看看 Fiddle .

编辑:忘记了 ORDER BY 子句。