Clever Geek Handbook

Meu telefone tocou. Quem fala? .. Vai ajudar "elefante"

A identificação automática de um cliente e de sua região por meio de uma chamada telefônica tornou-se parte integrante de qualquer sistema de HelpDesk ou CRM desenvolvido. Você só precisa ser capaz de fazer isso rapidamente - então, muitas oportunidades aparecem.



Por exemplo, você pode imediatamente mostrar ao gerente de qual cidade está vindo a chamada, restringir a lista de preços e condições de entrega atuais, exibir o cartão do chamador, as últimas transações com ele, um contato específico, ... - e muitas coisas úteis, como nosso VLSI CRM pode fazer !





Como implementar essa funcionalidade sozinho? Acontece que não é tão difícil. Você pode literalmente construir e testar um modelo funcional - você só precisa de um pacote de Node.js e PostgreSQL.



Determine a região por número



Vamos supor que o PABX nos envie um número de telefone de entrada, já normalizado e formatado em até 10 dígitos (consideraremos apenas chamadas dentro da Rússia). Qual é a maneira mais eficiente de entender de onde veio a chamada?



Coletando códigos de telefone



Primeiro, precisamos de um banco de dados de códigos telefônicos da Rússia em relação às regiões. Para fazer isso, você pode usar uma fonte oficial - um extrato atualizado do plano de numeração no site da Agência Federal de Comunicações.



Mas encontrar não é suficiente, você precisa baixar e extrair esses dados. Um pequeno script para Node.js usando a biblioteca de solicitações nos ajudará com isso : 



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);
  }
);


Agora vamos carregá-lo em nossa base de teste, e você pode trabalhar: 



psql -f phonecodes.sql -U postgres tst


Se tudo correr como deveria, quase 378 mil faixas serão carregadas em nossa mesa: 



SET
CREATE TABLE
COPY 377937


Observe que, em nosso exemplo, tanto o código quanto os números de limite do intervalo são representados por strings. Sim, eles podem ser transformados integer/bigint, mas não faremos isso por enquanto. Além disso, o número de telefone recebido nem sempre consiste apenas em números - por exemplo, alguns telefones públicos podem informar seu número com o "dígito A".


"Eles estão procurando bombeiros, a polícia está procurando ..."



Vamos tentar uma pergunta ingênua primeiro: 



WITH src AS (
  SELECT '4852262000' num --  
)
SELECT
  *
FROM
  src
, phonecodes
WHERE
  num LIKE (code || '%') AND --   
  num BETWEEN (code || numb) AND (code || nume) --    
LIMIT 1;




[veja explain.tensor.ru]



Quase 70 mil linhas foram subtraídas (e foi uma sorte que nem todas as 380 linhas!), quase 10 MB de dados foram retirados ... não muito eficientemente, mas o resultado foi alcançado: 



num        | code   | numb | nume | oper | region           | district | city
-----------------------------------------------------------------------------------
4852262000 | 485226 | 0000 | 9999 |   |  . |          |


Mas vamos nos livrar disso de alguma forma Seq Scan! Para fazer isso, precisamos apenas de um índice que ajudará a pesquisar por LIKE, certo? ..



Infelizmente, não. Se precisarmos pesquisar column LIKE (val || '%'), os índices de prefixo com varchar_pattern_ops nos ajudarão , mas temos o oposto - val LIKE (column || '%'). E temos uma situação próxima à que descrevi no artigo "Classificando erros de logs do PostgreSQL" .



Usamos o conhecimento da área aplicada



Perto, mas, felizmente, ainda é muito mais simples - nossos dados são fixos e há relativamente poucos deles. Além disso, os registros são distribuídos de forma bastante esparsa por códigos: 



SELECT --     - 
  ranges
, count(*)
FROM
  (
    SELECT --     
      code
    , count(*) ranges
    FROM
      phonecodes
    GROUP BY
      1
  ) T
GROUP BY
  1
ORDER BY
  1 DESC;


Apenas cerca de cem códigos têm 10 intervalos e quase um quarto tem exatamente um: 



ranges | count
--------------
    10 |   121
     9 |   577
     8 |  1705
     7 |  3556
     6 |  6667
     5 | 10496
     4 | 12491
     3 | 20283
     2 | 22627
     1 | 84453


Portanto, vamos apenas indexar o código por enquanto. E como precisamos de todos os intervalos do mesmo código todos juntos, ordenaremos nossa tabela com a ajuda de de CLUSTERmodo que os registros fiquem fisicamente próximos uns dos outros: 



CREATE INDEX ON phonecodes(code);
CLUSTER phonecodes USING phonecodes_code_idx;


Agora, vamos lembrar que nosso número de telefone consiste em exatamente (no total!) 10 dígitos, entre os quais precisamos isolar o código do prefixo. Ou seja, nossa tarefa é resolvida com calma por uma simples enumeração de não mais do que 10 opções: 



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;




[veja explain.tensor.ru]



Levamos apenas 5 chamadas de índice para encontrar o código que estávamos procurando. O ganho parece microscópico em números absolutos, mas obtivemos uma redução de 150 vezes na carga em relação à opção ingênua! Se o seu sistema tiver que processar dezenas ou centenas de milhares de solicitações por hora, a economia será muito grande!

E você pode fazer ainda menos iterações sobre o índice - se todos os códigos forem preliminarmente reduzidos à forma clássica "de 3 para 5 dígitos". No entanto, o número de intervalos em cada código aumentará e filtrá-los pode adicionar problemas.


int8range + GiST



Como corretamente observado nos comentários Miksir, uma vez que temos todos os pares "código + intervalo" e o número de entrada tem estritamente a mesma dimensão de 10 dígitos, então o problema pode ser reduzido a uma busca de intervalo entre valores numéricos.



Para fazer isso, criaremos um índice que tratará nossos registros como int8range: 



CREATE INDEX ON phonecodes USING gist(
  int8range(
    (code || numb)::bigint --   
  , (code || nume)::bigint --   
  , '[]' --   
  )
);


Depois disso, podemos usá-lo na solicitação: 



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;




[veja explain.tensor.ru]



Intervalos não sobrepostos + árvore b



Primeiro, vamos garantir que nossos intervalos numéricos não se sobreponham realmente: 



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;


Se você obtiver "nada" - está tudo bem, e você pode aplicar a seguinte otimização: o número só pode ser incluído no intervalo, ao final (ou início) do qual está mais próximo .



Para encontrar o "começo" mais próximo, precisamos apenas de um índice btree regular: 



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;


Apesar de sua aparente simplicidade, esta opção tem um desempenho pior do que a anterior:





[veja explain.tensor.ru]



Identificamos o cliente por número



Agora vamos imaginar que já temos uma tabela com clientes, onde o número de telefone "limpo" está escrito - todos os colchetes, hífens, etc. são removidos.



Mas aqui está o incômodo, nem todos eles têm um código de cidade - ou os gerentes têm preguiça de pontuar ou o PBX está configurado de modo que não envia números completos, mas "intracity" ... Como então encontrar um cliente - afinal, uma busca de correspondência completa não funcionará mais?



PBX fornece o número completo



Nesse caso, usaremos o mesmo algoritmo "exaustivo" . Apenas "cortaremos" os números não do final do número, mas do início.



Se o número no cartão do cliente foi indicado por completo, vamos topar com ele na primeira iteração. Se não completamente - quando "cortamos" alguns dos códigos apropriados.



Claro, precisaremos de algum tipo de verificação cruzada com outros detalhes (endereço, TIN, ...) para que não tenhamos uma situação de "cortar" o código de Moscou do número recebido e encontrar um cliente de São Petersburgo pelo número de 7 dígitos restantes. Petersburgo.



PBX fornece um número de "cidade"



        :     262000
   : 4852262000


Aqui a situação é mais interessante. Não podemos "incrementar" todos os códigos possíveis a um número curto e tentar pesquisar - há muitos deles. Vejamos a situação do outro lado - literalmente: 



    reverse(262000) -> 000262
reverse(4852262000) -> 0002622584


Acontece que se você expandir as linhas com números, a tarefa se transforma em uma pesquisa de prefixo regular , que é facilmente resolvida usando um índice com varchar_pattern_ops e LIKE! 



CREATE INDEX ON client(reverse(phone) varchar_pattern_ops);


SELECT
  *
FROM
  client
WHERE
  reverse(phone) LIKE (reverse($1) || '%');


E então, novamente, verificamos as informações adicionais - de qual região o PBX nos enviou o número, a qual região o cliente pertence.


More articles:


All Articles