Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро — тогда появляется масса возможностей.
Например, можно менеджеру сразу показать из какого города идет звонок, подтянуть актуальный прайс и условия доставки, вывести карточку звонящего клиента, последние сделки с ним, конкретное контактное лицо,… — да много чего полезного, как это умеет наш СБИС CRM!
А как этот функционал реализовать самостоятельно? Оказывается, не так уж сложно. Собрать и опробовать работающую модель можно, буквально, «на коленке» — нужна только связка из Node.js и PostgreSQL.
Давайте предположим, что АТС присылает нам уже нормализованный и отформатированный до 10 цифр (будем рассматривать только звонки внутри России) входящий телефонный номер. Как наиболее эффективно понять, откуда пришел звонок?
Сначала нам понадобится база телефонных кодов России в привязке к регионам. Для этого можно воспользоваться официальным источником — актуальной выпиской из плана нумерации на сайте Федерального агентства связи.
Но найти — мало, надо эти данные скачать и извлечь. В этом нам поможет небольшой скрипт для Node.js, использующий библиотеку request:
Теперь загрузим его в нашу тестовую базу, и можно работать:
Если все сработало как надо, в нашу таблицу будет загружено почти 378 тысяч диапазонов:
Сначала попробуем наивный запрос:
[посмотреть на explain.tensor.ru]
Вычитали почти 70 тысяч строк (и это еще повезло, что не все 380!), почти 10MB данных перелопатили… не слишком эффективно, но результат достигнут:
Но давайте как-то избавимся от
Увы, нет. Если нам надо искать
Близкую, но, к счастью, все-таки существенно проще — данные у нас фиксированы и их относительно немного. Причем по кодам записи распределены достаточно разреженно:
Только лишь около сотни кодов имеют по 10 диапазонов, а почти четверть — вообще ровно один:
Поэтому давайте проиндексируем пока только код. А раз все диапазоны одного кода нам понадобятся все вместе — упорядочим нашу таблицу с помощью
А теперь вспомним, что телефонный номер у нас состоит ровно (всего!) из 10 цифр, среди которых нам надо вычленить префиксный код. То есть наша задача спокойно решается простым перебором не более чем 10 вариантов:
[посмотреть на explain.tensor.ru]
Нам потребовалось всего 5 обращений к индексу, чтобы найти искомый код. Выигрыш кажется микроскопическим в абсолютных цифрах, но мы получили снижение нагрузки в 150 раз относительно наивного варианта! Если вашей системе приходится обрабатывать десятки и сотни тысяч таких запросов в час — экономия становится весьма солидной!
Как правильно заметил в комментариях miksir, раз у нас все пары «код + диапазон» и входящий номер имеют строго одинаковую размерность в 10 цифр, то задачу можно свести к интервальному поиску среди числовых значений.
Для этого создадим индекс, который будет рассматривать наши записи как
После этого мы сможем использовать его в запросе:
[посмотреть на explain.tensor.ru]
Сначала убедимся, что наши диапазоны номеров действительно не пересекаются:
Если получили «ничего» — все хорошо, и можно применить следующую оптимизацию: номер может входить только в тот из диапазонов, к концу (или началу) которого находится ближе всего.
Для поиска ближайшего «начала» нам достаточно обычного btree-индекса:
Несмотря на кажущуюся простоту, этот вариант дает производительность хуже предыдущего:
[посмотреть на explain.tensor.ru]
Теперь давайте представим, что у нас уже есть таблица с клиентами, где записан «подчищенный» номер телефона — убраны все скобки, дефисы, и т.п.
Но вот неприятность, далеко не все и них имеют код города — то ли менеджеры ленятся забивать, то ли АТС так настроена, что присылает не полные, а «внутригородские» номера… Как тогда найти клиента — ведь поиск по полному соответствию уже не сработает?
В этом случае воспользуемся тем же «переборным» алгоритмом. Только «отщипывать» цифры будем не с конца номера, а с начала.
Если номер в карточке клиента был указан полностью, мы на первой же итерации на него наткнемся. Если не полностью — когда «отрежем» какой-то из подходящих кодов.
Безусловно, нам потребуется какая-то перекрестная проверка по другим реквизитам (адрес, ИНН, ...), чтобы не получилось ситуации, что из входящего номера мы «отрезали» код Москвы, а по оставшемуся 7-значному номеру нашли клиента из Санкт-Петербурга.
Тут ситуация интереснее. «Приращивать» каждый возможный код к короткому номеру и пробовать искать мы не можем — их слишком много. Взглянем на ситуацию с другой стороны — буквально:
Оказывается, если развернуть строки с номерами, то задача превращается в обычный префиксный поиск, который легко решается с помощью индекса с varchar_pattern_ops и
А дальше, опять-таки перепроверяем дополнительную информацию — из какого региона АТС нам прислала номер, к какому региону относится клиент.
Например, можно менеджеру сразу показать из какого города идет звонок, подтянуть актуальный прайс и условия доставки, вывести карточку звонящего клиента, последние сделки с ним, конкретное контактное лицо,… — да много чего полезного, как это умеет наш СБИС CRM!
А как этот функционал реализовать самостоятельно? Оказывается, не так уж сложно. Собрать и опробовать работающую модель можно, буквально, «на коленке» — нужна только связка из Node.js и PostgreSQL.
Определяем регион по номеру
Давайте предположим, что АТС присылает нам уже нормализованный и отформатированный до 10 цифр (будем рассматривать только звонки внутри России) входящий телефонный номер. Как наиболее эффективно понять, откуда пришел звонок?
Собираем телефонные коды
Сначала нам понадобится база телефонных кодов России в привязке к регионам. Для этого можно воспользоваться официальным источником — актуальной выпиской из плана нумерации на сайте Федерального агентства связи.
Но найти — мало, надо эти данные скачать и извлечь. В этом нам поможет небольшой скрипт для Node.js, использующий библиотеку request:
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
Если все сработало как надо, в нашу таблицу будет загружено почти 378 тысяч диапазонов:
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;
[посмотреть на explain.tensor.ru]
Вычитали почти 70 тысяч строк (и это еще повезло, что не все 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
Как правильно заметил в комментариях miksir, раз у нас все пары «код + диапазон» и входящий номер имеют строго одинаковую размерность в 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]
Определяем клиента по номеру
Теперь давайте представим, что у нас уже есть таблица с клиентами, где записан «подчищенный» номер телефона — убраны все скобки, дефисы, и т.п.
Но вот неприятность, далеко не все и них имеют код города — то ли менеджеры ленятся забивать, то ли АТС так настроена, что присылает не полные, а «внутригородские» номера… Как тогда найти клиента — ведь поиск по полному соответствию уже не сработает?
АТС дает полный номер
В этом случае воспользуемся тем же «переборным» алгоритмом. Только «отщипывать» цифры будем не с конца номера, а с начала.
Если номер в карточке клиента был указан полностью, мы на первой же итерации на него наткнемся. Если не полностью — когда «отрежем» какой-то из подходящих кодов.
Безусловно, нам потребуется какая-то перекрестная проверка по другим реквизитам (адрес, ИНН, ...), чтобы не получилось ситуации, что из входящего номера мы «отрезали» код Москвы, а по оставшемуся 7-значному номеру нашли клиента из Санкт-Петербурга.
АТС дает «городской» номер
пришло от АТС : 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) || '%');
А дальше, опять-таки перепроверяем дополнительную информацию — из какого региона АТС нам прислала номер, к какому региону относится клиент.