رن هاتفي. من يتكلم؟ .. هل يساعد "الفيل"

أصبح التعرف التلقائي على العميل ومنطقته عن طريق مكالمة هاتفية واردة جزءًا لا يتجزأ من أي مكتب مساعدة أو نظام CRM مطور. ما عليك سوى أن تكون قادرًا على القيام بذلك بسرعة - ثم تظهر الكثير من الفرص.



على سبيل المثال ، يمكنك أن تُظهر للمدير على الفور المدينة التي تأتي منها المكالمة ، وتشديد قائمة الأسعار الحالية وشروط التسليم ، وعرض بطاقة المتصل ، وآخر المعاملات معه ، وشخص اتصال محدد ، ... - والكثير من الأشياء المفيدة ، كما يمكن لـ VLSI CRM أن يفعل !

كيف تنفذ هذه الوظيفة بنفسك؟ اتضح أنه ليس بهذه الصعوبة. يمكنك حرفياً بناء واختبار نموذج عملي على الركبة - ما عليك سوى حزمة من Node.js و PostgreSQL.

حدد المنطقة بالرقم

لنفترض أن PBX يرسل لنا رقم هاتف واردًا ، تم تطبيعه وتنسيقه بالفعل حتى 10 أرقام (سننظر فقط في المكالمات داخل روسيا). ما هي الطريقة الأكثر فاعلية لفهم مصدر المكالمة؟

جمع رموز الهاتف

أولاً ، نحتاج إلى قاعدة بيانات لرموز هواتف روسيا فيما يتعلق بالمناطق. للقيام بذلك ، يمكنك استخدام مصدر رسمي - مقتطف محدث من خطة الترقيم على موقع الويب الخاص بوكالة الاتصالات الفيدرالية.



لكن البحث لا يكفي ، فأنت بحاجة إلى تنزيل واستخراج هذه البيانات. سيساعدنا برنامج نصي صغير لـ 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

إذا نجح كل شيء كما ينبغي ، فسيتم تحميل ما يقرب من 378 ألف نطاق في طاولتنا:

SET
CREATE TABLE
COPY 377937

لاحظ أنه في مثالنا ، يتم تمثيل كل من الكود وأرقام حدود النطاق بالسلاسل. نعم ، يمكن تحويلهم إلي integer/bigint، لكننا لن نفعل ذلك الآن. علاوة على ذلك ، لا يتكون رقم الهاتف الوارد دائمًا من أرقام فقط - على سبيل المثال ، يمكن لبعض الهواتف العمومية الإبلاغ عن رقمها باستخدام "الرقم أ".

"إنهم يبحثون عن رجال إطفاء ، والشرطة تبحث عن ..."

لنجرب استعلامًا بسيطًا أولاً:

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

[انظر إلى الشرح. tensor.ru] طرحنا



ما يقرب من 70 ألف سطر (وكان من حسن الحظ أنه ليس كل 380 سطرًا!) ، ما يقرب من 10 ميغا بايت من البيانات تم تجريفها ... ليس بكفاءة عالية ، ولكن تم تحقيق النتيجة:

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;

يحتوي حوالي مائة رمز فقط على 10 نطاقات ، والربع تقريبًا يحتوي على واحد بالضبط:

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;

[انظر إلىشرح.tensor.ru]



استغرق الأمر منا 5 مكالمات فهرسة فقط للعثور على الكود الذي كنا نبحث عنه. يبدو الكسب مجهريًا بالأرقام المطلقة ، لكننا حصلنا على 150 ضعفًا في الحمل مقارنة بالخيار الساذج! إذا كان على نظامك معالجة عشرات أو مئات الآلاف من هذه الطلبات في الساعة ، فستصبح المدخرات كبيرة جدًا!

ويمكنك إجراء عدد أقل من التكرارات على الفهرس - إذا تم تقليل جميع الرموز مسبقًا إلى الشكل الكلاسيكي "من 3 إلى 5 أرقام". ومع ذلك ، فإن عدد النطاقات في كل رمز سيزداد ، وتصفيتها يمكن أن تضيف مشاكل.

int8range + GiST

كما لوحظ بشكل صحيح في التعليقات مكسر، نظرًا لأن لدينا جميع الأزواج "code + range" والرقم الوارد له نفس البعد تمامًا وهو 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;

[انظر إلىشرح.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;

على الرغم من بساطته الواضحة ، فإن هذا الخيار يعطي أداء أسوأ من السابق:





[انظر إلى الشرح.

نحدد العميل بالرقم

الآن دعنا نتخيل أن لدينا بالفعل جدولًا مع العملاء ، حيث يتم كتابة رقم الهاتف "المنظف" - تمت إزالة جميع الأقواس والواصلات وما إلى ذلك.



ولكن هذا هو مصدر الإزعاج ، فليس كلهم ​​لديهم رمز مدينة - فإما أن يكون المديرون كسالى جدًا لدرجة لا تسمح لهم بالتسجيل ، أو أن PBX مهيأ بحيث لا يرسل أرقامًا كاملة ، ولكن أرقام "intracity" ... كيف بعد ذلك للعثور على عميل - بعد كل شيء ، لن يعمل البحث عن التطابق الكامل؟

يعطي PBX الرقم الكامل

في هذه الحالة ، سوف نستخدم نفس الخوارزمية "الشاملة" . فقط سنقوم "بقرص" الأرقام ليس من نهاية الرقم ، ولكن من البداية.



إذا تمت الإشارة إلى الرقم في بطاقة العميل بالكامل ، فسوف نتعثر عليه في أول تكرار. إن لم يكن بالكامل - عندما "نقطع" بعض الرموز المناسبة.



بالطبع ، سنحتاج إلى نوع من التحقق من خلال تفاصيل أخرى (العنوان ، رقم التعريف الضريبي ، ...) حتى لا نحصل على موقف "قطعنا" رمز موسكو عن الرقم الوارد ، ووجدنا عميلاً من سانت بطرسبرغ بالرقم المتبقي المكون من 7 أرقام. بطرسبورغ.

يعطي 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 الرقم ، والمنطقة التي ينتمي إليها العميل.