מציאת רשומה לפי התחלה מדוייקת ב PostgreSQL

הנה בעיה מעניינת – כיצד אני מביא רשומה המתאימה ביותר להתחלה כלשהי, כאשר ההתחלה משתנה באורך/תווים שלה?

בשביל להיות פחות כללי, אציג את הבעיה כך:
יש לי קידומות של מספרי טלפון ואני צריך למצוא מי ספק הטלפון שמחזיק בקידומת הזו.

כאשר אני יודע כי 2 תווים ראשונים מחזיקים תמיד בקידומת הבעיה מאוד פשוטה – אני חותך 2 תווים ראשונים ומחפש עליהם קידומות בצורה חד חד ערכית, ואם מדובר ב unique index חיפוש מהיר ביותר שאולי יעשה sequence scan במקום חיפוש באינדקס ויהיה מהיר מאוד.

אבל כאשר חלק מהקידומות הן בעלות 2 תווים, חלק אחר בעלות 3 תווים ואפילו ישנן קידומות בעלות 4 או 6 תווים כאשר התו האחרון הוא זה שמבדיל בין ספקיות שונות יש בעיה.
הרי אין לי ידע מקדים מה הקידומת ומה הוא המספר עצמו.

למזלי יש כלים מאוד חזקים המסייעים בזה עם מסד נתונים PostgreSQL או PG בקיצור.

הדגמה של טבלת קידומות:

create table prefix_providers (
  prefix varchar(15) primary key not null,
  provider_name varchar not null
)

הנה קצת קידומות של ספקיות שהכנתי לצורך ההדגמה:

insert into prefix_providers values
('022', 'Paltal'), ('032', 'Paltal'), ('042', 'Paltal'), 
('082', 'Paltal'), ('092', 'Paltal'), ('0230', 'Hot'), 
('0231', 'Hot'), ('0330', 'Hot'), ('0331', 'Hot'),
('0430', 'Hot'), ('0431', 'Hot'), ('0830', 'Hot'),
('0831', 'Hot'), ('0930', 'Hot'), ('0931', 'Hot'),
('025', 'Bezeq'), ('026', 'Bezeq'), ('027', 'Bezeq'),
('028', 'Bezeq'), ('029', 'Bezeq'), ('039', 'Bezeq');

לא הזנתי את כלל המספרים עבור טלפונים נייחדים וכבר ניתן לראות את הבעיה, נכון?

מה הדרך שהייתם כותבים את השאילתא למצוא את הספק המדויק של מספר הטלפון 03987654321?

הדרך שלי היא לפרק את המחרוזת לסדרה של תווים כאשר כל איבר בסדרה מכיל גם את האיברים הקודמים פלוס עצמו. כלומר 0, 03, 039, 0398 וכו' עד להשארת המספר עצמו.
לאחר מחקר רב, הגעתי לנוסחה הבאה:

ּSELECT ARRAY(SELECT substring('ABCDEF' FROM 1 FOR i)
FROM generate_series(1, GREATEST(length('ABCDEF'))) g(i));

התוצאה היא פשוטה:

            array             
══════════════════════════════
 {A,AB,ABC,ABCD,ABCDE,ABCDEF}

מה זה הקסם הזה?
אתחיל לפתוח סוגריים מבפנים כלפי חוץ לשם ההסבר.
יש שימוש בפונקציה מובנית הקיימת ב PG בשם generate_series.
הפונקציה מאפשרת ליצור טווח (מספרי או תאריכי) מהתחלה מסוימת uעד יעד מסוים וצורת הקפיצה להשיג את היעד הסופי (למשל קפיצה של 25 ספרות).
כך שאם ארצה לקבל בין 0 ל100 את כל המספרים בקפיצה של 25, אכתוב זאת כך:

select generate_series(0, 100, 25);

 generate_series 
═════════════════
               0
              25
              50
              75
             100
(5 rows)

השלב הבא בפירוק המחרוזת הוא לקבל את הערכים לטבלה זמנית בשם "g" עם שדה בשם i.
עכשיו אפשר לחזור ל select הבוחר תתי מחרוזות ולהחזיר תת מחרוזת כאשר כל איטרציה מגדילה את כמות התווים.

אבל יש בעיה – אני מקבל מספר של רשומות (כל תת מחרוזת היא רשומה).
השלב הבא יהיה לכניס את המידע של כל הרשומות למערך:

select ARRAY(select generate_series(0, 100, 25));

      array       
══════════════════
 {0,25,50,75,100}
(1 row)

עכשיו אצור פונקציה בשם prefix על מנת שיהיה קל יותר להשתמש בתוצאה הסופית ולא אהיה צריך לחזור על עצמי כל כך הרבה פעמים:

CREATE OR REPLACE FUNCTION prefixes(varchar) 
   RETURNS varchar[]
   LANGUAGE sql IMMUTABLE
   AS $_$
SELECT ARRAY(SELECT substring($1 FROM 1 FOR i)
 FROM generate_series(1, GREATEST(length($1))) g(i))::varchar[];

את התוצאה השל הפונקציה ניתן להריץ על שאילתא המשתמשת באופרטור השוואה בשם ANY. ערך החזרה שלו הוא חיובי אם לפחות איבר אחד קיים ברשימה בתוך מערך השוואה.

select prefix, prefix=any(prefixes('0334567890'))
from prefix_providers;

  prefix │ ?column? 
════════╪══════════
 022    │ f
 032    │ f
 042    │ f
 082    │ f
 092    │ f
 0230   │ f
 0231   │ f
 0330   │ f
 0331   │ f
 0430   │ f
 0431   │ f
 0830   │ f
 0831   │ f
 0930   │ f
 0931   │ f
 025    │ f
 026    │ f
 027    │ f
 028    │ f
 029    │ f
 035    │ f
 036    │ f
 037    │ f
 038    │ f
 039    │ f
 0398   │ f
(26 rows)

כפי שניתן לראות הכל הוא False כי אמנם יש הרבה רשומות המתחילות ב03 עם עוד ספרה לאחריה, אבל אין קידומת של 03 עם הספרה 3 לאחר מכן (033). אם אוסיף תמיכה גם בקידומת הזו לפחות תשובה אחת תהיה חיובית:

insert into prefix_providers values ('033', 'Bezeq');

select prefix, prefix=any(prefixes('0334567890'))
from prefix_providers;

 prefix │ ?column? 
════════╪══════════
 022    │ f
 032    │ f
 042    │ f
 082    │ f
 092    │ f
 0230   │ f
 0231   │ f
 0330   │ f
 0331   │ f
 0430   │ f
 0431   │ f
 0830   │ f
 0831   │ f
 0930   │ f
 0931   │ f
 025    │ f
 026    │ f
 027    │ f
 028    │ f
 029    │ f
 035    │ f
 036    │ f
 037    │ f
 038    │ f
 039    │ f
 0398   │ f
 033    │ t
(27 rows)

כיצד להשתמש בזה בפועל?

select prefix, provider_name
from prefix_providers
where prefix=any(prefixes('03987654321'));

 prefix │ provider_name 
════════╪═══════════════
 039    │ Bezeq
(1 row)

אבל יש בעיה.
מה קורה כאשר יש לי גם קידומת 0398? התשובה היא שגם הרשומה הזו תחזור:

 prefix │ provider_name 
════════╪═══════════════
 039    │ Bezeq
 0398   │ me
(2 rows)

כאן נכסת תמיכה של פעולת SQL פשוטה שבה אני מחזיר את האיבר הגדול ביותר ורק אותו:

select max(prefix) prefix, provider_name
from prefix_providers
where prefix=any(prefixes('03987654321')) group by prefix
order by prefix desc
limit 1;

 prefix │ provider_name 
════════╪═══════════════
 0398   │ me
(1 row)

ועכשיו אני תמיד אקבל את ה prefix הארוך ביותר שמתאים למספר הטלפון שאני בודק.