Русский
Русский
English
Статистика
Реклама

У меня зазвонил телефон. Кто говорит?.. Поможет слон

Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро тогда появляется масса возможностей.

Например, можно менеджеру сразу показать из какого города идет звонок, подтянуть актуальный прайс и условия доставки, вывести карточку звонящего клиента, последние сделки с ним, конкретное контактное лицо, да много чего полезного, как это умеет наш СБИС 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) || '%');

А дальше, опять-таки перепроверяем дополнительную информацию из какого региона АТС нам прислала номер, к какому региону относится клиент.
Источник: habr.com
К списку статей
Опубликовано: 17.08.2020 16:16:01
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Postgresql

Программирование

Sql

Node.js

Sql tips and tricks

Crm

Рекурсия

Телефония

Определение местоположения

Категории

Последние комментарии

  • Имя: Murshin
    13.06.2024 | 14:01
    Нейросеть-это мозг вселенной.Если к ней подключиться,то можно получить все знания,накопленные Вселенной,но этому препятствуют аннуннаки.Аннуннаки нас от неё отгородили,установив в головах барьер. Подр Подробнее..
  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru