sql-server - 根据事件开始和持续时间计算一段时间内的事件项目

Question

我正在使用 MS SQL Server 2005 并遇到以下问题。我有一个电话通话记录，需要分析一段时间内的线路利用率。我将尝试通过省略细节来简化任务，并仅询问我遇到的问题。假设调用记录到表中，其中包含以下列:调用开始的日期和时间、调用持续时间(以分钟为单位)以及所使用的线路号。我需要计算在每个特定时刻(每分钟)有多少条线路繁忙。实际上，我最终会通过进行一些聚合来使用更大的时间段(15 分钟、1/2 小时等)，但我对此没有任何问题。

因此示例源表(3 行)可能具有以下内容:

StartDateTime, Duration, LineNr
2013-01-01 10:00:00, 5, 1
2013-01-01 10:01:00, 6, 2
2013-01-01 10:03:00, 3, 3
2013-01-01 10:08:00, 2, 1
2013-01-01 10:09:00, 3, 2
2013-01-01 10:11:00, 1, 1

查询返回的结果表应如下所示:

Period, BusyLineCount
...
2013-01-01 09:58:00, 0
2013-01-01 09:59:00, 0
2013-01-01 10:00:00, 1
2013-01-01 10:01:00, 2
2013-01-01 10:02:00, 2
2013-01-01 10:03:00, 3
2013-01-01 10:04:00, 3
2013-01-01 10:05:00, 2
2013-01-01 10:06:00, 1
2013-01-01 10:07:00, 0
2013-01-01 10:08:00, 1
2013-01-01 10:09:00, 2
2013-01-01 10:10:00, 1
2013-01-01 10:11:00, 2
2013-01-01 10:12:00, 0
2013-01-01 10:13:00, 0
...

我怎样才能做到这一点？

附注这是我在这里发布的第一个问题，英语不是我的母语，所以请不要介意我做错了什么。预先感谢您的帮助。

Answer 1

这是一种使用递归 CTE 的方法。这可能会在小范围内表现良好，但您需要用更大的次数来测试它。

DECLARE @Start DATETIME, @End DATETIME
SET @Start = '20130101 09:58:00'
SET @End = '20130101 10:15:00'

;WITH TimePeriods AS
(
    SELECT @Start Period
    UNION ALL
    SELECT DATEADD(MINUTE,1,Period)
    FROM TimePeriods
    WHERE DATEADD(MINUTE,1,Period) <= @End
), LineUsage AS (
    SELECT StartDateTime, Duration, LineNr
    FROM YourTable
    UNION ALL
    SELECT DATEADD(MINUTE,1,StartDateTime), Duration-1, LineNr
    FROM LineUsage
    WHERE Duration-1 > 0
)
SELECT A.Period, COUNT(DISTINCT B.LineNr) BusyLineCount
FROM TimePeriods A
LEFT JOIN LineUsage B
  ON A.Period = B.StartDateTime
GROUP BY A.Period
ORDER BY A.Period
OPTION(MAXRECURSION 0)

<强> Here is a sql fiddle 进行现场演示。