SQL Server相关问题

Question

我需要完成以下任务：针对一张包含电话记录的SQL Server表格，每个电话记录都有起始时间和结束时间。需要编写一个存储过程，在特定时间段内（例如5小时），以固定间隔（例如2分钟）返回连接电话数量。

Something like:

Interval                                    Nr of Calls Connected
01-01-2010 12:00:00 - 01-01-2010 12:05:00   30
01-01-2010 12:05:01 - 01-01-2010 12:10:00   10
.............

如何最快地完成这个任务？感谢您的帮助。

- stefan

---

你想展示至少有1个通话的时间间隔结果吗？还是展示即使它们没有通话的时间间隔？ - Gabriele Petrioli

每个时间间隔即使其中没有调用也要进行。SQL Server 2005。 - stefan

你想要一个过程，根据时间间隔告诉你调用的次数，还是想要一个返回时间间隔及其调用次数列表的过程？ - Gabriel Guimarães

Proc必须返回一个带有调用次数的间隔列表。 - stefan

有人能否更改一下这个问题的标题？它太模糊了。 - Chris Simpson

Answer 1

这将适用于带有调用的间隔...

Declare @datetimestart datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 5 --in minutes

Select
  [start_interval], [end_interval] , count([start_interval]) as [calls]
From
  (
    Select 
     DateAdd( Minute,Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart) , 
    DateAdd( Minute,@interval + Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart) 
    From yourTable
  ) As W([start_interval],[end_interval])
group by [start_interval], [end_interval]

这将适用于所有间隔，无论调用次数如何...

Declare @datetimestart datetime, @datetimeend datetime, @datetimecurrent datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 10
Set @datetimeend = (Select max([date]) from yourtable)

SET @datetimecurrent = @datetimestart

declare @temp as table ([start_interval] datetime, [end_interval] datetime)

while  @datetimecurrent < @datetimeend
BEGIN
  insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent)
  set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent)
END

Select
  *
From
  (
    Select 
      [start_interval],[end_interval], count(d.[start_time]) 
    From @temp t left join yourtable d on d.[start_time] between t.[start_interval] and t.[end_interval]
  ) As W([start_interval],[end_interval], [calls])

- Gabriele Petrioli

---

嗨Gaby，感谢您的帮助。我正在查看最后一个查询，以包括所有间隔，并且这个从@temp t左连接yourtable d on d.[start_time] between t.[start_interval] and t.[end_interval]只考虑在该间隔内开始的通话，并省略了开始日期<间隔开始和结束日期>间隔结束的通话。这些通话仍然连接，并且应该被包括在内。 - stefan

@stefan，我明白了... 所以一次调用可能会在多个时间间隔中被计算，是吗？ - Gabriele Petrioli

@stefan，如果是这样的话，以下应该涵盖所有情况（我认为）on ( (d.[start_time] between t.[start_interval] and t.[end_interval] ) OR (d.[end_time] between t.[start_interval] and t.[end_interval] ) OR (d.[start_time] < t.[start_interval] AND d.[end_time] > t.[end_interval])。 - Gabriele Petrioli

d.end_time >= t.start_interval AND d.start_time <= t.end_interval - Gabriel Guimarães

@Gabriel 哈哈...当然你是对的..我过度复杂化了.. 脸掌.. - Gabriele Petrioli

谢谢Gaby和Gabriel。解决方案非常出色。 - stefan

Answer 2

我稍微修改了Gaby的例子，以达到你的预期

Declare @datetimeend datetime
,@datetimecurrent datetime
,@interval int
Set @interval = 10
Set @datetimeend = (Select max([end_time]) from Calls)
SET @datetimecurrent = '2010-04-17 14:20:00'

declare @temp as table ([start_interval] datetime, [end_interval] datetime)

while  @datetimecurrent < @datetimeend
BEGIN
  insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent)
  set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent)
END

Select 
  [start_interval],[end_interval], count(d.id) [COUNT]
From @temp t 
left join Calls d on 
    d.end_time >= t.start_interval
AND d.start_time <= t.end_interval
GROUP BY [start_interval],[end_interval]

使用这个来创建表并填充它

CREATE TABLE dbo.Calls
    (
    id int NOT NULL IDENTITY (1, 1),
    start_time datetime NOT NULL,
    end_time datetime NULL,
    caller nvarchar(50) NULL,
    receiver nvarchar(50) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Calls ADD CONSTRAINT
    PK_Calls PRIMARY KEY CLUSTERED 
    (
    id
    )  ON [PRIMARY]

GO

DECLARE @I INT
SET @I = 0
WHILE @I < 100
BEGIN
INSERT INTO Calls
(start_time, end_time)
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-10,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-9,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-9,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-8,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-8,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-7,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-7,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-6,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-6,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-5,GETDATE()))
UNION
SELECT
DATEADD(HOUR,-@I,DATEADD(MINUTE,-5,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-4,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-4,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-3,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-3,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-2,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-2,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-1,GETDATE()))
UNION
select 
DATEADD(HOUR,-@I,DATEADD(MINUTE,-1,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-0,GETDATE()));
SET @I = @I + 1
END    

已在SQL Server 2008中完成，但脚本也适用于其他版本。

- Gabriel Guimarães

Answer 3

我会使用数字数据透视表来获取时间间隔，然后计算所有与该时间段重叠的通话数量：

SELECT Intervals.IntervalStart
    ,Intervals.IntervalEnd
    ,COUNT(*)
FROM (
    SELECT DATEADD(MINUTE, Numbers * 2, @StartTime) AS IntervalStart
        ,DATEADD(MINUTE, (Numbers + 1) * 2, @StartTime) AS IntervalEnd
    FROM Numbers
    WHERE Numbers BETWEEN 0 AND (5 * 60 / 2)
) AS Intervals
LEFT JOIN Calls
    ON Calls.CallEnd >= Intervals.IntervalStart
        AND Calls.CallStart < Intervals.IntervalEnd
GROUP BY Intervals.IntervalStart
    ,Intervals.IntervalEnd

要获取空的时间间隔，您需要从另一个“Intervals”派生表中使用LEFT JOIN连接到此表。

- Cade Roux

Answer 4

这个方法怎么样？

select Year(StartTime) as Year, Month(StartTime) as Month, Day(StartTime) as Day, datepart(hh, StartTime) as Hour, datepart(mm, StartTime) / 2 as TwoMinuteSegment, count(*)
from MyTable
where StartDate between '01-01-2010 12:00:00' and '01-01-2010 17:00:00'
group by Year(StartTime), Month(StartTime), Day(StartTime), datepart(hh, StartTime), datepart(mm, StartTime) / 2

- D'Arcy Rittich