私の電話が鳴った。誰が話しますか？..「象」を助けます

着信電話によるクライアントとその地域の自動識別は、開発されたHelpDeskまたはCRMシステムの不可欠な部分になっています。あなたはそれを素早く行うことができる必要があります-そうすれば多くの機会が現れます。



たとえば、電話の発信元の都市をマネージャーにすぐに表示したり、現在の価格表と配信条件を厳しくしたり、発信者のカード、彼との最後の取引、特定の連絡担当者などを表示したりできます。また、VLSI CRMでできるように、多くの便利な機能を利用できます。

この機能を自分で実装するにはどうすればよいですか？それほど難しいことではありません。文字通り、膝の上で動作するモデルを構築してテストできます。必要なのは、Node.jsとPostgreSQLのバンドルだけです。

番号で地域を決定する

PBXが、すでに正規化され、最大10桁までフォーマットされた着信電話番号を送信するとします（ロシア国内の通話のみを考慮します）。呼び出しがどこから来たのかを理解するための最も効率的な方法は何ですか？

電話コードの収集

まず、地域に関連するロシアの電話コードのデータベースが必要です。これを行うには、公式の情報源を使用できます。これは、連邦通信庁のWebサイトにある番号付け計画からの最新の抜粋です。



しかし、見つけるだけでは不十分です。このデータをダウンロードして抽出する必要があります。リクエストライブラリを使用するNode.jsの小さなスクリプトは、これに役立ちます。

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

それをテストベースにロードしてみましょう。次の作業を行うことができます。

psql -f phonecodes.sql -U postgres tst

すべてが正常に機能した場合、ほぼ37万8000の範囲がテーブルにロードされます。

SET
CREATE TABLE
COPY 377937

この例では、範囲のコードと境界番号の両方が文字列で表されていることに注意してください。はい、に変えることができますがinteger/bigint、今のところこれは行いません。さらに、着信電話番号は必ずしも番号のみで構成されているわけではありません。たとえば、一部の公衆電話は「数字A」で番号を報告できます。

「彼らは消防士を探しています、警察は探しています...」

最初に単純なクエリを試してみましょう。

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

[explain.tensor.ruを見てください]



ほぼ7万行が差し引かれ（そして、380行すべてではなかったのは幸運でした！）、ほぼ10MBのデータがシャベルされました...あまり効率的ではありませんが、結果は達成されました：

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

しかし、どうにかしてそれを取り除きましょうSeq Scan！これを行うには、検索に役立つインデックスが必要LIKEですよね？..



ああ、いいえ。検索する必要がある場合はcolumn LIKE (val || '%')、インデックスの前にvarchar_pattern_opsを付けると役立ちますが、逆になります- val LIKE (column || '%')。そして、「PostgreSQLログからのエラーの分類」の記事で説明した状況に近い状況になります。

応用分野の知識を活用

近いですが、幸いなことに、それでもはるかに単純です。データは固定されており、比較的少数です。さらに、レコードはコードによってかなりまばらに配布されます。

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

約100のコードだけが10の範囲を持ち、ほぼ4分の1がちょうど1つを持っています。

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

それでは、とりあえずコードにインデックスを付けましょう。また、同じコードのすべての範囲をまとめて必要とするためCLUSTER、レコードが物理的に互いに隣接するように、を使用してテーブルを配置しましょう。

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

そして今、私たちの電話番号は正確に（すべて！）10桁で構成されており、その中でプレフィックスコードを分離する必要があることを思い出してください。つまり、私たちのタスクは、10個以下のオプションを単純に列挙することで落ち着いて解決されます。

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;

[explain.tensor.ruを見てください]



探していたコードを見つけるのに5回のインデックス呼び出ししかかかりませんでした。ゲインは絶対数では微視的に見えますが、単純なオプションと比較して負荷が150分の1に削減されました。システムが1時間あたり数万または数十万のそのような要求を処理する必要がある場合、節約は非常に大きくなります。

また、すべてのコードが「3桁から5桁」の従来の形式に事前に縮小されている場合は、インデックスに対してさらに少ない反復を実行できます。ただし、各コードの範囲の数が増えるため、それらをフィルタリングすると問題が発生する可能性があります。

int8range + GiST

コメントに正しく記載されているように miksir、すべてのペア「コード+範囲」があり、着信番号は厳密に同じ10桁の次元であるため、問題は数値間の間隔検索に減らすことができます。



これを行うには、レコードを int8range次のように扱うインデックスを作成します。

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

その後、リクエストで使用できます。

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;

[explain.tensor.ruを見てください]

重複しない間隔+ btree

まず、番号の範囲が実際に重複していないことを確認しましょう。

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;

「何も」得られない場合-すべてが正常であり、次の最適化を適用できます。番号は、最も近い範囲の最後（または最初）までのみ含めることができます。



最も近い「始まり」を見つけるには、通常のbtreeインデックスが必要です。

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;

その明らかな単純さにもかかわらず、このオプションは前のオプションよりもパフォーマンスが低下します：





[explain.tensor.ruを見てください]

クライアントを番号で識別します

ここで、「クリーンアップされた」電話番号が書き込まれているクライアントのテーブルがすでにあると想像してみましょう。すべての括弧、ハイフンなどが削除されています。



しかし、ここに厄介な点があります。すべてに都市コードがあるわけではありません。マネージャーが怠惰すぎてスコアを付けられないか、PBXが完全ではなく「都市内」の番号を送信するように構成されているため、クライアントを見つけるにはどうすればよいでしょうか。結局、完全一致検索は機能しなくなります。

PBXは完全な数を与えます

この場合、同じ「網羅的」アルゴリズムを使用します。番号の最後からではなく、最初から番号を「ピンチオフ」するのは私たちだけです。



カスタマーカードの番号が完全に示されている場合、最初の繰り返しでそれを見つけます。完全ではないにしても、適切なコードのいくつかを「遮断」するとき。



もちろん、他の詳細（アドレス、TINなど）による何らかのクロスチェックが必要になります。これにより、着信番号からモスクワコードを「切り取り」、Stからクライアントを見つけたという状況が発生することはありません。ピーターズバーグ。

PBXは「都市」番号を与えます

        :     262000
   : 4852262000

ここで状況はもっと興味深いです。考えられるすべてのコードを短い数に「インクリメント」して検索を試みることはできません。コードが多すぎます。反対側から状況を見てみましょう-文字通り：

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

行を数字で展開すると、タスクは通常のプレフィックス検索に変わります。これは、varchar_pattern_opsとLIKE！のインデックスを使用して簡単に解決できます。

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

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

そして、もう一度、追加情報を再確認します。PBXが番号を送信した地域、クライアントが属する地域です。