python - MySQL聚合查询的聚合结果

Question

我有一个包含数百万条通话记录的表格，我需要对其运行汇总统计信息。该表如下所示:

+----------+----------+------------------+------------------+------+-------------+---------------------+---------------------+----------+-----------+-------------------------------------+-------------+
| id       | calltype | client_id | extension_number | flow | partyid     | start               | answer              | duration | disposion | sipcallid                                  | did         |
+----------+----------+------------------+------------------+------+-------------+---------------------+---------------------+----------+-----------+-------------------------------------+-------------+
| 35080566 | out      |       139 | 2222*050         | in   | 01123334455 | 2015-11-12 17:11:10 | 2015-11-12 17:11:10 |        4 | ANSWERED  | 20202911-3656337069-994458@sip.example.com | 01932855644 |
| 35077822 | out      |       139 | 2222*603         | in   | 02114455784 | 2015-11-12 16:37:41 | 2015-11-12 16:37:41 |       27 | ANSWERED  | 20138716-3656335055-417971@sip.example.com | 01123334455 |
| 35077821 | out      |       139 | 2222*603         | in   | 02114455784 | 2015-11-12 16:38:08 | 2015-11-12 16:38:08 |       80 | ANSWERED  | 20138716-3656335055-417971@sip.example.com | 01123334455 |
| 35077820 | local    |       139 | 2222*747         | in   | 2222*605    | 2015-11-12 16:38:09 | 2015-11-12 16:38:09 |       79 | ANSWERED  | 20138716-3656335055-417971@sip.example.com | 01123334455 |
| 35077346 | out      |       139 | 2222*603         | in   | 07841254789 | 2015-11-12 16:26:15 | 2015-11-12 16:26:15 |       27 | ANSWERED  | 20113840-3656334365-407195@sip.example.com | 01123334455 |
| 35077345 | out      |       139 | 2222*603         | in   | 07841254789 | 2015-11-12 16:26:42 | 2015-11-12 16:26:42 |      527 | ANSWERED  | 20113840-3656334365-407195@sip.example.com | 01123334455 |
| 35077344 | local    |       139 | 2222*746         | in   | 2222*609    | 2015-11-12 16:26:43 | 2015-11-12 16:26:43 |      526 | ANSWERED  | 20113840-3656334365-407195@sip.example.com | 01123334455 |
| 35065079 | out      |       139 | 2222*603         | in   | 02415785414 | 2015-11-12 14:37:21 | 2015-11-12 14:37:21 |       21 | ANSWERED  | 19848872-3656327834-411032@sip.example.com | 01123334455 |
| 35065078 | out      |       139 | 2222*603         | in   | 02415785414 | 2015-11-12 14:37:42 | 2015-11-12 14:37:42 |      776 | ANSWERED  | 19848872-3656327834-411032@sip.example.com | 01123334455 |
| 35065077 | local    |       139 | 2222*744         | in   | 2222*604    | 2015-11-12 14:37:42 | 2015-11-12 14:37:42 |      776 | ANSWERED  | 19848872-3656327834-411032@sip.example.com | 01123334455 |
+----------+----------+------------------+------------------+------+-------------+---------------------+---------------------+----------+-----------+-------------------------------------+-------------+

我需要每天运行一个查询来汇总数据。这应该很简单，但正如您从数据中看到的那样，一个公共(public)调用有多个行(例如，底部三行是同一调用的不同分支 - 这是显而易见的，因为它们都具有相同的 SIP 调用 ID)。调用开始(即响铃)的时间是 start，接听时间是 answer。

我需要生成以下统计数据:

每个 DID 的调用总数

没有。接听电话 < 5 秒

没有。接听的电话 > 10 秒

我有一个计算 MAX(answer)-MAX(start) 的查询，它给出了在相关时间段内回答的那些人的总数，但我不知道如何聚合那个输出给我每天的数字。

我的查询是这样的:

SELECT ch.did "Inbound DDI", 
  DATE(ch.start) Date, 
  IF((MAX(answer)-MIN(start)) < 5, 1 , 0),
  IF((MAX(answer)-MIN(start)) BETWEEN 5 AND 10, 1 , 0),
  IF((MAX(answer)-MIN(start)) > 10, 1 , 0)
  sipcallid
FROM 
  call_history ch
WHERE 
  flow = 'in' 
  AND ch.did <> ""
  AND ch.client_client_id = 1207 
  AND ch.duration > 0
  AND ch.disposition = "ANSWERED"
  AND DATE(start) = DATE_SUB(CURDATE(), INTERVAL 2 DAY) 
GROUP BY 
  ch.sipcallid;

有没有办法聚合它的输出，还是我必须编写脚本？例如，我可以看到我可以在 Python 中执行此操作。

Answer 1

根据链接的建议，您可以使用子查询来实现您的目标并避免使用 python 脚本。查询可能看起来像这样:

SELECT
  agg.Date,
  SUM(agg.short),
  SUM(agg.long)
FROM (
SELECT ch.did "Inbound DDI", 
  DATE(ch.start) Date, 
  IF((MAX(answer)-MIN(start)) < 5, 1 , 0) as 'short',
  IF((MAX(answer)-MIN(start)) BETWEEN 5 AND 10, 1 , 0) as 'avg',
  IF((MAX(answer)-MIN(start)) > 10, 1 , 0) as 'long',
  sipcallid
FROM 
  call_history ch
WHERE 
  flow = 'in' 
  AND ch.did <> ""
  AND ch.duration > 0
  AND ch.disposion = "ANSWERED"
GROUP BY 
  ch.sipcallid
) agg
GROUP BY agg.Date

您可以在 sqlfiddle 尝试此查询