如何使用SQL从通话记录表中识别未接来电与回电？

核心逻辑 #

未接来电判定规则为Start = End，对应的回电需要满足三个条件：

• 主叫/被叫号码与未接来电完全反转（未接的主叫是回电的被叫，未接的被叫是回电的主叫）
• 回电发生在未接来电之后
• 回电为已接通状态（Start != End）

分步实现 #

1. 筛选未接来电数据集

先从原表提取所有未接来电，给字段起别名方便后续关联：

SELECT 
    `From` AS missed_caller,
    `To` AS missed_callee,
    Start AS missed_time
FROM calls
WHERE Start = End

2. 关联原表匹配对应回电

通过LEFT JOIN关联原表，可同时保留有回电和无回电的未接记录；若仅需显示有回电的记录，替换为INNER JOIN即可：

SELECT
    -- 未接来电信息
    m.missed_caller AS 未接来电主叫,
    m.missed_callee AS 未接来电被叫,
    m.missed_time AS 未接时间,
    -- 回电信息
    c.`From` AS 回电主叫,
    c.`To` AS 回电被叫,
    c.Start AS 回电开始时间,
    c.End AS 回电结束时间
FROM (
    -- 子查询获取未接来电
    SELECT 
        `From` AS missed_caller,
        `To` AS missed_callee,
        Start AS missed_time
    FROM calls
    WHERE Start = End
) m
LEFT JOIN calls c
    ON c.`From` = m.missed_callee
    AND c.`To` = m.missed_caller
    AND c.Start > m.missed_time
    AND c.Start != c.End

3. 处理多回电场景（可选）

若同一个未接来电存在多条回电，可通过窗口函数ROW_NUMBER()筛选出最早的一条回电：

WITH missed_calls AS (
    SELECT 
        `From` AS missed_caller,
        `To` AS missed_callee,
        Start AS missed_time
    FROM calls
    WHERE Start = End
),
call_backs AS (
    SELECT
        m.*,
        c.`From` AS cb_from,
        c.`To` AS cb_to,
        c.Start AS cb_start,
        c.End AS cb_end,
        ROW_NUMBER() OVER (PARTITION BY m.missed_caller, m.missed_callee ORDER BY c.Start) AS rn
    FROM missed_calls m
    LEFT JOIN calls c
        ON c.`From` = m.missed_callee
        AND c.`To` = m.missed_caller
        AND c.Start > m.missed_time
        AND c.Start != c.End
)
SELECT
    missed_caller AS 未接来电主叫,
    missed_callee AS 未接来电被叫,
    missed_time AS 未接时间,
    cb_from AS 回电主叫,
    cb_to AS 回电被叫,
    cb_start AS 回电开始时间,
    cb_end AS 回电结束时间
FROM call_backs
WHERE rn = 1 OR rn IS NULL

示例数据验证 #

用你提供的示例数据测试：

• 第一条未接来电（9901234567→8854320145，10:00）会匹配到后续的回电记录（8854320145→9901234567，10:05-10:07）
• 7609123987→8854320145的未接来电无对应回电，结果中回电字段会显示为NULL

内容的提问来源于stack exchange，提问作者JG1