SQL实现通话表号码在from_number与to_number字段的方向标记方法

需求说明 #

在电话网络的通话记录表中存在主叫字段from_number、被叫字段to_number，需要实现以下需求：

• 获取两个字段中所有去重号码的列表
• 为每个号码标记direction属性：
  • 仅出现在from_number字段标记为from
  • 仅出现在to_number字段标记为to
  • 两个字段都出现过标记为both
• 标记结果支持按direction过滤筛选

已有表结构与测试数据 #

create table calls 
(
   call_date date,
   from_number varchar(16),
   to_number varchar(16)
);

INSERT calls
VALUES ('2020-07-03','619876544',  '022445545'),
       ('2020-07-03','61123456',  '642445544'), 
       ('2020-07-03','03123456',   '61333333'),
       ('2020-07-03','65123456',   '619876543'),
       ('2020-07-04','642445545',  '61123456'),
       ('2020-07-04','61333333',  '632445555'),
       ('2020-07-04','642445545',  '049876543'),
       ('2020-07-03','649876543',  '61333333'),
       ('2020-07-04','612445555',  '022445545');

实现方案 #

方案1：获取去重号码+方向标记列表 #

该方案返回所有去重号码对应的方向属性，适合号码维度的统计与过滤：

WITH from_nums AS (
    -- 提取所有去重主叫号码
    SELECT DISTINCT from_number AS num FROM calls
),
to_nums AS (
    -- 提取所有去重被叫号码
    SELECT DISTINCT to_number AS num FROM calls
)
SELECT 
    COALESCE(f.num, t.num) AS phone_number,
    CASE 
        WHEN f.num IS NOT NULL AND t.num IS NOT NULL THEN 'both'
        WHEN f.num IS NOT NULL THEN 'from'
        WHEN t.num IS NOT NULL THEN 'to'
    END AS direction
FROM from_nums f
FULL OUTER JOIN to_nums t ON f.num = t.num
-- 如需过滤直接加WHERE条件，例如筛选both的号码：WHERE direction = 'both'
ORDER BY direction;

方案2：为原通话记录添加方向标记 #

该方案保留原通话记录的所有字段，新增direction列，和你提供的期望输出结构一致：

SELECT 
    call_date,
    from_number,
    to_number,
    CASE 
        -- 主叫在被叫列出现过、被叫也在主叫列出现过
        WHEN EXISTS (SELECT 1 FROM calls c2 WHERE c2.to_number = c1.from_number) 
             AND EXISTS (SELECT 1 FROM calls c2 WHERE c2.from_number = c1.to_number) THEN 'both'
        -- 仅主叫在被叫列出现过
        WHEN EXISTS (SELECT 1 FROM calls c2 WHERE c2.to_number = c1.from_number) THEN 'from'
        -- 仅被叫在主叫列出现过
        WHEN EXISTS (SELECT 1 FROM calls c2 WHERE c2.from_number = c1.to_number) THEN 'to'
        ELSE 'none'
    END AS direction
FROM calls c1;

原写法问题说明 #

你之前的实现仅做了一次左连接，只能判断被叫号码是否出现在主叫列，无法覆盖主叫号码是否出现在被叫列的判断逻辑，所以没法准确区分from和both的场景，换成上述的CASE判断+EXISTS子查询或者全外连接的方案即可解决问题。

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