代码之家 › 专栏 › 技术社区 › Will

SQL中的日期范围交集

database mysql sql

Will · 技术社区 · 14 年前

我有一个表,其中每一行都有一个开始和结束日期时间。这些可以是任意的短跨度或长跨度。

我想查询具有两个开始日期和停止日期时间的所有行交叉点的总持续时间。

如何在MySQL中做到这一点?

或者,您是否必须选择与查询开始和停止时间相交的行,然后计算每行的实际重叠,并在客户端对其求和?

举个例子,用毫秒让它更清晰:

某些行:

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

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

让我们计算每行的重叠:

ROW  INTERSECTION
1    70
2    10
3     0

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

4 回复 | 直到 14 年前

Unreason 14 年前

如果你的例子在第一行应该说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

使用 greatest /最小和 date functions 您应该能够直接在日期类型上获得所需的内容。

Erwin Smout 14 年前

恐怕你运气不好。

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

您需要的聚合运算符不是选项,因为SQL没有它应该操作的数据类型(该类型是间隔类型,如“临时数据和关系模型”中所述)。

递归解决方案可能是可行的,但很可能难以编写,难以向其他程序员读取,而且优化器能否将该查询转换为最佳数据访问策略也值得怀疑。

或者我误解了你的问题。

Matt Gibson 14 年前

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

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

称之为时间维度(这种技术经常用于数据仓库中的维度建模。)

然后是:

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之间运行时间的总毫秒数。

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

正如我所说,这通常用于数据仓库;它很好地适用于某些类型的问题——例如,我将其用于保险系统,在保险系统中需要两个日期之间的总天数,并且数据的总体日期范围很容易估计(从最早的客户数据开始出生日期到未来几年的某个日期,超过了任何正在出售的保单的结束日期。)

可能不适合你,但我觉得这是值得分享的一个有趣的技巧!

Erwin Smout 14 年前

在您添加示例之后,很明显我误解了您的问题。

您不是“累积交叉行”。

为您提供解决方案的步骤包括:

将每一行的起点和终点与给定的起点和终点相交。这应该可以通过使用case表达式或类似性质的东西来实现,风格如下:

选择(case startdate<givenstartdate:givenstartdate,case startdate>=givenstartdate:startdate)作为retainedStartDate,(同样对于enddate),作为retainedEndDate,从…根据需要为空的东西和类似的东西提供食物。

对于retainedStartDate和retainedEndDate,使用日期函数计算保留间隔的长度(即行与给定时间段的重叠)。

选择其中的sum()。