sql - 从 SQL 分析时间相关的事件日志

Question

我在 SQL Server 数据库中有一个事件日志。从本质上讲，它记录了调用的时间和调用在调用中心结束的时间(作为两个不同的记录)，以及一些其他细节。我试图通过这些数据了解在任何给定时间使用了多少条电话线。我想不出任何让 SQL 查询为我确定这一点的好方法，尽管这将是理想的(如果它没有牺牲太多速度)。

我的第一个想法是让一个程序查询每个调用的开始和结束事件，以确定调用的持续时间。然后我可以遍历每个时间单位，记录在任何给定时间有多少正在进行的调用。有没有一种方法可以在 SQL 中执行此操作，而不是在 C# 或类似的东西中使用线性方法？

编辑:调用有一个唯一的 ID。一个 session ID，如果你愿意的话。此外，开始和结束事件是两条不同的记录 -- 不是一条记录。我认为这有点复杂。此外，该表中有超过 1500 万条记录。

Id  EvId             CallId                           DateTime       
--  ---- ------------------------------------    --------------------
 1  0   df1cbc93-5cf3-402a-940b-4441f6a7ec5c     7/9/2008 8:12:56 PM
 2  1   df1cbc93-5cf3-402a-940b-4441f6a7ec5c     7/9/2008 8:13:07 PM
 3  0   ec1c2078-1765-4377-9126-6f26fe33e4a9    7/10/2008 4:33:10 PM
 4  10  ec1c2078-1765-4377-9126-6f26fe33e4a9    7/10/2008 4:33:13 PM
 5  1   ec1c2078-1765-4377-9126-6f26fe33e4a9    7/10/2008 4:33:13 PM
 6  0   a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d    7/10/2008 4:33:13 PM
 7  10  a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d    7/10/2008 4:33:15 PM
 8  1   a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d    7/10/2008 4:33:15 PM
 9  0   d23f393d-0272-445a-8670-3f71b016174e    7/10/2008 4:33:15 PM
10  10  d23f393d-0272-445a-8670-3f71b016174e    7/10/2008 4:33:17 PM
11  1   d23f393d-0272-445a-8670-3f71b016174e    7/10/2008 4:33:17 PM


EvId   Description
----   ----------------
  0 New Call
  1 End of Call
  2 Caller Hangup
 10 CPA Completed

Answer 1

在你使用我的查询示例之前，你需要建立一个“帮助”表，你只需要为每个数据库做一次:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

这基本上创建了一个包含单个列的表，该列包含从 1 到 8000 的值。您可以使用 CTE 来做同样的事情，但由于您没有说 SQL Server 版本，这对所有人都有效，而且更好如果你要多次运行。

试试这个:

DECLARE @Calls  table (rowID int not null primary key identity(1,1)
                      ,EvId int not null
                      ,CallId varchar(36)
                      ,rowDateTime datetime
                      )
SET NOCOUNT ON
INSERT INTO @Calls VALUES ( 0,'df1cbc93-5cf3-402a-940b-4441f6a7ec5c',' 7/9/2008 8:12:56 PM')
INSERT INTO @Calls VALUES ( 1,'df1cbc93-5cf3-402a-940b-4441f6a7ec5c',' 7/9/2008 8:13:07 PM')
INSERT INTO @Calls VALUES ( 0,'ec1c2078-1765-4377-9126-6f26fe33e4a9','7/10/2008 4:33:10 PM')
INSERT INTO @Calls VALUES (10,'ec1c2078-1765-4377-9126-6f26fe33e4a9','7/10/2008 4:33:13 PM')
INSERT INTO @Calls VALUES ( 1,'ec1c2078-1765-4377-9126-6f26fe33e4a9','7/10/2008 4:33:13 PM')
INSERT INTO @Calls VALUES ( 0,'a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d','7/10/2008 4:33:13 PM')
INSERT INTO @Calls VALUES (10,'a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d','7/10/2008 4:33:15 PM')
INSERT INTO @Calls VALUES ( 1,'a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d','7/10/2008 4:33:15 PM')
INSERT INTO @Calls VALUES ( 0,'d23f393d-0272-445a-8670-3f71b016174e','7/10/2008 4:33:15 PM')
INSERT INTO @Calls VALUES (10,'d23f393d-0272-445a-8670-3f71b016174e','7/10/2008 4:33:17 PM')
INSERT INTO @Calls VALUES ( 1,'d23f393d-0272-445a-8670-3f71b016174e','7/10/2008 4:33:17 PM')
--I added more test data, to hit more cases
INSERT INTO @Calls VALUES ( 0,'111111111111111111111111111111111111','7/10/2008 4:10:00 PM')
INSERT INTO @Calls VALUES (10,'111111111111111111111111111111111111','7/10/2008 4:11:00 PM')
INSERT INTO @Calls VALUES ( 1,'111111111111111111111111111111111111','7/10/2008 4:11:00 PM')
INSERT INTO @Calls VALUES ( 0,'222222222222222222222222222222222222','7/10/2008 4:15:00 PM')
INSERT INTO @Calls VALUES (10,'222222222222222222222222222222222222','7/10/2008 4:16:00 PM')
INSERT INTO @Calls VALUES ( 1,'222222222222222222222222222222222222','7/10/2008 4:16:00 PM')
INSERT INTO @Calls VALUES ( 0,'333333333333333333333333333333333333','7/10/2008 4:09:00 PM')
INSERT INTO @Calls VALUES (10,'333333333333333333333333333333333333','7/10/2008 4:18:00 PM')
INSERT INTO @Calls VALUES ( 1,'333333333333333333333333333333333333','7/10/2008 4:18:00 PM')
INSERT INTO @Calls VALUES ( 0,'444444444444444444444444444444444444','7/10/2008 4:13:00 PM')
INSERT INTO @Calls VALUES (10,'444444444444444444444444444444444444','7/10/2008 4:14:00 PM')
INSERT INTO @Calls VALUES ( 1,'444444444444444444444444444444444444','7/10/2008 4:14:00 PM')
INSERT INTO @Calls VALUES ( 0,'555555555555555555555555555555555555','7/10/2008 4:13:00 PM')
SET NOCOUNT OFF

DECLARE @StartRange  datetime
DECLARE @EndRange    datetime

SET @StartRange='7/10/2008 4:12:00 PM'
SET @EndRange  ='7/10/2008 4:15:00 PM'

SET @EndRange=DATEADD(mi,1,@EndRange)

--this lists the match time and each calls details in progress at that time
SELECT
    DATEADD(mi,n.Number-1,c.StartTime) AS 'TimeOfMatch'
        ,c.CallID
        ,c.StartTime,c.EndTime
    FROM (SELECT --this derived table joins together the start and end dates into a single row, filtering out rows more than 90 minutes before the start range (if calls are longer than 90 minutes, increase this) and filters out any rows after the end date (will consider call done at end date then)
              CallID, MIN(rowDateTime) AS StartTime, CASE  WHEN MAX(rowDateTime)=MIN(rowDateTime) THEN @EndRange ELSE MAX(rowDateTime) END  AS EndTime
              FROM @Calls 
              WHERE rowDateTime>=DATEADD(mi,-90,@StartRange) --AND rowDateTime<=@EndRange
              GROUP BY CallID
         ) c
        INNER JOIN Numbers   n ON DATEDIFF(mi,c.StartTime,c.EndTime)+1>=n.Number
    WHERE DATEADD(mi,n.Number-1,c.StartTime)>=@StartRange AND DATEADD(mi,n.Number-1,c.StartTime)<@EndRange
    ORDER BY 1

--this lists just the match time and the call count
SELECT
    DATEADD(mi,n.Number-1,c.StartTime) AS 'TimeOfMatch'
        ,c.CallID
        ,c.StartTime,c.EndTime
    FROM (SELECT --this derived table joins together the start and end dates into a single row, filtering out rows more than 90 minutes before the start range (if calls are longer than 90 minutes, increase this) and filters out any rows after the end date (will consider call done at end date then)
              CallID, MIN(rowDateTime) AS StartTime, CASE  WHEN MAX(rowDateTime)=MIN(rowDateTime) THEN @EndRange ELSE MAX(rowDateTime) END  AS EndTime
              FROM @Calls 
              WHERE rowDateTime>=DATEADD(mi,-90,@StartRange) --AND rowDateTime<=@EndRange
              GROUP BY CallID
         ) c
        INNER JOIN Numbers   n ON DATEDIFF(mi,c.StartTime,c.EndTime)+1>=n.Number
    WHERE DATEADD(mi,n.Number-1,c.StartTime)>=@StartRange AND DATEADD(mi,n.Number-1,c.StartTime)<@EndRange
    ORDER BY 1

这是输出:

TimeOfMatch             CallID                               StartTime               EndTime
----------------------- ------------------------------------ ----------------------- -----------------------
2008-07-10 16:12:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:13:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:13:00.000 444444444444444444444444444444444444 2008-07-10 16:13:00.000 2008-07-10 16:14:00.000
2008-07-10 16:13:00.000 555555555555555555555555555555555555 2008-07-10 16:13:00.000 2008-07-10 16:16:00.000
2008-07-10 16:14:00.000 555555555555555555555555555555555555 2008-07-10 16:13:00.000 2008-07-10 16:16:00.000
2008-07-10 16:14:00.000 444444444444444444444444444444444444 2008-07-10 16:13:00.000 2008-07-10 16:14:00.000
2008-07-10 16:14:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:15:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:15:00.000 555555555555555555555555555555555555 2008-07-10 16:13:00.000 2008-07-10 16:16:00.000
2008-07-10 16:15:00.000 222222222222222222222222222222222222 2008-07-10 16:15:00.000 2008-07-10 16:16:00.000

(10 row(s) affected)

TimeOfMatch             
----------------------- -----------
2008-07-10 16:12:00.000 1
2008-07-10 16:13:00.000 3
2008-07-10 16:14:00.000 3
2008-07-10 16:15:00.000 3

(4 row(s) affected)

您将需要一个关于 rowDateTime+CallId 的复合索引。但是，为了获得最佳性能，如果您创建了一个包含单个调用的开始和结束日期的新表(startdate+CallId 上的聚集索引)(可能在 EvId=0 插入开始日期时使用触发器，并且当 EvId =1 更新结束日期)，那么可以用这个新表删除派生表。