Clever Geek Handbook

Mein Telefon klingelte. Wer spricht? .. Hilft "Elefant"

Die automatische Identifizierung eines Kunden und seiner Region durch einen eingehenden Anruf ist ein wesentlicher Bestandteil jedes entwickelten HelpDesk- oder CRM-Systems. Sie müssen es nur schnell tun können - dann ergeben sich viele Möglichkeiten.



Sie können beispielsweise dem Manager sofort anzeigen, aus welcher Stadt der Anruf kommt, die aktuelle Preisliste und die Lieferbedingungen verschärfen, die Karte des Anrufers, die letzten Transaktionen mit ihm, einen bestimmten Ansprechpartner usw. anzeigen - und viele nützliche Dinge, wie dies unser VLSI CRM kann !





Wie kann ich diese Funktionalität selbst implementieren? Es stellt sich nicht so schwierig heraus. Sie können buchstäblich ein funktionierendes Modell am Knie erstellen und testen - Sie benötigen lediglich ein Bündel von Node.js und PostgreSQL.



Bestimmen Sie die Region anhand der Nummer



Nehmen wir an, die TK-Anlage sendet uns eine eingehende Telefonnummer, die bereits normalisiert und mit bis zu 10 Ziffern formatiert ist (wir werden nur Anrufe innerhalb Russlands berücksichtigen). Was ist der effizienteste Weg, um zu verstehen, woher der Anruf kam?



Telefoncodes sammeln



Erstens brauchen wir eine Datenbank mit Telefoncodes Russlands in Bezug auf Regionen. Dazu können Sie eine offizielle Quelle verwenden - einen aktuellen Auszug aus dem Nummerierungsplan auf der Website des Bundesamtes für Kommunikation.



Das Auffinden reicht jedoch nicht aus. Sie müssen diese Daten herunterladen und extrahieren. Ein kleines Skript für Node.js, das die Anforderungsbibliothek verwendet, hilft uns dabei : 



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


Laden wir es jetzt in unsere Testbasis und Sie können arbeiten: 



psql -f phonecodes.sql -U postgres tst


Wenn alles so geklappt hat, wie es sollte, werden fast 378.000 Bereiche in unsere Tabelle geladen: 



SET
CREATE TABLE
COPY 377937


Beachten Sie, dass in unserem Beispiel sowohl der Code als auch die Grenznummern des Bereichs durch Zeichenfolgen dargestellt werden. Ja, sie können verwandelt werden integer/bigint, aber wir werden dies vorerst nicht tun. Darüber hinaus besteht die eingehende Telefonnummer nicht immer nur aus Nummern. Beispielsweise können einige Münztelefone ihre Nummer mit der "Ziffer A" melden.


"Sie suchen Feuerwehrleute, die Polizei sucht ..."



Versuchen wir zuerst eine naive Abfrage: 



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




[siehe EXPLAIN.tensor.ru] Wir haben



fast 70.000 Zeilen abgezogen (und es war ein Glück, dass nicht alle 380 Zeilen!), fast 10 MB Daten wurden geschaufelt ... nicht sehr effizient, aber das Ergebnis wird erzielt: 



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


Aber lasst es uns irgendwie loswerden Seq Scan! Dazu brauchen wir nur einen Index, der bei der Suche hilft LIKE, oder? ..



Ach, nein. Wenn wir suchen müssen column LIKE (val || '%'), helfen uns Präfixindizes mit varchar_pattern_ops , aber wir haben das Gegenteil - val LIKE (column || '%'). Und wir bekommen eine Situation ähnlich der, die ich im Artikel "Klassifizieren von Fehlern aus PostgreSQL-Protokollen" beschrieben habe .



Wir nutzen Kenntnisse des Fachgebiets



Nah dran, aber zum Glück ist es noch viel einfacher - unsere Daten sind fixiert und es gibt relativ wenige davon. Darüber hinaus sind die Datensätze durch Codes eher spärlich verteilt: 



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


Nur etwa hundert Codes haben 10 Bereiche, und fast ein Viertel hat genau einen: 



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


Lassen Sie uns jetzt nur den Code indizieren. Und da wir alle Bereiche desselben Codes zusammen benötigen, ordnen wir unsere Tabelle mit Hilfe von CLUSTERso, dass die Datensätze physisch nebeneinander liegen: 



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


Und jetzt denken wir daran, dass unsere Telefonnummer aus genau (allen!) 10 Ziffern besteht, unter denen wir den Präfixcode isolieren müssen. Das heißt, unsere Aufgabe wird ruhig durch eine einfache Aufzählung von nicht mehr als 10 Optionen gelöst: 



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;




[siehe EXPLAIN.tensor.ru]



Wir brauchten nur 5 Indexaufrufe, um den gesuchten Code zu finden. Der Gewinn scheint in absoluten Zahlen mikroskopisch klein zu sein, aber wir haben eine 150-fache Reduzierung der Last im Vergleich zur naiven Option! Wenn Ihr System Zehntausende oder Hunderttausende solcher Anfragen pro Stunde verarbeiten muss, werden die Einsparungen sehr erheblich!

Und Sie können noch weniger Iterationen über den Index durchführen - wenn alle Codes auf die klassische Form "von 3 auf 5 Stellen" reduziert sind. Dann erhöht sich jedoch die Anzahl der Bereiche in jedem Code, und das Filtern dieser Bereiche kann zu Problemen führen.


int8range + GiST



Wie in den Kommentaren richtig vermerkt MiksirDa wir alle Paare "Code + Bereich" haben und die eingehende Nummer genau die gleiche Dimension von 10 Ziffern hat, kann das Problem auf eine Intervallsuche zwischen numerischen Werten reduziert werden.



Zu diesem Zweck erstellen wir einen Index, der unsere Datensätze wie folgt behandelt int8range: 



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


Danach können wir es in der Anfrage verwenden: 



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;




[siehe EXPLAIN.tensor.ru]



Nicht überlappende Intervalle + Baum



Stellen wir zunächst sicher, dass sich unsere Nummernkreise nicht wirklich überschneiden: 



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;


Wenn Sie "nichts" erhalten, ist alles in Ordnung, und Sie können die folgende Optimierung anwenden: Die Zahl kann nur in den Bereich aufgenommen werden, dessen Ende (oder Anfang) am nächsten liegt .



Um den nächsten "Anfang" zu finden, benötigen wir einen regulären btree-Index: 



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;


Trotz ihrer offensichtlichen Einfachheit bietet diese Option eine schlechtere Leistung als die vorherige:





[siehe EXPLAIN.tensor.ru]



Wir identifizieren den Kunden anhand seiner Nummer



Stellen wir uns nun vor, wir haben bereits eine Tabelle mit Kunden, in der die "bereinigte" Telefonnummer geschrieben ist - alle Klammern, Bindestriche usw. werden entfernt.



Aber hier ist ein Ärgernis, nicht alle haben einen Stadtcode - entweder sind die Manager zu faul, um zu punkten, oder die TK-Anlage ist so konfiguriert, dass sie nicht vollständige, sondern "Intracity" -Nummern sendet ... Wie kann man dann einen Kunden finden - schließlich funktioniert eine vollständige Übereinstimmungssuche nicht mehr?



PBX gibt die vollständige Nummer an



In diesem Fall verwenden wir denselben "erschöpfenden" Algorithmus . Nur werden wir die Zahlen nicht vom Ende der Zahl, sondern von Anfang an "abklemmen".



Wenn die Nummer auf der Kundenkarte vollständig angegeben wurde, werden wir bei der ersten Iteration darauf stoßen. Wenn nicht vollständig - wenn wir einige der entsprechenden Codes "abschneiden".



Natürlich müssen wir eine Art Gegenprüfung durch andere Details (Adresse, TIN, ...) durchführen, damit wir nicht die Situation bekommen, dass wir den Moskauer Code von der eingehenden Nummer „abschneiden“ und einen Kunden aus St. Petersburg anhand der verbleibenden 7-stelligen Nummer finden. Petersburg.



PBX gibt eine "Stadt" -Nummer an



        :     262000
   : 4852262000


Hier ist die Situation interessanter. Wir können nicht jeden möglichen Code auf eine kurze Zahl "inkrementieren" und versuchen zu suchen - es gibt zu viele davon. Schauen wir uns die Situation von der anderen Seite an - wörtlich: 



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


Wenn Sie die Zeilen mit Zahlen erweitern, wird die Aufgabe zu einer regulären Präfixsuche , die mit einem Index mit varchar_pattern_ops und LIKE! Leicht gelöst werden kann. 



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


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


Und dann überprüfen wir noch einmal die zusätzlichen Informationen - aus welcher Region die PBX uns die Nummer gesendet hat, zu welcher Region der Client gehört.


More articles:


All Articles