Clever Geek Handbook

Ponsel saya berdering. Siapa yang berbicara? .. Akan membantu "gajah"

Identifikasi otomatis klien dan wilayahnya dengan panggilan telepon masuk telah menjadi bagian integral dari sistem HelpDesk atau CRM yang dikembangkan. Anda hanya perlu bisa melakukannya dengan cepat - kemudian banyak peluang muncul.



Misalnya, Anda dapat langsung menunjukkan kepada manajer dari kota mana panggilan tersebut berasal, memperketat daftar harga saat ini dan kondisi pengiriman, menampilkan kartu penelepon, transaksi terakhir dengannya, kontak tertentu, ... - dan banyak hal berguna, seperti yang dapat dilakukan CRM VLSI kami !





Bagaimana cara menerapkan fungsi ini sendiri? Ternyata tidak terlalu sulit. Anda benar-benar dapat membangun dan menguji model yang berfungsi langsung - Anda hanya perlu sekumpulan Node.js dan PostgreSQL.



Tentukan wilayah dengan nomor



Anggaplah PBX mengirimkan nomor telepon masuk, yang sudah dinormalisasi dan diformat hingga 10 digit (kami hanya akan mempertimbangkan panggilan di Rusia). Apa cara paling efisien untuk memahami dari mana panggilan itu berasal?



Mengumpulkan kode telepon



Pertama, kami memerlukan database kode telepon Rusia terkait dengan wilayah. Untuk melakukan ini, Anda dapat menggunakan sumber resmi - kutipan terbaru dari rencana penomoran di situs web Agen Komunikasi Federal.



Tetapi menemukan tidak cukup, Anda perlu mengunduh dan mengekstrak data ini. Skrip kecil untuk Node.js menggunakan pustaka permintaan akan membantu kami dalam hal ini : 



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


Sekarang mari kita muat ke dalam test base kita, dan Anda bisa bekerja: 



psql -f phonecodes.sql -U postgres tst


Jika semuanya berjalan sebagaimana mestinya, hampir 378 ribu rentang akan dimuat ke dalam tabel kami: 



SET
CREATE TABLE
COPY 377937


Perhatikan bahwa dalam contoh kami, baik kode dan nomor batas rentang diwakili oleh string. Ya, mereka dapat diubah menjadi integer/bigint, tetapi kami tidak akan melakukan ini untuk saat ini. Selain itu, nomor telepon yang masuk tidak selalu hanya terdiri dari angka - misalnya, beberapa telepon umum dapat melaporkan nomornya dengan "digit A".


"Mereka mencari petugas pemadam kebakaran, polisi mencari ..."



Mari coba kueri naif dulu: 



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




[lihat menjelaskan.tensor.ru]



Hampir 70 ribu baris dikurangi (dan untungnya tidak semua 380 baris!), hampir 10MB data disekop ... tidak terlalu efisien, tetapi hasilnya tercapai: 



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


Tapi mari kita singkirkan itu Seq Scan! Untuk melakukan ini, kita hanya perlu indeks yang akan membantu untuk mencari LIKE, kan? ..



Aduh, tidak. Jika kita perlu mencari column LIKE (val || '%'), maka indeks awalan dengan varchar_pattern_ops akan membantu kita , tetapi kita memiliki kebalikannya - val LIKE (column || '%'). Dan kami mendapatkan situasi yang mirip dengan yang saya jelaskan di artikel "Mengklasifikasikan kesalahan dari log PostgreSQL" .



Kami menggunakan pengetahuan tentang bidang terapan



Hampir, tetapi, untungnya, ini masih jauh lebih sederhana - data kami sudah diperbaiki dan jumlahnya relatif sedikit. Selain itu, catatan didistribusikan agak jarang dengan kode: 



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


Hanya sekitar seratus kode yang memiliki 10 rentang, dan hampir seperempatnya memiliki tepat satu: 



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


Jadi mari kita indeks kode saja untuk saat ini. Dan karena kita membutuhkan semua rentang dari kode yang sama secara bersamaan, kita akan mengurutkan tabel kita dengan bantuan CLUSTERsehingga catatan secara fisik bersebelahan satu sama lain: 



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


Dan sekarang mari kita ingat bahwa nomor telepon kita terdiri dari (semua!) 10 digit, di antaranya kita perlu memisahkan kode awalan. Artinya, tugas kita diselesaikan dengan tenang dengan penghitungan sederhana tidak lebih dari 10 opsi: 



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;




[lihat menjelaskan.tensor.ru]



Kami hanya membutuhkan 5 panggilan indeks untuk menemukan kode yang kami cari. Keuntungannya tampak mikroskopis dalam jumlah absolut, tetapi kami mendapat pengurangan beban 150 kali lipat dibandingkan dengan opsi naif! Jika sistem Anda harus memproses puluhan atau ratusan ribu permintaan seperti itu per jam, penghematannya menjadi sangat besar!

Dan Anda dapat melakukan iterasi yang lebih sedikit pada indeks - jika semua kode telah direduksi sebelumnya ke bentuk klasik "dari 3 menjadi 5 digit". Namun, jumlah rentang di setiap kode akan meningkat, dan memfilternya dapat menambah masalah.


int8range + GiST



Seperti dicatat dengan benar di komentar miksir, karena kita memiliki semua pasangan "kode + rentang" dan nomor yang masuk memiliki dimensi yang sama persis yaitu 10 digit, maka masalahnya dapat dikurangi menjadi pencarian interval di antara nilai-nilai numerik.



Untuk melakukan ini, kami akan membuat indeks yang akan memperlakukan catatan kami sebagai int8range: 



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


Setelah itu kita bisa menggunakannya di request: 



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;




[lihat menjelaskan.tensor.ru]



Interval yang tidak tumpang tindih + pohon



Pertama, mari pastikan rentang angka kita tidak benar-benar tumpang tindih: 



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;


Jika Anda mendapatkan "tidak ada" - semuanya baik-baik saja, dan Anda dapat menerapkan pengoptimalan berikut: nomor hanya dapat dimasukkan dalam rentang, sampai akhir (atau awal) yang terdekat .



Untuk menemukan "awal" terdekat, kita membutuhkan indeks btree biasa: 



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;


Meskipun terlihat sederhana, opsi ini memberikan performa yang lebih buruk daripada yang sebelumnya:





[lihat menjelaskan.tensor.ru]



Kami mengidentifikasi klien dengan nomor



Sekarang mari kita bayangkan bahwa kita sudah memiliki tabel dengan klien, di mana nomor telepon "dibersihkan" tertulis - semua tanda kurung, tanda hubung, dll. Dihapus.



Tapi di sini gangguannya, tidak semuanya memiliki kode kota - apakah manajer terlalu malas untuk mencetak skor, atau PBX dikonfigurasi sedemikian rupa sehingga tidak mengirimkan angka penuh, tetapi nomor "intracity" ... Lalu bagaimana menemukan klien - lagipula, pencarian pertandingan lengkap tidak akan berfungsi lagi?



PBX memberikan nomor lengkapnya



Dalam kasus ini, kita akan menggunakan algoritma "lengkap" yang sama . Hanya kita yang akan “mencubit” angka-angkanya bukan dari akhir angka, tetapi dari awal.



Jika nomor di kartu pelanggan ditunjukkan secara penuh, kami akan menemukannya pada iterasi pertama. Jika tidak sepenuhnya - ketika kami "memotong" beberapa kode yang sesuai.



Tentu saja, kami memerlukan semacam pemeriksaan silang dengan detail lain (alamat, TIN, ...) sehingga kami tidak mendapatkan situasi bahwa kami "memotong" kode Moskow dari nomor yang masuk, dan menemukan klien dari St. Petersburg. Petersburg.



PBX memberikan nomor "kota"



        :     262000
   : 4852262000


Di sini situasinya lebih menarik. Kami tidak dapat "menaikkan" setiap kode yang mungkin menjadi angka yang pendek dan mencoba mencari - jumlahnya terlalu banyak. Mari kita lihat situasi dari sisi lain - secara harfiah: 



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


Ternyata jika Anda memperluas baris dengan angka, maka tugas tersebut berubah menjadi pencarian awalan biasa , yang dengan mudah diselesaikan menggunakan indeks dengan varchar_pattern_ops dan LIKE! 



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


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


Dan kemudian, sekali lagi, kami memeriksa ulang informasi tambahan - dari wilayah mana PBX mengirimkan nomor tersebut kepada kami, di wilayah mana klien berada.


More articles:


All Articles