mysql - 查找并行时间的动作记录

Question

我有一个大约 700 万行的呼叫数据记录表 (CDR)，每行包含一个呼叫记录：唯一标识符、呼叫者号码、接收号码、answer_datetime 和 duration_in_seconds

我正在寻找一种有效的方法来查找由同一接收号码并行处理的呼叫。我尝试的任何查询都花费了太长时间

表结构：

    CREATE TABLE `cdrs` (
  `global_identifier` varchar(32) DEFAULT NULL,
  `caller_num` int(14) DEFAULT NULL,
  `receiving_num` int(14) DEFAULT NULL,
  `call_answer` datetime DEFAULT NULL,
  `call_duration` int(7) DEFAULT NULL,
  KEY `caller_num` (`caller_num`),
  KEY `receiving_num` (`receiving_num`),
  KEY `call_answer` (`call_answer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

我已经尝试过的查询：

SELECT
    DATE_FORMAT(call_answer,'%Y%m') AS Ym,
    b_num,
    COUNT(*) AS cnt,
    SUM(call_duration) / 60 AS c_dur
FROM
(

SELECT
    ycdr.*


FROM
    cdrs ycdr
    INNER JOIN cdrs ycdr2 ON
        ycdr2.receiving_num = ycdr.receiving_num
        AND ycdr2.caller_num != ycdr.caller_num
WHERE
    ycdr2.call_answer BETWEEN
    ycdr.call_answer AND ycdr.call_answer
    AND ycdr.call_answer >= '2015-01-01'
    AND ycdr.call_answer < '2015-01-05'
GROUP BY
    ycdr.global_identifier

) a
;

解释结果：

标签： mysqlsql