מציאת רשומה לפי התחלה מדוייקת ב 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 הארוך ביותר שמתאים למספר הטלפון שאני בודק.

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

  1. Unnamed

    כמה פרפיקסים כאלה יש לך? והאם המערכת יודעת כשהטבלה משתנה?
    אולי עדיף לקרוא את כולם פעם אחת (פר שינוי) ואז לחסוך קריאה לדטאבייס כל פעם.

    1. ik_5 מאת

      יש לי כמה עשרות מליוני רשומות ומהירות התגובה היא בין 2 ל 4 מילי-שניות (החל מPG11) שצריך למצוא את ה prefix המתאים ביותר.
      ויש לי עוד בעיה שיש כאלו שהמספר הראשון הוא כבר ייחודי (למשל פריפקס של תו אחד שהוא ייחודי, וכאלו של 6 תווים שיוצרים משהו ייחודי).

  2. Unnamed

    2 מילי-שניות נשמע הרבה זמן… אבל באמת צריך לדעת יותר על הבעיה כדי לומר משהו רציני.
    מניח שיש סיבות טובות.

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת /  לשנות )

תמונת גוגל

אתה מגיב באמצעות חשבון Google שלך. לצאת מהמערכת /  לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת /  לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת /  לשנות )

מתחבר ל-%s

אתר זו עושה שימוש ב-Akismet כדי לסנן תגובות זבל. פרטים נוספים אודות איך המידע מהתגובה שלך יעובד.