例如，您可以立即向经理显示呼叫来自哪个城市，收紧当前的价目表和交货条件，显示呼叫者的卡，与他的最后一笔交易，特定的联系人等等……以及我们VLSI CRM可以做的许多有用的事情！
如何自己实现此功能？事实证明并不是那么困难。您实际上可以在膝盖上构建和测试工作模型-您只需要一捆Node.js和PostgreSQL。
通过数字确定区域
假设PBX向我们发送了一个传入的电话号码，该号码已经规范化并格式化为最多10位数字（我们将仅考虑俄罗斯境内的电话）。了解呼叫来源的最有效方法是什么？
收集电话代码
首先，我们需要一个与地区有关的俄罗斯电话代码数据库。为此，您可以使用官方资源-联邦通信局网站上编号计划的最新摘录。
但是查找还不够，您需要下载并提取这些数据。一个使用请求库的Node.js小脚本将帮助我们：
const async = require('async')
, request = require('request');
const fs = require('fs');
let queue = [
'ABC-3xx'
, 'ABC-4xx'
, 'ABC-8xx'
, 'DEF-9xx'
]
.map(key => (
{
base : 'https://rossvyaz.gov.ru'
, path : `/data/${key}.csv`
}
));
let ranges = [];
async.doWhilst(
cb => {
//
let task = queue.shift();
request(
{
url : task.base + task.path
, pool : false
}
, (err, res, body) => {
// CSV
body.split('\n').forEach(line => {
let tds = line.split(';');
let place = tds[5].split('|');
ranges.push([
tds[0]
, tds[1]
, tds[2]
, tds[4]
, place[place.length - 1]
, place[place.length - 2] && place[place.length - 2].startsWith('-') ? place[place.length - 2] : ''
, place.length > 1
? place[0].startsWith('-')
? ''
: place[0]
: ''
]);
});
return cb(err);
}
);
}
// ,
, cb => {
return cb(null, queue.length);
}
// -
, err => {
//
ranges.forEach(row => {
//
let ln = row[0].length + row[1].length - 10;
if (ln > 0) {
let sfx = row[0].slice(-ln);
if (row[1].startsWith(sfx) && row[2].startsWith(sfx)) {
row[1] = row[1].slice(ln);
row[2] = row[2].slice(ln);
}
}
//
let pfx;
for (let i = 1; i < row[1].length; i++) {
if (row[2].startsWith(row[1].slice(0, i))) {
pfx = row[1].slice(0, i);
}
else {
break;
}
}
if (pfx) {
row[0] = row[0] + pfx;
row[1] = row[1].slice(pfx.length);
row[2] = row[2].slice(pfx.length);
}
});
let sql = `
SET client_encoding = 'UTF-8';
CREATE TABLE phonecodes(
code
varchar
, numb
varchar
, nume
varchar
, oper
varchar
, region
varchar
, district
varchar
, city
varchar
);
COPY phonecodes FROM STDIN;
`;
// COPY-
let copy = ranges.map(row => row.join('\t')).join('\n') + '\n\\.\n';
fs.writeFileSync('phonecodes.sql', sql + copy);
}
);
现在，将其加载到我们的测试库中，您可以工作：
psql -f phonecodes.sql -U postgres tst如果一切正常，将近37.8万个范围将被加载到我们的表中：
SET
CREATE TABLE
COPY 377937请注意，在我们的示例中，范围的代码和边界号都由字符串表示。是的，可以将它们转换为integer/bigint，但是我们暂时不会这样做。此外，传入的电话号码并不总是仅由数字组成-例如，某些公用电话可以用“数字A”报告其电话号码。
“他们正在寻找消防员，警察正在寻找……”
让我们先尝试一个简单的查询：
WITH src AS (
SELECT '4852262000' num --
)
SELECT
*
FROM
src
, phonecodes
WHERE
num LIKE (code || '%') AND --
num BETWEEN (code || numb) AND (code || nume) --
LIMIT 1;
[请看explorer.tensor.ru]减去了
将近70,000行（幸运的是，不是全部380行！），几乎10MB的数据被铲除了……效率不高，但结果达到了：
num | code | numb | nume | oper | region | district | city
-----------------------------------------------------------------------------------
4852262000 | 485226 | 0000 | 9999 | | . | | 但是，让我们以某种方式摆脱它
Seq Scan！要做到这一点，我们只需要一个指数，这将有助于通过搜索LIKE，对不对？..
唉，没有。如果我们需要搜索
column LIKE (val || '%')，那么带有varchar_pattern_ops的前缀索引将对我们有所帮助，但与之相反val LIKE (column || '%')。我们得到的情况与我在“对PostgreSQL日志中的错误进行分类”一文中所描述的情况非常接近。
我们使用应用领域的知识
接近，但是幸运的是，它仍然简单得多-我们的数据是固定的，而且相对较少。此外，记录通过代码分配得相当稀疏：
SELECT -- -
ranges
, count(*)
FROM
(
SELECT --
code
, count(*) ranges
FROM
phonecodes
GROUP BY
1
) T
GROUP BY
1
ORDER BY
1 DESC;只有大约一百个代码具有10个范围，而将近四分之一的代码具有一个范围：
ranges | count
--------------
10 | 121
9 | 577
8 | 1705
7 | 3556
6 | 6667
5 | 10496
4 | 12491
3 | 20283
2 | 22627
1 | 84453因此，让我们现在仅索引代码。并且由于我们需要一起使用同一代码的所有范围，因此
CLUSTER让我们在的帮助下安排表格，使记录在物理上彼此相邻：
CREATE INDEX ON phonecodes(code);
CLUSTER phonecodes USING phonecodes_code_idx;现在，让我们记住我们的电话号码完全由（全部！）10位数字组成，其中我们需要隔离前缀代码。也就是说，通过不超过10个选项的简单枚举就可以轻松解决我们的任务：
WITH RECURSIVE src AS (
SELECT '4852262000' num
)
, T AS (
SELECT
num pfx -- ""
, NULL::phonecodes pc
FROM
src
UNION ALL
SELECT
substr(pfx, 1, length(pfx) - 1) -- ""
, (
SELECT
X
FROM
phonecodes X
WHERE
code = T.pfx AND --
(TABLE src) BETWEEN (code || numb) AND (code || nume) --
LIMIT 1
) pc
FROM
T
WHERE
pc IS NOT DISTINCT FROM NULL AND -- ,
length(pfx) > 2 -- ...
)
SELECT
(pc).* -- ""
FROM
T
WHERE
pc IS DISTINCT FROM NULL;
[看explain.tensor.ru]
我们只用了5次索引调用就找到了我们想要的代码。绝对值的增加似乎微不足道，但是相对于天真的选择，我们的负载减少了150倍！如果您的系统每小时必须处理数以万计的此类请求，那么节省的费用将非常可观！
如果将所有代码预先简化为“ 3到5位数字”的经典形式，则可以在索引上进行更少的迭代。但是，每个代码中的范围数量将增加，对其进行过滤会增加问题。
int8range + GiST
正如评论中正确指出的 米克西尔，因为我们都有所有“代码+范围”对，并且输入的号码严格具有10位数字的相同维，所以可以将问题简化为在数值之间进行间隔搜索。
为此，我们将创建一个索引，将我们的记录视为
int8range：
CREATE INDEX ON phonecodes USING gist(
int8range(
(code || numb)::bigint --
, (code || nume)::bigint --
, '[]' --
)
);之后，我们可以在请求中使用它：
WITH src AS (
SELECT '4852262000'::bigint num
)
SELECT
*
FROM
phonecodes
WHERE
int8range((code || numb)::bigint, (code || nume)::bigint, '[]') @> ( --
SELECT
int8range(num, num, '[]') -- ""
FROM
src
)
LIMIT 1;
[看explain.tensor.ru]
非重叠间隔+ btree
首先，让我们确保我们的数字范围没有真正重叠：
SELECT
*
FROM
phonecodes X
, phonecodes Y
WHERE
int8range((X.code || X.numb)::bigint, (X.code || X.nume)::bigint, '[]') &&
int8range((Y.code || Y.numb)::bigint, (Y.code || Y.nume)::bigint, '[]') AND
X.ctid <> Y.ctid;如果您什么都没有，那么一切都很好，您可以应用以下优化：该数字只能包含在范围内，直到最接近的末尾（或开始）。
为了找到最接近的“开始”，我们需要一个常规的btree索引：
CREATE INDEX ON phonecodes((code || numb));WITH src AS (
SELECT '4852262000' num
)
SELECT
*
FROM
src
, LATERAL (
SELECT
*
FROM
( --
SELECT
*
FROM
phonecodes
WHERE
(code || numb) <= src.num
ORDER BY
(code || numb) DESC
LIMIT 1
) T
WHERE
src.num BETWEEN (code || numb) AND (code || nume) --
) T;
尽管其明显的简单，此选项给出比前一个更差的性能：
[在explain.tensor.ru样子]
我们通过编号识别客户
现在，让我们想象一下，我们已经有了一个包含客户的表，其中写入了“清理过的”电话号码-删除了所有方括号，连字符等。
但这很麻烦，不是所有人都具有城市代码-经理们太懒惰而无法得分，或者PBX的配置使其发送的不是完整的，而是“城市内的”数字……然后如何找到客户-毕竟，完全匹配搜索将不再起作用？
PBX提供完整号码
在这种情况下，我们将使用相同的“穷举”算法。只有我们才能从数字的结尾而不是数字的开头“捏住”数字。
如果客户卡中的号码显示完整，我们将在第一次迭代时偶然发现它。如果不完全-当我们“切断”一些适当的代码时。
当然，我们需要通过其他详细信息（地址，TIN，...）进行某种交叉检查，以免出现以下情况：我们从传入号码中“截断”了莫斯科代码，并从其余的7位数字中找到了来自圣彼得堡的客户。彼得斯堡。
PBX给出一个“城市”号码
: 262000
: 4852262000这里的情况更有趣。我们无法将每个可能的代码“递增”到一个短数字并尝试搜索-它们太多了。让我们从另一面看情况-从字面上看：
reverse(262000) -> 000262
reverse(4852262000) -> 0002622584事实证明，如果用数字扩展行，则该任务将变成常规的前缀搜索，使用带有varchar_pattern_ops和
LIKE！的索引可以轻松解决该问题。
CREATE INDEX ON client(reverse(phone) varchar_pattern_ops);SELECT
*
FROM
client
WHERE
reverse(phone) LIKE (reverse($1) || '%');然后，再次，我们再次检查其他信息-PBX从哪个区域向我们发送了号码，客户属于哪个区域。