Например, можно менеджеру сразу показать из какого города идет звонок, подтянуть актуальный прайс и условия доставки, вывести карточку звонящего клиента, последние сделки с ним, конкретное контактное лицо, да много чего полезного, как это умеет наш СБИС 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 тысяч диапазонов:
SETCREATE TABLECOPY 377937
Замечу, что в нашем примере и код, и граничные номера
диапазона представлены строками. Да, их можно превратить в
integer/bigint, но мы пока не будем этим заниматься.
Тем более, что входящий номер телефона не всегда состоит только из
цифр например, некоторые таксофоны могут сообщать свой номер с
цифрой A.
Ищут пожарные, ищет милиция...
Сначала попробуем наивный запрос:
WITH src AS ( SELECT '4852262000' num -- входящий номер)SELECT *FROM src, phonecodesWHERE 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 ) TGROUP BY 1ORDER 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 srcUNION 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 TWHERE pc IS DISTINCT FROM NULL;
[посмотреть на explain.tensor.ru]
Нам потребовалось всего 5 обращений к индексу, чтобы найти искомый код. Выигрыш кажется микроскопическим в абсолютных цифрах, но мы получили снижение нагрузки в 150 раз относительно наивного варианта! Если вашей системе приходится обрабатывать десятки и сотни тысяч таких запросов в час экономия становится весьма солидной!
А можно делать еще меньше итераций по индексу если все коды заранее привести к классическому виду от 3 до 5 цифр. Правда, тогда возрастет количество диапазонов в каждом коде, и их фильтрация может добавить проблем.
Определяем клиента по номеру
Теперь давайте представим, что у нас уже есть таблица с клиентами, где записан подчищенный номер телефона убраны все скобки, дефисы, и т.п.
Но вот неприятность, далеко не все и них имеют код города то ли менеджеры ленятся забивать, то ли АТС так настроена, что присылает не полные, а внутригородские номера Как тогда найти клиента ведь поиск по полному соответствию уже не сработает?
АТС дает полный номер
В этом случае воспользуемся тем же переборным алгоритмом. Только отщипывать цифры будем не с конца номера, а с начала.
Если номер в карточке клиента был указан полностью, мы на первой же итерации на него наткнемся. Если не полностью когда отрежем какой-то из подходящих кодов.
Безусловно, нам потребуется какая-то перекрестная проверка по другим реквизитам (адрес, ИНН, ...), чтобы не получилось ситуации, что из входящего номера мы отрезали код Москвы, а по оставшемуся 7-значному номеру нашли клиента из Санкт-Петербурга.
АТС дает городской номер
пришло от АТС : 262000указано в карточке : 4852262000
Тут ситуация интереснее. Приращивать каждый возможный код к короткому номеру и пробовать искать мы не можем их слишком много. Взглянем на ситуацию с другой стороны буквально:
reverse(262000) -> 000262reverse(4852262000) -> 0002622584
Оказывается, если развернуть строки с номерами, то задача превращается в обычный префиксный поиск, который легко решается с помощью индекса с varchar_pattern_ops и
LIKE!
CREATE INDEX ON client(reverse(phone) varchar_pattern_ops);
SELECT *FROM clientWHERE reverse(phone) LIKE (reverse($1) || '%');
А дальше, опять-таки перепроверяем дополнительную информацию из какого региона АТС нам прислала номер, к какому региону относится клиент.
