如何在SQL中将电话号码前缀与国家/地区匹配

提问者：小点点

如何在SQL中将电话号码前缀与国家/地区匹配

我正在尝试从数字列表中提取国家/地区代码前缀，并将它们与它们所属的地区相匹配。数据可能如下所示：

| id | phone_number   |
|----|----------------|
| 1  | +27000000000   |
| 2  | +16840000000   |
| 3  | +10000000000   |
| 4  | +27000000000   |

此处的国家代码为：

美属萨摩亚:1684年 < li >美国和加勒比海地区:1个 < li >南非:27人

期望的结果将是这样的：

| country                     | count |
|-----------------------------|-------|
| South Africa                | 2     |
| American Samoa              | 1     |
| United States and Caribbean | 1     |

有些困难是因为

国家前缀代码从1到4个数字不等，即使没有国家前缀，
电话号码长度因地而异。
我对这个数据库没有写权限，所以添加另一个列，虽然可能是最好的解决方案，但在这个用例中不起作用

这是我目前的解决方案：

SELECT 
CASE
    WHEN SUBSTRING(phone_number,1,5) = '+1684' THEN 'American Samoa'
    WHEN SUBSTRING(phone_number,1,5) = '+1264' THEN 'Anguilla'
    ...
    WHEN SUBSTRING(phone_number,1,5) = '+1599' THEN 'Saint Martin'
    WHEN SUBSTRING(phone_number,1,4) = '+355' THEN 'Albania'
    WHEN SUBSTRING(phone_number,1,4) = '+213' THEN 'Algeria'
    ...
    WHEN SUBSTRING(phone_number,1,4) = '+263' THEN 'Zimbabwe'
    WHEN SUBSTRING(phone_number,1,3) = '+93' THEN 'Afghanistan'
    WHEN SUBSTRING(phone_number,1,3) = '+54' THEN 'Argentina'
    ...
    WHEN SUBSTRING(phone_number,1,3) = '+58' THEN 'Venezuela'
    WHEN SUBSTRING(phone_number,1,3) = '+84' THEN 'Vietnam'
    WHEN SUBSTRING(phone_number,1,2) = '+1' THEN 'United States and Caribbean'
    WHEN SUBSTRING(phone_number,1,2) = '+7' THEN 'Kazakhstan, Russia'
    ELSE 'unknown'
END as country_name,
count(*)
FROM users
GROUP BY country_name
order by count desc

有 ~205 当 ...然后是案例。它似乎效率非常低，并且超时了。我认为这是因为它在每一行上运行模式匹配。这需要扩展到大约 10 数百万行

有没有更有效的方法可以做到这一点？

我正在使用postgreSQL 9.6.16

共1个答案

匿名用户

尽管阅读了整个表，索引在这里还是有帮助的。为了聚合每个国家代码的数据，DBMS必须按国家代码对所有行进行排序。排序是一项昂贵的操作，尤其是在大型数据集上。如果您有国家代码的索引，DBMS将在索引中找到已经预先排序的代码，并且可以避免对数据进行排序的工作。

您在一列中没有单独的国家/地区代码，但每个电话号码都以代码开头，因此您可以索引完整的电话号码：

create index idx on users (phone_number);

然后，您必须向DBMS表明您对字符串的开头感兴趣，因此它将考虑使用索引。在电话号码上调用类似＜code＞SUBSTRING＜/code＞的函数可能会使DBMS对此视而不见。改用LIKE。根据文件(https://www.postgresql.org/docs/9.3/indexes-types.html)，字符串上的索引可以与LIKE“something%”一起使用：

WHEN phone_number LIKE '+1684%' THEN 'American Samoa'

无法保证这会有所帮助，但我认为值得一试。这取决于优化器是否看到使用索引中预先排序的电话号码的优势。