SQL中的日期范围交集

Question

我有一张表格，每行都有一个开始和结束的日期时间。它们可能是任意短或长的时间跨度。

我想查询所有行与两个开始和结束日期时间的交集的总持续时间。

如何在MySQL中实现此功能？

还是说您需要选择与查询开始和结束时间相交的行，然后在客户端计算每行的实际重叠并将其求和？

举个例子，使用毫秒使其更清晰：

一些行：

ROW  START  STOP
1    1010   1240
2     950   1040
3    1120   1121

我们想知道这些行在1030和1100之间的总时间。

让我们计算每一行的重叠部分：

ROW  INTERSECTION
1    70
2    10
3     0

所以这个例子中的总和是80。

- Will

---

我很难理解你的问题，请你举个例子。 - lexu

像1-10、2-9、3-8这样的重叠部分的总和是多少？ - aioobe

@Will - 如果我理解正确，你在示例＃1中的交集不应该是30；intersection((1010, 1240),(1030, 1100)) = 70。 - Unreason

@Unreason，是的，我说的是毫秒，然后我开始以60秒为单位计数，抱歉。 - Will

好的，那你试过我的解决方案了吗？ - Unreason

Answer 1

如果你的示例在第一行应该是70，假设@range_start和@range_end为你的条件参数：

SELECT SUM( LEAST(@range_end, stop) - GREATEST(@range_start, start) )
FROM Table
WHERE @range_start < stop AND @range_end > start

使用最大值/最小值和日期函数，您应该能够直接在日期类型上操作以获取所需内容。

- Unreason

Answer 2

如果您知道最长时间，有一个相当有趣的解决方案。创建一个包含从一到最长时间的所有数字的表格。

millisecond
-----------
1
2
3
...
1240

称其为 time_dimension（在数据仓库的维度建模中经常使用此技术）。

然后是这个：

SELECT 
  COUNT(*) 
FROM 
  your_data 
    INNER JOIN time_dimension ON time_dimension.millisecond BETWEEN your_data.start AND your_data.stop
WHERE 
  time_dimension.millisecond BETWEEN 1030 AND 1100

...将为您提供1030到1100之间运行时间的总毫秒数。

当然，您能否使用此技术取决于您是否能够安全地预测数据中最大的毫秒数。

这通常用于数据仓库，正如我所说; 它适用于某些类型的问题--例如，我曾在保险系统中使用它，需要计算两个日期之间的总天数，并且数据的整体日期范围易于估计（从最早的客户出生日期到未来几年的日期，超出了任何正在销售的保单的结束日期。）

可能对您不起作用，但我认为值得分享作为一种有趣的技术！

- Matt Gibson

Answer 3

我恐怕你没有运气了。

由于您不知道将要“累积相交”的行数，因此您需要递归解决方案或聚合运算符。

所需的聚合运算符不可用，因为SQL没有它应该操作的数据类型（该类型是间隔类型，如“时间数据和关系模型”中所述）。

递归解决方案可能是可行的，但编写起来可能很困难，对其他程序员来说也很难阅读，并且还有疑问优化器是否能将该查询转换为最佳数据访问策略。

或者我误解了您的问题。

- Erwin Smout

Answer 4

在您添加示例之后，我确实误解了您的问题。

您并没有“累计交错行”。

带您找到解决方案的步骤是：

将每行的起始点和结束点与给定的起始点和结束点相交。这应该可以使用CASE表达式或类似的语法完成，例如：

SELECT (CASE startdate < givenstartdate : givenstartdate, CASE startdate >= givenstartdate : startdate) as retainedstartdate,（同样适用于enddate）as retainedenddate FROM ... 根据需要处理null值等等。

使用保留的开始日期和结束日期，使用日期函数计算保留间隔的长度（即您的行与给定时间段的重叠部分）。

选择其SUM()。

- Erwin Smout