sql - 如何从SQL中的电话号码将电话号码前缀与国家/地区匹配

Question

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

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

这里的国家代码是:

美属萨摩亚:+1684

美国和加勒比:+1

南非:+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 WHEN ... THEN案件。它似乎非常低效并且超时。我认为这是因为它在每一行上运行模式匹配。这将需要扩展到大约 10 百万行

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

我正在使用 postgreSQL 9.6.16

Answer 1

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

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

create index idx on users (phone_number);

然后你必须让 DBMS 清楚你对字符串的开头感兴趣，所以它会考虑使用索引。调用类似 SUBSTRING 的函数在电话号码上很可能使 DBMS 对此视而不见。使用 LIKE反而。根据文档( https://www.postgresql.org/docs/9.3/indexes-types.html )，字符串索引可以与 LIKE 'something%' 一起使用:

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

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