All geek questions in one place

Analyzing a Time-Dependent Event Log from SQL

I have an event log in a SQL Server database. In fact, it records when the call was made and when the call ended in the call center (like two different records), as well as several other details. I am trying to understand how many telephone lines are in use at any given time with this data. I can’t think of a good way to get the SQL query to determine this for me, although that would be ideal (if it had not sacrificed a lot of speed).

My first thought was that the program requested the start and end events for each call, determining the duration of the call. Then I could go through each unit of time, keeping track of the number of calls at any given time. Is there a way to do this in SQL instead of using a linear method in C # or something similar?

Edit: There is a unique identifier for calls. Session id if you want. In addition, the start and end events are two different records - not one record. I think this makes it a little complicated. In addition, this table contains over 15 million records.

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
+3
source share
4 answers

, "", :

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 ), .

+1

, , . CTE : , , , . , . CTE , , .

declare @temp table (
    EvId int not null
    , CallId uniqueidentifier not null
    , DateTime Datetime not null);

 declare @starttime datetime
    , @endtime datetime;

 select @starttime = '7/10/2008 1:33:14 PM';
 select @endtime = '7/10/2008 1:43:14 PM';

 -- These are all the calls
 -- that started before the start time
 with started_call as (
 select * from call_log 
    where DateTime < @starttime 
    and EvId = 0)
-- These are all the calls 
-- that ended    before the start time
 , ended_call as (
 select * from call_log 
    where DateTime < @starttime 
    and EvId = 1)
-- These are all the call ids 
-- that were ongoing at the start time  
 , existing_calls as (
 select CallId from started_call
 except
 select CallId from ended_call)
-- These are all the call events logged
-- for calls that were were ongoing at the start time   
 , existing_details as (
 select l.* 
    from call_log l
    join existing_calls e on e.CallId = l.CallId
    where l.DateTime < @starttime)
-- these are events that occured
-- between start time and endtime   
, new_events as (
    select * from call_log
    where DateTime between @starttime and @endtime)
-- and these are all the events that are of interest
, all_events as (
    select * from existing_details
    union all
    select * from new_events)
-- put all the interesting events into a @temp table
-- unfortunately QO cannot spool this for us
-- so we better do it isntead   
insert into @temp (EvId, CallId, DateTime)
    select EvId, CallId, DateTime  from all_events;

-- Extract events, along with the count
-- at the time of the event
select e.*,(
        select sum(case
            when EvId = 0 then 1 -- Start call
            when EvId = 1 then -1 -- end call
            else 0 end) -- Other events 
        from @temp se
        where se.DateTime < e.DateTime) as cnt
from @temp e
where DateTime between @starttime and @endtime
order by DateTime;

, , . , . 1 . 10- 1,1 (628 @temp, 505 ) 1,5 . , , (DatTime >= - ).

@temp , .

:

EvId    CallId                                  DateTime                cnt
1   401D9E00-040C-4B0E-8864-C66B72CF47AA    2008-07-10 13:33:16.000 23
10  401D9E00-040C-4B0E-8864-C66B72CF47AA    2008-07-10 13:33:16.000 23
1   8BF7AF50-B32C-464A-AF01-FDB653F0517D    2008-07-10 13:33:18.000 22
10  8BF7AF50-B32C-464A-AF01-FDB653F0517D    2008-07-10 13:33:18.000 22
0   CB523E24-5CE2-4E36-9D6C-4AE7BCEB1F53    2008-07-10 13:33:19.000 21
1   4A54EEB6-A899-4167-9D5C-2CE1BC838FFB    2008-07-10 13:33:20.000 22

. , .

create table call_log (id int identity(1,1) not null
    , EvId int not null
    , CallId uniqueidentifier not null
    , DateTime Datetime not null);
create clustered index cdx_call_log on call_log(EvId, DateTime);
create nonclustered index idx_call_log_call_id on call_log(CallId);
go

 set nocount on;
 declare @i int, @date datetime, @callId uniqueidentifier;
 select @i = 0, @date = '7/10/2008 12:33:14 PM';
 begin transaction
 while @i < 1000000
 begin
    declare @duration int,
        @delay int;
    select @duration = rand()*180,
        @delay = rand() * 10;
    select @date = dateadd(second, @delay, @date)
        , @callId = newid();

    insert into call_log (EvId, CallId, DateTime)
    values  (0, @callId, @date)
        , (10, @callId, dateadd(second, @duration, @date))
        , (1, @callId, dateadd(second, @duration, @date));
    select @i = @i + 1;
    if (0 = @i%100)
    begin
        commit;
        begin tran;
    end
 end
 commit
 go
+1

:

DECLARE @tblCalls TABLE(ActionEffect int, ActionTime datetime)

INSERT INTO @tblCalls(ActionEffect, ActionTime)
    SELECT 1, [DateTime]
    FROM tblCallRecords
    WHERE EviD = 0

INSERT INTO @tblCalls(ActionEffect, ActionTime)
    SELECT -1, [DateTime]
    FROM tblCallRecords
    WHERE EvID > 0

( , EvID, 0, ?)

, , :

SELECT Sum(ActionEffect)
FROM @tblCalls
WHERE ActionTime < @GivenMoment

15 .

, , , , - :

SELECT a.ActionTime, Sum(b.ActionEffect) AS OpenCalls
FROM @tblCalls AS a
LEFT JOIN @tblCalls AS b ON a.ActionTime > b.ActionTime
GROUP BY a.ActionTime

. , , , , .

0

, . , , .

1) DateTime UniqueID

2), (, 24 48 ) , .

, .

, , -

 Select CallId, 
     Min(DateTime) as StartOfCall , Max(DateTime) as EndofCall        
 from Call_log
 where
    (evid = 0 or evid=1)
 and DateTime between @ExtendedStartPeriod and @ExtendedEndPeriod

ExtendedStartPeriod ExtendedEndPeriod - ( , 48 )

, , ,

Select UniqueID from (...) table1
where StartOfCall <= @EndDate or EndOfCall >= @StartDate

( ) , .

Select DateTime, 
  CallChange = Case 
  When Evid = 0 then 1
  When Evid = 1 then -1
  else 0
 end
 from call_log 
 where 
  unique_id in ( ... )  
  and (evid = 0 or evid=1)
 and DateTime between @ExtendedStartPeriod and @ExtendedEndPeriod

This should give you a list of events and increase or decrease the number of calls. In your example, something like

         7/9/2008 8:12:56 PM  1
         7/9/2008 8:13:07 PM -1
        7/10/2008 4:33:10 PM  1
        7/10/2008 4:33:13 PM -1
        7/10/2008 4:33:13 PM  1
        7/10/2008 4:33:15 PM -1
        7/10/2008 4:33:15 PM  1
        7/10/2008 4:33:17 PM -1

If the number of calls per second is extremely large, this can help to group per minute to reduce the size of the data returned from sql.

This is even possible by completing an additional request.

Select 
   Count(CallChange) ,
   DatePart("yyyy", DateTime) , 
   DatePart("mm", DateTime),
   DatePart("dd", DateTime),
   DatePart("hh", DateTime),
   DatePart("mi", DateTime)
   DatePart("ss", DateTime)
From
   ( ...) 

  Group By
     DatePart("yyyy", DateTime) , 
     DatePart("mm", DateTime),
     DatePart("dd", DateTime),
     DatePart("hh", DateTime),
     DatePart("mi", DateTime)
     DatePart("ss", DateTime)

As for how I can go with Sql, maybe someone can take it further, otherwise I think that it will be necessary to do a little C # in order to maintain the number of transactions per period.

0
source

Source: https://habr.com/ru/post/1715370/

More articles:


All Articles