MySQL查询 - 峰值并发呼叫CDR数据

Question

MySQL查询 - 峰值并发呼叫CDR数据

4

你好，我需要帮助计算从MySQL存储的CDR日期中一天内有多少个高峰并发呼叫。

数据集如下：

INSERT INTO `cdr` (`calldate`, `clid`, `src`, `dst`, `dcontext`, `channel`, `dstchannel`, 
                   `lastapp`, `lastdata`, `duration`, `billsec`, `disposition`, `amaflags`, 
                   `accountcode`, `uniqueid`, `userfield`) VALUES

我可以使用以下查询来计算每个唯一日期的条目数。

SELECT COUNT(1) AS entries, date(calldate) AS DATE
FROM  `cdr` 
GROUP BY DATE (calldate)
LIMIT 0 , 1000

然而，这只告诉我最大并发通话的理论峰值，而不是实际峰值。

要获得实际峰值，我们需要首先知道每个通话的开始和结束日期和时间。目前，开始日期和时间记录在(calldate)字段中，通话持续时间以秒为单位记录在(duration)字段中。通过将(duration)字段存储的秒数与(calldate)字段相加，我们可以计算出完成时间。

现在，我们知道了开始和结束时间，我们需要计算这些时间是否重叠，并重叠了多少次。这种级别的SQL查询超出了我的知识范围。

简而言之，我正在尝试使用MySQL查询计算从MySQL存储的CDR数据中最大同时呼叫量是多少。非常感谢您的帮助。

样本数据：

    calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield
08/11/2013 17:02,x,x,1000,default,x,x,x,x,26,26,ANSWERED,3,x,1383930162,x
08/11/2013 17:02,x,x,1000,default,x,x,x,x,24,24,ANSWERED,3,x,1383930164,x
08/11/2013 17:02,x,x,1000,default,x,x,x,x,45,45,ANSWERED,3,x,1383930146,x
08/11/2013 17:10,x,x,1000,default,x,x,x,x,2,2,ANSWERED,3,x,1383930649,x
08/11/2013 17:22,x,x,1000,default,x,x,x,x,4,4,ANSWERED,3,x,1383931380,x
08/11/2013 17:23,x,x,1000,default,x,x,x,x,5,5,ANSWERED,3,x,1383931388,x
08/11/2013 17:23,x,x,1000,default,x,x,x,x,9,9,ANSWERED,3,x,1383931395,x
10/11/2013 09:28,x,x,1000,default,x,x,x,x,7,7,ANSWERED,3,x,1384075689,x
10/11/2013 10:09,x,x,1000,default,x,x,x,x,57,57,ANSWERED,3,x,1384078181,x
10/11/2013 10:09,x,x,1000,default,x,x,x,x,81,81,ANSWERED,3,x,1384078164,x
10/11/2013 10:09,x,x,1000,default,x,x,x,x,102,102,ANSWERED,3,x,1384078143,x
11/11/2013 10:23,x,x,1000,default,x,x,x,x,3,3,ANSWERED,3,x,1384165439,x
11/11/2013 17:46,x,x,1000,default,x,x,x,x,30,30,ANSWERED,3,x,1384191975,x
11/11/2013 17:46,x,x,1000,default,x,x,x,x,30,30,ANSWERED,3,x,1384191976,x
11/11/2013 17:45,x,x,1000,default,x,x,x,x,50,50,ANSWERED,3,x,1384191956,x
11/11/2013 17:55,x,x,1000,default,x,x,x,x,9,9,ANSWERED,3,x,1384192544,x
13/11/2013 10:59,x,x,1000,default,x,x,x,x,209,209,ANSWERED,3,x,1384340382,x
13/11/2013 10:59,x,x,1000,default,x,x,x,x,230,230,ANSWERED,3,x,1384340361,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1342,1342,ANSWERED,3,x,1384340963,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1312,1312,ANSWERED,3,x,1384341009,x
13/11/2013 11:08,x,x,1000,default,x,x,x,x,1441,1441,ANSWERED,3,x,1384340891,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1288,1288,ANSWERED,3,x,1384341059,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1306,1306,ANSWERED,3,x,1384341050,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1378,1378,ANSWERED,3,x,1384340990,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1419,1419,ANSWERED,3,x,1384340953,x
13/11/2013 11:06,x,x,1000,default,x,x,x,x,1558,1558,ANSWERED,3,x,1384340815,x
13/11/2013 11:12,x,x,1000,default,x,x,x,x,1254,1254,ANSWERED,3,x,1384341121,x
13/11/2013 11:10,x,x,1000,default,x,x,x,x,1330,1330,ANSWERED,3,x,1384341045,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1431,1431,ANSWERED,3,x,1384340947,x
13/11/2013 11:11,x,x,1000,default,x,x,x,x,1302,1302,ANSWERED,3,x,1384341076,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1383,1383,ANSWERED,3,x,1384340995,x
13/11/2013 11:08,x,x,1000,default,x,x,x,x,1444,1444,ANSWERED,3,x,1384340937,x
13/11/2013 11:07,x,x,1000,default,x,x,x,x,1531,1531,ANSWERED,3,x,1384340850,x
13/11/2013 11:09,x,x,1000,default,x,x,x,x,1418,1418,ANSWERED,3,x,1384340963,x
13/11/2013 12:02,x,x,1000,default,x,x,x,x,10,10,ANSWERED,3,x,1384344169,x
13/11/2013 12:01,x,x,1000,default,x,x,x,x,807,807,ANSWERED,3,x,1384344072,x
13/11/2013 12:03,x,x,1000,default,x,x,x,x,680,680,ANSWERED,3,x,1384344200,x
13/11/2013 12:01,x,x,1000,default,x,x,x,x,793,793,ANSWERED,3,x,1384344090,x
13/11/2013 12:01,x,x,1000,default,x,x,x,x,772,772,ANSWERED,3,x,1384344111,x

- user3246938

如果您提供示例数据，这总是有帮助的。哪些字段具有通话的开始和结束时间？ - Gordon Linoff

开始时间为（calldate），结束日期需要通过添加以秒为单位存储在字段（duration）中的持续时间来计算。 - user3246938

3个回答

1

尝试为每次通话开始添加+1，为每次结束减去-1，然后只需获取+1/-1列的累积和
***如有需要，请转换通话日期格式或使用您的格式：

set @from:='2015-02-01';
set @to:='2015-03-01';
set @csum:=0;
SELECT DT,CallCount, (@csum := @csum + CallCount) as cumulative_sum
FROM 
(select calldate AS DT, 1 AS CallCount
 from cdr
 where calldate between @from and @to

 union all

 select ADDDATE(calldate,INTERVAL duration SECOND) AS DT, -1 AS CallCount
 from cdr 
 where calldate between @from and @to
  ) Calls
ORDER BY 1 asc;

- mirkobrankovic

0

使用分组，让开关将总呼叫次数+1写入新的CDR值，以便了解最后一个呼叫进入系统时有多少个呼叫已经上线。

- Randy

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Eugen Rieck · Accepted Answer

这个应该能用，但会严重影响性能！

SELECT
  calldate,
  MAX(concurrent)+1 AS peakcount
FROM (
    SELECT
      DATE(a.calldate) as calldate,
      COUNT(b.uniqueid) AS concurrent
    FROM cdr AS a, cdr AS b
    WHERE  
      a.calldate BETWEEN '2013-11-08 00:00:00' AND '2013-11-13 23:59:59'
      AND (
        (a.calldate<=b.calldate AND (UNIX_TIMESTAMP(a.calldate)+a.duration)>=UNIX_TIMESTAMP(b.calldate))
        OR (b.calldate<=a.calldate AND (UNIX_TIMESTAMP(b.calldate)+b.duration)>=UNIX_TIMESTAMP(a.calldate))
      )
      AND a.uniqueid>b.uniqueid
    GROUP BY a.uniqueid
  ) AS baseview
GROUP BY calldate

为您的示例数据提供正确的答案。以下是它的工作原理:

最内层(a.calldate<=b.calldate AND (UNIX_TIMESTAMP(a.calldate)+a.duration)>=UNIX_TIMESTAMP(b.calldate)...)计算了交集: 如果一个呼叫的起始点在另一个呼叫的起始点及之后，并且在该呼叫的终止点或之前，则两个呼叫重叠
自连接通话表找到所有重叠部分，
但有一个问题: 自连接在第1行和第2行之间找到了一个重叠，但在第2行和第1行之间找到了另一个重叠。如果超过两个呼叫重叠，则很难解决这个问题。
现在，由于您的数据包含一个数值唯一标识符，我们可以使用它来过滤那些重复，三倍等等的记录，这是通过AND a.uniqueid>b.uniqueid选择器和GROUP BY a.uniqueid完成的，它使得只有具有最小uniqueid的呼叫看到所有并发呼叫，其他呼叫看到较少数量的并发呼叫。
在外部查询中对此使用MAX()将其过滤掉。
我们需要+1来获得峰值呼叫数量: 有2个并发呼叫的呼叫表示3个峰值计数。

SQLfiddle