17.08.2020 11:55
Kilor 38 2900 Источник
Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой 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 тысяч диапазонов:

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) || '%');

А дальше, опять-таки перепроверяем дополнительную информацию — из какого региона АТС нам прислала номер, к какому региону относится клиент.