快速实现大量数据匹配来电号码归属

问题分析

手机号码又主要分两种，018179700104和18179700104，即带0开头和不带0开头的。

邮编又分两种三位数的邮编和四位的邮编，如北京010，深圳0755.

结果一个来电号码要判断是否满足四种情况中的一种。Left join的haul就会产生巨大的数据量匹配，导致非常的缓慢。

：由于数据库数据量大，且来电归属地的表数据量庞大，还需要同时匹配手机号码和邮编。

首先我们来看一下，号码归属地的表

 

以上三列分别是判断归属地的手机号码，区号和对应的城市。为了实现同时匹配邮编和电话号码。我们需要将邮编和手机号码，合并成一列，并将对应的城市生成一列。

Postgresql的语句如下：

SELECT DISTINCT  phone,city from rd_attribution UNION ALL  SELECT DISTINCT city_code,city from rd_attribution

我们发现执行这条sql语句非常慢，需要6秒左右，如果我们用大量数据去和这个临时表left就会造成sql语句执行非常非常的缓慢。

所以我们可以创建一张表，专门用于存储这两个字段。这样我们就可以大大加快sql执行速度。

假设表名为city1

Sql语句如下：insert into city1(phone,city)  SELECT DISTINCT  phone,city from rd_attribution UNION ALL  SELECT DISTINCT city_code,city from rd_attribution

生成表如下：

接下来我们就考虑，如何实现连接这张表时字段一一对应，而不是判断一个字段可能满足手机号码，邮编其中的一种情况。

首先我们知道手机号码是11位数据，且是1开头。

当前面加0时，就是12位。

如果是座机的话，现在扩容后是8位。

如果区号是三位数，如010，加上八位座机号码，便是11位

如果区号是4位数，如0755，加上八位座机号码，便是12位

以上便有四种情况

	电话号码长度	分析数据
0开头	11位	座机，且邮编3位		substr(n.calling_pty,1,3)
	12位	1开头	手机号码	substr(n.calling_pty,2,7)
		非1开头	座机，4位邮编	substr(n.calling_pty,1,4)
1开头	11位	正常手机号码		substr(n.calling_pty,1,3)

以上判断是为了让我们截取来电号码，去跟归属地表中的数据去判断，归属地。

我们在数据库中用case when来实现。以下是完整的查询归属地的语句。

select DISTINCT sd.*,s.city from  (
select DISTINCT t.num1,n.calling_pty,

      case when substr(n.calling_pty,1,1)='1' and length(n.calling_pty)=11 then substr(n.calling_pty,1,7)

      when substr(n.calling_pty,1,1)='0' and length(n.calling_pty)=11 then  substr(n.calling_pty,1,3)

            when substr(n.calling_pty,1,2)='01' and length(n.calling_pty)=12 then substr(n.calling_pty,2,7)

            when substr(n.calling_pty,1,1)='0' and length(n.calling_pty)=12 and substr(n.calling_pty,2,1)!='1'     then substr(n.calling_pty,1,4)

             else 'null'

            end  phones

from call_rec t left join call_rec n on t.callid=n.callid

where

t.num1>'2017-11-01 00:00:00'

and length(n.calling_pty)>7

)  sd

left join city1 s

on sd.phones=s.phone

至此完美实现可快速匹配归属地。重点在对来电号码进行分析，处理。