Clever Geek Handbook

Sonó mi teléfono. ¿Quién habla? .. Ayudará a "elefante"

La identificación automática de un cliente y su región mediante una llamada telefónica entrante se ha convertido en una parte integral de cualquier HelpDesk o sistema CRM desarrollado. Solo necesita poder hacerlo rápidamente , entonces aparecen muchas oportunidades.



Por ejemplo, puede mostrar inmediatamente al gerente de qué ciudad proviene la llamada, ajustar la lista de precios actual y las condiciones de entrega, mostrar la tarjeta de la persona que llama, las últimas transacciones con él, una persona de contacto específica, ... - ¡y muchas cosas útiles, como puede hacer nuestro VLSI CRM !





¿Cómo implementar esta funcionalidad usted mismo? No resulta tan difícil. Literalmente, puede construir y probar un modelo funcional en la rodilla; solo necesita un paquete de Node.js y PostgreSQL.



Determinar la región por número



Supongamos que la centralita nos envía un número de teléfono entrante, ya normalizado y formateado hasta 10 dígitos (consideraremos solo llamadas dentro de Rusia). ¿Cuál es la forma más eficaz de saber de dónde procede la llamada?



Recopilación de códigos telefónicos



Primero, necesitamos una base de datos de códigos telefónicos de Rusia en relación con las regiones. Para hacer esto, puede usar una fuente oficial: un extracto actualizado del plan de numeración en el sitio web de la Agencia Federal de Comunicaciones.



Pero encontrar no es suficiente, necesita descargar y extraer estos datos. Un pequeño script para Node.js usando la biblioteca de solicitudes nos ayudará con esto : 



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


Ahora cargámoslo en nuestra base de pruebas y ya puedes trabajar: 



psql -f phonecodes.sql -U postgres tst


Si todo salió como debería, se cargarán casi 378 mil rangos en nuestra tabla: 



SET
CREATE TABLE
COPY 377937


Tenga en cuenta que en nuestro ejemplo, tanto el código como los números de límite del rango están representados por cadenas. Sí, se pueden convertir en integer/bigint, pero no lo haremos por ahora. Además, el número de teléfono entrante no siempre consta solo de números; por ejemplo, algunos teléfonos públicos pueden informar su número con el "dígito A".


"Están buscando bomberos, la policía está buscando ..."



Probemos primero con una consulta ingenua: 



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




[mira explica.tensor.ru] Restamos



casi 70 mil líneas (¡y fue una suerte que no las 380!), casi 10 MB de datos fueron recogidos ... no muy eficientemente, pero el resultado se logró: 



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


¡Pero eliminémoslo de alguna manera Seq Scan! Para hacer esto, solo necesitamos un índice que ayude a buscar por LIKE, ¿verdad? .. Por



desgracia, no. Si necesitamos buscar column LIKE (val || '%'), entonces los índices de prefijos con varchar_pattern_ops nos ayudarán , pero tenemos lo contrario - val LIKE (column || '%'). Y obtenemos una situación cercana a la que describí en el artículo "Clasificación de errores de registros de PostgreSQL" .



Usamos el conocimiento del campo aplicado



Cerca, pero, afortunadamente, todavía es mucho más simple: nuestros datos son fijos y hay relativamente pocos de ellos. Además, los registros se distribuyen de forma bastante dispersa por códigos: 



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


Solo alrededor de cien códigos tienen 10 rangos, y casi una cuarta parte tiene exactamente uno: 



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


Así que indexemos el código por ahora. Y como necesitamos todos los rangos del mismo código todos juntos CLUSTER, organicemos nuestra tabla con la ayuda de para que los registros estén físicamente uno al lado del otro: 



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


Ahora recordemos que nuestro número de teléfono consta de exactamente (¡en total!) 10 dígitos, entre los cuales debemos aislar el código de prefijo. Es decir, nuestra tarea se resuelve tranquilamente mediante una simple enumeración de no más de 10 opciones: 



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;




[mira explica.tensor.ru]



Solo nos tomó 5 llamadas de índice para encontrar el código que estábamos buscando. La ganancia parece microscópica en números absolutos, ¡pero obtuvimos una reducción de 150 veces en la carga en relación con la opción ingenua! Si su sistema tiene que procesar decenas o cientos de miles de solicitudes de este tipo por hora, los ahorros se vuelven muy sustanciales.

Y puede hacer incluso menos iteraciones sobre el índice, si todos los códigos se reducen preliminarmente a la forma clásica "de 3 a 5 dígitos". Sin embargo, la cantidad de rangos en cada código aumentará y filtrarlos puede agregar problemas.


int8range + GiST



Como se señaló correctamente en los comentarios miksir, dado que tenemos todos los pares "código + rango" y el número entrante tiene estrictamente la misma dimensión de 10 dígitos, entonces el problema se puede reducir a una búsqueda de intervalo entre valores numéricos.



Para hacer esto, crearemos un índice que tratará nuestros registros como int8range: 



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


Después de eso, podemos usarlo en la solicitud: 



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;




[mira explica.tensor.ru]



Intervalos no superpuestos + btree



Primero, asegurémonos de que nuestros rangos de números no se superpongan 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;


Si obtiene "nada", todo está bien y puede aplicar la siguiente optimización: el número solo se puede incluir en el rango, hasta el final (o comienzo) del que está más cerca .



Para encontrar el "comienzo" más cercano, necesitamos un í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;


A pesar de su aparente simplicidad, esta opción ofrece un rendimiento peor que la anterior:





[mira explica.tensor.ru]



Identificamos al cliente por número



Ahora imaginemos que ya tenemos una tabla con clientes, donde se escribe el número de teléfono "limpiado": se eliminan todos los corchetes, guiones, etc.



Pero aquí hay una molestia, no todos tienen un código de ciudad , o los gerentes son demasiado perezosos para puntuar, o el PBX está tan configurado que no envía números completos, sino "intracity" ... ¿Cómo entonces encontrar un cliente? Después de todo, una búsqueda completa de coincidencias ya no funcionará.



PBX da el número completo



En este caso, usaremos el mismo algoritmo "exhaustivo" . Solo "pellizcaremos" los números no desde el final del número, sino desde el principio.



Si el número en la tarjeta del cliente se indicó en su totalidad, lo encontraremos en la primera iteración. Si no completamente, cuando "cortamos" algunos de los códigos apropiados.



Por supuesto, necesitaremos algún tipo de verificación cruzada con otros detalles (dirección, TIN, ...) para que no obtengamos una situación en la que "cortamos" el código de Moscú del número entrante y encontramos un cliente de San Petersburgo por el número restante de 7 dígitos. San Petersburgo.



PBX da un número de "ciudad"



        :     262000
   : 4852262000


Aquí la situación es más interesante. No podemos "incrementar" todos los códigos posibles a un número corto e intentar buscar, hay demasiados. Veamos la situación desde el otro lado, literalmente: 



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


Resulta que si expande las líneas con números, entonces la tarea se convierte en una búsqueda de prefijo regular , que se resuelve fácilmente usando un índice con varchar_pattern_ops y LIKE! 



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


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


Y luego, nuevamente, verificamos la información adicional: de qué región la PBX nos envió el número, a qué región pertenece el cliente.


More articles:


All Articles