Clever Geek Handbook

Mon téléphone a sonné. Qui parle? .. Aidera "l'éléphant"

L'identification automatique d'un client et de sa région par un appel téléphonique entrant fait désormais partie intégrante de tout système HelpDesk ou CRM développé. Vous avez juste besoin de pouvoir le faire rapidement - alors de nombreuses opportunités apparaissent.



Par exemple, vous pouvez immédiatement montrer au responsable de quelle ville l'appel provient, resserrer la liste de prix et les conditions de livraison actuelles, afficher la carte de l'appelant, les dernières transactions avec lui, un interlocuteur spécifique, ... - et beaucoup de choses utiles, comme notre VLSI CRM peut le faire !





Comment implémenter vous-même cette fonctionnalité? Cela ne s'avère pas si difficile. Vous pouvez littéralement construire et tester un modèle fonctionnel sur le genou - vous avez juste besoin d'un ensemble de Node.js et PostgreSQL.



Déterminez la région par numéro



Supposons que le PBX nous envoie un numéro de téléphone entrant, déjà normalisé et formaté jusqu'à 10 chiffres (nous ne considérerons que les appels en Russie). Quelle est la manière la plus efficace de comprendre d'où vient l'appel?



Collecte des codes téléphoniques



Premièrement, nous avons besoin d'une base de données des codes téléphoniques de la Russie par rapport aux régions. Pour ce faire, vous pouvez utiliser une source officielle - un extrait à jour du plan de numérotation sur le site Web de l'Agence fédérale des communications.



Mais trouver ne suffit pas, vous devez télécharger et extraire ces données. Un petit script pour Node.js utilisant la bibliothèque de requêtes nous aidera avec ceci : 



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


Maintenant, chargeons-le dans notre base de test, et vous pouvez travailler: 



psql -f phonecodes.sql -U postgres tst


Si tout fonctionne comme il se doit, près de 378000 plages seront chargées dans notre tableau: 



SET
CREATE TABLE
COPY 377937


Notez que dans notre exemple, le code et les numéros de limite de la plage sont représentés par des chaînes. Oui, ils peuvent être transformés en integer/bigint, mais nous ne le ferons pas pour le moment. De plus, le numéro de téléphone entrant ne se compose pas toujours uniquement de chiffres - par exemple, certains téléphones publics peuvent indiquer leur numéro avec le «chiffre A».


"Ils recherchent des pompiers, la police cherche ..."



Essayons d'abord une requête naïve: 



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




[regardez expliquez.tensor.ru] Nous avons soustrait



près de 70 000 lignes (et nous avons eu de la chance que les 380 lignes ne soient pas toutes pelletées!), près de 10 Mo de données ont été pelletées ... pas très efficacement, mais le résultat est atteint: 



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


Mais débarrassons-nous en quelque sorte Seq Scan! Pour ce faire, nous avons juste besoin d'un index qui aidera à rechercher par LIKE, non? ..



Hélas, non. Si nous avons besoin de rechercher column LIKE (val || '%'), alors les index de préfixe avec varchar_pattern_ops nous aideront , mais nous avons le contraire - val LIKE (column || '%'). Et nous obtenons une situation proche de celle que j'ai décrite dans l'article "Classifier les erreurs à partir des logs PostgreSQL" .



Nous utilisons la connaissance du domaine appliqué



Proche, mais heureusement, c'est encore beaucoup plus simple - nos données sont fixes et il y en a relativement peu. De plus, les enregistrements sont distribués assez peu par codes: 



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


Une centaine de codes seulement ont 10 plages, et près d'un quart en ont exactement une: 



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


Donc, indexons uniquement le code pour le moment. Et comme nous avons besoin de toutes les plages du même code ensemble, nous allons ordonner notre table à l'aide de CLUSTERafin que les enregistrements soient physiquement les uns à côté des autres: 



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


Rappelons maintenant que notre numéro de téléphone se compose d'exactement (au total!) 10 chiffres, parmi lesquels nous devons isoler le code de préfixe. Autrement dit, notre tâche est résolue calmement par une simple énumération de pas plus de 10 options: 



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;




[regardez explic.tensor.ru]



Il ne nous a fallu que 5 appels d'index pour trouver le code que nous recherchions. Le gain semble microscopique en chiffres absolus, mais nous avons obtenu une réduction de charge de 150 fois par rapport à l'option naïve! Si votre système doit traiter des dizaines ou des centaines de milliers de demandes de ce type par heure, les économies deviennent très substantielles!

Et vous pouvez faire encore moins d'itérations sur l'index - si tous les codes sont pré-réduits à la forme classique "de 3 à 5 chiffres". Cependant, le nombre de plages dans chaque code augmentera et leur filtrage peut ajouter des problèmes.


int8range + GiST



Comme correctement noté dans les commentaires Miksir, puisque nous avons toutes les paires "code + plage" et que le nombre entrant a strictement la même dimension de 10 chiffres, alors le problème peut être réduit à une recherche par intervalles parmi des valeurs numériques.



Pour ce faire, nous allons créer un index qui traitera nos enregistrements comme int8range: 



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


Après cela, nous pouvons l'utiliser dans la demande: 



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;




[regardez expliquez.tensor.ru]



Intervalles sans chevauchement + arbre b



Tout d'abord, assurons-nous que nos plages de nombres ne se chevauchent pas vraiment: 



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 vous n'obtenez "rien" - tout va bien, et vous pouvez appliquer l'optimisation suivante: le nombre ne peut être inclus que dans la plage, à la fin (ou au début) dont il est le plus proche .



Pour trouver le "début" le plus proche, nous avons juste besoin d'un index btree régulier: 



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;


Malgré son apparente simplicité, cette option donne des performances moins bonnes que la précédente:





[regardez explic.tensor.ru]



Nous identifions le client par numéro



Imaginons maintenant que nous ayons déjà une table avec les clients, où le numéro de téléphone "nettoyé" est écrit - tous les crochets, tirets, etc. sont supprimés.



Mais voici le désagrément, tous n'ont pas de code de ville - soit les gestionnaires sont trop paresseux pour marquer, soit le PBX est tellement configuré qu'il n'envoie pas des numéros complets, mais des numéros "intracités" ... Comment alors trouver un client - après tout, une recherche de correspondance complète ne fonctionnera plus?



PBX donne le numéro complet



Dans ce cas, nous utiliserons le même algorithme «exhaustif» . Seulement nous «pincerons» les nombres non pas à partir de la fin du nombre, mais depuis le début.



Si le numéro de la carte client était indiqué en entier, nous tomberons dessus dès la première itération. Si ce n'est pas complètement - lorsque nous "coupons" certains des codes appropriés.



Bien sûr, nous aurons besoin d'une sorte de vérification croisée par d'autres détails (adresse, NIF, ...) afin de ne pas avoir une situation où nous «coupons» le code de Moscou du numéro entrant et avons trouvé un client de Saint-Pétersbourg par le numéro à 7 chiffres restant. Pétersbourg.



PBX donne un numéro de "ville"



        :     262000
   : 4852262000


Ici, la situation est plus intéressante. Nous ne pouvons pas "incrémenter" chaque code possible en un nombre court et essayer de rechercher - il y en a trop. Regardons la situation de l'autre côté - littéralement: 



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


Il s'avère que si vous développez les lignes avec des nombres, la tâche se transforme en une recherche de préfixe régulière , qui est facilement résolue en utilisant un index avec varchar_pattern_ops et LIKE! 



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


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


Et puis, encore une fois, nous vérifions les informations supplémentaires - à partir de quelle région le PBX nous a envoyé le numéro, à quelle région appartient le client.


More articles:


All Articles